Обсуждение: generating dynamic queries using pl/pgsql

Поиск

Список

Период

Сортировка

generating dynamic queries using pl/pgsql

От

sarlav kumar

Дата:

18 января 2005 г., 21:36:48

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

Re: generating dynamic queries using pl/pgsql

От

"K Anderson"

Дата:

19 января 2005 г., 09:01:19

----- 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

Re: generating dynamic queries using pl/pgsql

От

sarlav kumar

Дата:

20 января 2005 г., 20:40:56

>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.

I read through the manual. But I am still not clear how to actually generate the "queries" dynamically. In my case, some of the "select queries" that I want to dynamically generate have joins and some of the "select queries" are just from one table.

I am new to pl/pgsql. I came up with the following code to start with. This one just has all the queries hardcoded. I want to dynamically generate the "select query part" in the "create table temp1" statement. I am totally lost:(. It would be great if someone can help me!

Also, is it possible to dump a table within pl/pgsql?

create function try1(date,text) returns integer as '

declare
arch_date alias for $1;
filename alias for $2;

begin

create table temp1 as select * from affiliate_batch where tx_dt < arch_date;
-- Here, I would like to dump the table "temp1" to 'filename' before deleting it

drop table 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 < arch_date order by bd.batch_id;