Re: PL/pgSQL techniques better than bash for dynamic DO?

út 9. 4. 2024 v 18:33 odesílatel Ron Johnson <ronljohnsonjr(at)gmail(dot)com>
napsal:

> PG 9.6.11, if relevant, migrating to PG 14 Real Soon Now.
>
> I must purge the oldest X period of records from 70 tables, every Sunday.
> The field name, interval (X days or months) and date (CURRENT_DATE or
> CURRENT_TIMESTAMP) varies for each table.
> Thus, I put all the relevant data in a tab-separated value file, and use
> bash to read through it, purging one table at a time. This works well,
> except for Foreign Key constraints; carefully ordering the file to purge
> records in the correct order eliminates most FK errors, but not all.
>
> Therefore, I created an anonymous DO statement to delete the "deletable"
> old records, while skipping the ones that would fail from a FK constraint.
> (Eventually, the records in the FK table will get deleted, so eventually
> the records who's DELETE failed will succeed in getting deleted.)
>
> (NOTE: I cannot change the FK constraints to ON DELETE CASCADE, and nor do
> I want to fight with the 3rd party app vendor, since it defeats the purpose
> of FK constraints.)
>
> Here's the snippet of bash code:
> local Schema=$1
> local Table=$2
> local Field=$3
> local DtCol=$4 # CURRENT_TIMESTAMP or CURRENT_DATE
> local Thresh=$5 # example: '90 day'
> local FQTable=${Schema}.${Table}
> DeS="DO \$\$
> DECLARE
> delsum INTEGER = 0;
> delcnt INTEGER;
> skipsum integer = 0;
> cur_row CURSOR FOR
> SELECT $Field, ${Table}_id
> from ${FQTable}
> where $Field < (${DtCol} - interval ${Thresh});
> BEGIN
> FOR arow IN cur_row
> LOOP
> BEGIN
> DELETE FROM ${FQTable} WHERE CURRENT OF cur_row;
> GET DIAGNOSTICS delcnt = ROW_COUNT;
> delsum = delsum + delcnt; EXCEPTION
> WHEN others THEN
> skipsum = skipsum + 1;
> RAISE NOTICE ' Skipped ${FQTable} WHERE ${Table}_id =
> %; ${Field} = %',
> arow.${Table}_id, arow.${Field};
> END;
> END LOOP;
> RAISE NOTICE 'Sum of deleted rows: %', delsum;
> RAISE NOTICE 'Sum of skipped rows: %', skipsum;
> END \$\$;
> "
>
> It generates the perfectly functional SQL:
> DO $$
> DECLARE
> delsum INTEGER = 0;
> delcnt INTEGER;
> skipsum integer = 0;
> cur_row CURSOR FOR
> SELECT modified_on, check_id
> from tms.check
> where modified_on < (CURRENT_TIMESTAMP - interval '90 day');
> BEGIN
> FOR arow IN cur_row
> LOOP
> BEGIN
> DELETE FROM tms.check WHERE CURRENT OF cur_row;
> GET DIAGNOSTICS delcnt = ROW_COUNT;
> delsum = delsum + delcnt;
> EXCEPTION
> WHEN others THEN
> skipsum = skipsum + 1;
> RAISE NOTICE ' Skipped tms.check WHERE check_id = %;
> modified_on = %',
> arow.check_id, arow.modified_on;
> END;
> END LOOP;
> RAISE NOTICE 'Sum of deleted rows: %', delsum;
> RAISE NOTICE 'Sum of skipped rows: %', skipsum;
> END $$;
>
> Can I do this better in PL/pgSQL with dynamic SQL (that doesn't get hairy
> with nested quotes, etc)?
>

you can pass values by GUC instead

pavel(at)nemesis:~$ psql -v var="AHOJ"
Assertions: on
psql (17devel)
Type "help" for help.

(2024-04-09 19:07:55) postgres=# select set_config('my.var', :'var', false);
┌────────────┐
│ set_config │
╞════════════╡
│ AHOJ │
└────────────┘
(1 row)

(2024-04-09 19:08:46) postgres=# do $$
postgres$# declare myvar varchar default current_setting('my.var');
postgres$# begin
postgres$# raise notice '%', myvar;
postgres$# end;
postgres$# $$;
NOTICE: AHOJ
DO

Regards

Pavel