út 9. 4. 2024 v 18:33 odesílatel Ron Johnson <ronljohnso...@gmail.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@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