Only when I have a very narrowly scoped task that I have no clue how to do exactly.
Today I used it to write this plpgsql function to take a db seed and shift all timestamp columns forward to compensate for the db seed being old:
CREATE OR REPLACE FUNCTION timewarp()
RETURNS VOID LANGUAGE plpgsql AS $$
DECLARE
r RECORD;
max_timestamp TIMESTAMP;
interval_to_add INTERVAL;
BEGIN
FOR r IN SELECT c.table_schema, c.table_name, c.column_name
FROM information_schema.columns c
JOIN information_schema.tables t ON c.table_schema = t.table_schema AND c.table_name = t.table_name
WHERE c.data_type IN ('timestamp without time zone', 'timestamp with time zone')
AND c.table_schema NOT IN ('pg_catalog', 'information_schema') -- Exclude system schemas
AND t.table_type = 'BASE TABLE' -- Ensure targeting only user-defined tables (excluding views)
AND t.table_schema NOT LIKE 'pg_%' -- Exclude other potential PostgreSQL system schemas
LOOP
-- Calculate the maximum value in the column
EXECUTE format('SELECT max(%I) FROM %I.%I', r.column_name, r.table_schema, r.table_name) INTO max_timestamp;
-- If there's a maximum value, calculate the interval and update the column
IF max_timestamp IS NOT NULL THEN
interval_to_add := now() - max_timestamp;
EXECUTE format('UPDATE %I.%I SET %I = %I + %L', r.table_schema, r.table_name, r.column_name, r.column_name, interval_to_add);
END IF;
END LOOP;
END;
$$;
After a few prompts to make it fix a few oversights, it worked like a charm.
plpgsql
function to take a db seed and shift all timestamp columns forward to compensate for the db seed being old: