Re: generating dynamic queries using pl/pgsql

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

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