>Yes, it's possible.
>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
