Re: generating dynamic queries using pl/pgsql

<BR><BR>----- Original Message -----<BR>From: "sarlav kumar" <SARLAVK(at)YAHOO(dot)COM><BR>To: pgsqlnovice <PGSQL-NOVICE(at)POSTGRESQL(dot)ORG><BR>Subject: [NOVICE] generating dynamic queries using pl/pgsql<BR>Date: Tue, 18 Jan 2005 10:34:11 -0800 (PST)<BR><BR>
<DIV>Hi All,</DIV>
<DIV>&nbsp;</DIV>
<DIV>Is it possible to "generate" dynamic queries using pl/pgsql? </DIV>
<DIV>&nbsp;</DIV>
<DIV>I am trying to archive a set of tables: affiliate_event, affiliate_batch, affiliate_batch_details, affiliate_daily_batch, affiliate_daily_batch_details. </DIV>
<DIV>&nbsp;</DIV>
<DIV>The following steps are necessary for each table:</DIV>
<DIV>&nbsp;</DIV>
<DIV>&nbsp;1) select the data from the&nbsp;table based on the date (and other clause), and Move that data to a temporary table&nbsp;</DIV>
<DIV>&nbsp;2)&nbsp;Dump the temp table to a file</DIV>
<DIV>&nbsp;3) Delete the data from the original table</DIV>
<DIV>&nbsp;4)&nbsp;Delete the temporary table.</DIV>
<DIV>&nbsp;</DIV>
<DIV>Is it possible&nbsp;for pl/pgsql to&nbsp;dynamically generate the SQL select queries needed in step 1??</DIV>
<DIV>What information/input would it need in that case?</DIV>
<DIV>&nbsp;</DIV>
<DIV>These are the step 1 queries I am using:</DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT color=#0000ff>create table temp1 as select * from affiliate_batch where tx_dt &lt; '12/31/2003';</FONT></DIV>
<DIV>..</DIV>
<DIV>delete&nbsp;from temp1;</DIV>
<DIV>--------------------------------------------------------------------------------------------------------------------------------</DIV>
<DIV><FONT color=#0000ff>create table temp1 as select bd.* from affiliate_batch_details bd join affiliate_batch b on bd.batch_id=b.id where&nbsp; b.tx_dt &lt; '12/31/2003' order by bd.batch_id;</FONT></DIV>
<DIV>..</DIV>
<DIV>delete&nbsp;from temp1;</DIV>
<DIV>--------------------------------------------------------------------------------------------------------------------------------</DIV>
<DIV><FONT color=#0000ff>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 &lt; '12/31/2003' order by adb.monthly_batch_id;</FONT></DIV>
<DIV>..</DIV>
<DIV>delete&nbsp;from temp1;</DIV>
<DIV>---------------------------------------------------------------------------------------------------------------------------------</DIV>
<DIV><FONT color=#0000ff>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 &lt; '12/31/2003' order by ad.monthly_batch_id) order by adb.batch_id;</FONT></DIV>
<DIV>
<DIV>..</DIV>
<DIV>delete&nbsp;from temp1;</DIV>
<DIV>---------------------------------------------------------------------------------------------------------------------------------</DIV></DIV>
<DIV><FONT color=#0000ff>create table temp1 as select * from affiliate_event where dt&lt;'12/31/2003';</FONT></DIV>
<DIV>
<DIV>..</DIV>
<DIV>delete&nbsp;from temp1;</DIV>
<DIV>---------------------------------------------------------------------------------------------------------------------------------</DIV>
<DIV>&nbsp;</DIV>
<DIV>Thanks in advance!</DIV>
<DIV>&nbsp;</DIV>
<DIV>Saranya</DIV>
<DIV>&nbsp;</DIV>
<DIV>----------------</DIV>
<DIV>&nbsp;</DIV>
<DIV>Yes, it's possible. </DIV>
<DIV>&nbsp;</DIV>
<DIV><A href="http://www.postgresql.org/docs/7.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN">http://www.postgresql.org/docs/7.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN</A></DIV>
<DIV>&nbsp;</DIV>
<DIV>The manual has some great information.</DIV></DIV><BR>
--
<p>____________________________________________________<br>
Get your free email from <a href="http://www.kittymail.com" target="_blank">http://www.kittymail.com</a></p>

Powered by Outblaze