From: | sarlav kumar <sarlavk(at)yahoo(dot)com> |
---|---|
To: | pgsqlnovice <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: generating dynamic queries using pl/pgsql |
Date: | 2005-01-20 17:40:48 |
Message-ID: | 20050120174048.64254.qmail@web51303.mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
>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'
From | Date | Subject | |
---|---|---|---|
Next Message | Sean Davis | 2005-01-20 17:50:52 | Re: generating dynamic queries using pl/pgsql |
Previous Message | operationsengineer1 | 2005-01-20 16:37:16 | Re: Strange User Problem |