Subject: [NOVICE] generating dynamic queries using pl/pgsql
Date: Tue, 18 Jan 2005 10:34:11 -0800 (PST)

Hi All,

Is it possible to "generate" dynamic queries using pl/pgsql?

I am trying to archive a set of tables: affiliate_event, affiliate_batch, affiliate_batch_details, affiliate_daily_batch, affiliate_daily_batch_details.

The following steps are necessary for each table:

1) select the data from the table based on the date (and other clause), and Move that data to a temporary table

2) Dump the temp table to a file

3) Delete the data from the original table

4) Delete the temporary table.

Is it possible for pl/pgsql to dynamically generate the SQL select queries needed in step 1??

What information/input would it need in that case?

These are the step 1 queries I am using:

create table temp1 as select * from affiliate_batch where tx_dt < '12/31/2003';

delete from temp1;

--------------------------------------------------------------------------------------------------------------------------------

create table temp1 as select bd.* from affiliate_batch_details bd join affiliate_batch b on bd.batch_id=b.id where b.tx_dt < '12/31/2003' order by bd.batch_id;

delete from temp1;

--------------------------------------------------------------------------------------------------------------------------------

create table temp1 as select adb.* from affiliate_daily_batch adb join affiliate_batch b on adb.monthly_batch_id=b.id where b.tx_dt < '12/31/2003' order by adb.monthly_batch_id;

delete from temp1;

---------------------------------------------------------------------------------------------------------------------------------

create table temp1 as select adb.* from affiliate_daily_batch_details adb where exists (select ad.id from affiliate_daily_batch ad join affiliate_batch b on ad.monthly_batch_id=b.id where adb.batch_id=ad.id and b.tx_dt < '12/31/2003' order by ad.monthly_batch_id) order by adb.batch_id;

delete from temp1;

---------------------------------------------------------------------------------------------------------------------------------

create table temp1 as select * from affiliate_event where dt<'12/31/2003';

delete from temp1;

---------------------------------------------------------------------------------------------------------------------------------

Thanks in advance!

Saranya

----------------

Yes, it's possible.

http://www.postgresql.org/docs/7.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

The manual has some great information.

____________________________________________________
Get your free email from http://www.kittymail.com