Re: generating dynamic queries using pl/pgsql - Mailing list pgsql-novice

From sarlav kumar
Subject Re: generating dynamic queries using pl/pgsql
Date
Msg-id 20050120174048.64254.qmail@web51303.mail.yahoo.com
Whole thread Raw
In response to Re: generating dynamic queries using pl/pgsql  ("K Anderson" <msmouse@kittymail.com>)
Responses Re: generating dynamic queries using pl/pgsql
List pgsql-novice
>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


Do you Yahoo!?
Yahoo! Search presents - Jib Jab's 'Second Term'

pgsql-novice by date:

Previous
From:
Date:
Subject: Re: Strange User Problem
Next
From: Sean Davis
Date:
Subject: Re: generating dynamic queries using pl/pgsql