From: | sarlav kumar <sarlavk(at)yahoo(dot)com> |
---|---|
To: | pgsqlnovice <pgsql-novice(at)postgresql(dot)org> |
Subject: | generating dynamic queries using pl/pgsql |
Date: | 2005-01-18 18:34:11 |
Message-ID: | 20050118183411.32966.qmail@web51305.mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
From | Date | Subject | |
---|---|---|---|
Next Message | Keith Worthington | 2005-01-18 18:54:00 | Re: How the get variables out of a plggsql RECORD by column number |
Previous Message | Subha | 2005-01-18 17:55:14 | Postgresql on windows |