Newsgroups - Index

Re: generating dynamic queries using pl/pgsql

From:	sarlav kumar
Subject:	Re: generating dynamic queries using pl/pgsql
Date:	Thu, 20 Jan 2005 09:40:48 -0800 (PST)

--0-764587359-1106242848=:60972
Content-Type: text/plain; charset=us-ascii

>Yes, it's possible.
>STATEMENTS-EXECUTING-DYN">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;
-- Here, I would like to dump the table "temp1" to 'filename' before deleting it
drop table temp1;

create table temp1 as select * from affiliate_daily_batch adb where exists (select b.id from affiliate_batch b where b.tx_dt < arch_date and adb.monthly_batch_id=b.id) order by adb.monthly_batch_id;
-- Here, I would like to dump the table "temp1" to 'filename' before deleting it
drop table temp1;

create table temp1 as select adb.* from affiliate_daily_batch_details adb join affiliate_daily_batch ad on ad.id=adb.batch_id join affiliate_batch b on ad.monthly_batch_id=b.id where b.tx_dt < arch_date order by ad.monthly_batch_id;
-- Here, I would like to dump the table "temp1" to 'filename' before deleting it
drop table temp1;

create table temp1 as select * from affiliate_event where 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 * from clickthrough where date < arch_date||''23:59:59'';
-- Here, I would like to dump the table "temp1" to 'filename' before deleting it
drop table temp1;

return 1;
end;
' language 'plpgsql';

Thanks a lot for all the help.

Saranya

---------------------------------
Do you Yahoo!?
Yahoo! Search presents - Jib Jab's 'Second Term'
--0-764587359-1106242848=:60972
Content-Type: text/html; charset=us-ascii

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