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.
110 sats \ 0 replies \ @jp305 7 Mar
That's where it shines the most and how I use it too.
Something that would take a single brain hours to create or something very typing intensive or repetitive.
reply