Обсуждение: Is there a way to build a query based on data in a table?
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
Hello, From what I know, in pure SQL, it is not possible to perform what you want. But it is feasible in PL/pgSQL (and this script language is supported by default by postgresql...). Regards, Brice 2013/8/28 Herouth Maoz <herouth@unicell.co.il>: > 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