Andrew Edson wrote:
> I've been given a file to maintain, the purpose of which is to purge the database of records more than two years old.
(Database setup is pg 8.1.3)
>
> The program (written in perl) enters postgres as the user 'postgres', and is supposed to select foreign-key records
fromall tables that link together with a table which has a delete_dt field in it, so long as the delete_dt value
(timestampwith time zone) is more than two years old. It then, within a running loop, is supposed to table-by-table
deleteall records where the value of the key in question matches the returned value.
Why don't you use ON DELETE CASCADE foreign key constraints? Let the
data base handle all this work rather than maintaining a complicated
external Perl script which might get out if sync with the data base as
data base design changes are implemented. You don't even need Perl at
all, actually: Have cron fire a very simple shell command using psql to
invoke the delete command against the primary key table (the one with
the delete_dt field), and let the foreign key constraints take care of,
... well..., the foreign key references.