[SQL] 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
Re: [SQL] Is there a way to build a query based on data in a table?
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 : > 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] CTAGS for PL/pgSQL ?
Does anyone know if there are any CTAGS extensions or variants that support PL/pgSQL ? I use exuberant-ctags which does not support it, and a web search does not return anything promising. (I sent this same email to -novice the list, but this list seems more appropriate). Regards, Charles Sheridan -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
