Hello.
I want to run an automatic archiving SQL script, that looks at a meta-table containing data about the tables to be archived.
Basically, suppose I have this data in the meta-table:
table_name varchar(100)
datetime_column_name varchar(100)
archive_interval interval
Like this:
public.foo | foo_date | 1 day
schema1.bar | bar_time | 2 day
And for each such table I want to run a query like
INSERT INTO foo__archive
SELECT * FROM foo
WHERE foo_date < timestamp 'today' - interval '1 day'
;
And I want to build this dynamically and run it based on the table above. Is there any way of doing this? I mean, not by doing text manipulation in a programming language?
Herouth