Re: generating dynamic queries using pl/pgsql
От | K Anderson |
---|---|
Тема | Re: generating dynamic queries using pl/pgsql |
Дата | |
Msg-id | 20050119060105.50D5F1027BE@ws3.hk5.outblaze.com обсуждение исходный текст |
Ответ на | generating dynamic queries using pl/pgsql (sarlav kumar <sarlavk@yahoo.com>) |
Ответы |
Re: generating dynamic queries using pl/pgsql
(sarlav kumar <sarlavk@yahoo.com>)
|
Список | pgsql-novice |
----- Original Message -----From: "sarlav kumar" <SARLAVK@YAHOO= .COM>To: pgsqlnovice Subject: [NOVICE]= generating dynamic queries using pl/pgsqlDate: 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_bat= ch, affiliate_batch_details, affiliate_daily_batch, affiliate_daily_batch_d= etails. The following steps are necessary for each table: 1) select the data from the table based on the date (and ot= her 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_ba= tch 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=3Db.id where = b.tx_dt < '12/31/2003' order by bd.batch_id; .. delete from temp1; ----------------------------------------------------------------------= ---------------------------------------------------------- create table temp1 as select adb.* from affiliat= e_daily_batch adb join affiliate_batch b on adb.monthly_batch_id=3Db.id whe= re b.tx_dt < '12/31/2003' order by adb.monthly_batch_id; .. delete from temp1; ----------------------------------------------------------------------= ----------------------------------------------------------- create table temp1 as select adb.* from affiliat= e_daily_batch_details adb where exists (select ad.id from affiliate_daily_b= atch ad join affiliate_batch b on ad.monthly_batch_id=3Db.id where adb.batc= h_id=3Dad.id and b.tx_dt < '12/31/2003' order by ad.monthly_batch_id) or= der by adb.batch_id; .. delete from temp1; ----------------------------------------------------------------------= ----------------------------------------------------------- create table temp1 as select * from affiliate_ev= ent where dt<'12/31/2003'; .. delete from temp1; ----------------------------------------------------------------------= ----------------------------------------------------------- Thanks in advance! Saranya ---------------- Yes, it's possible. <A href=3D"http://www.postgresql.org/docs/7.4/interactive/plpgsql-stat= ements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN">http://www.postgresql.org/doc= s/7.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN<= /A> The manual has some great information. --=20 ____________________________________________________ Get your free email from <a href=3D"http://www.kittymail.com" target=3D"_bl= ank">http://www.kittymail.com Powered by Outblaze
В списке pgsql-novice по дате отправления: