Thread: plpgsql returning resultset
Hi
I know the subject has been discussed before, but I dont find what any
information that helps me make it work, so please bear with me.
In pg 8.2 I want to write a function that gathers data from different
tables and joins it into a single resultset, similar to "select * from
tableA", but the problem I keep having is that I cant get the return to
work. I have tried return next and it fails. I have also tried refcursor,
but am not sure if that is the best way, its a littlebit cumbersome in a
program.
Are those the only two options? and what did I do wrong in the return next
create function test2() returns setof record as
$$
declare
val_list record;
begin
select * into val_list from tableA;
return next val_list;
return:
end
$$ .....
with the query:
select test2();
ERROR: set-valued function called in context that cannot accept a set
CONTEXT: line 9 at return next
regards thomas
On 02/09/2008 11:12, tfinneid@student.matnat.uio.no wrote:
> create function test2() returns setof record as
> $$
> declare
> val_list record;
> begin
> select * into val_list from tableA;
> return next val_list;
> return:
> end
> $$ .....
Hi there,
You need to do it like this:
with val_list in
select * from tableA do
loop
return next val_list;
end loop;
return;
There's an example here:
http://www.postgresql.org/docs/8.3/static/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING
Ray.
------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------
> Hi there, > > You need to do it like this: > > with val_list in > select * from tableA do > loop > return next val_list; > end loop; > return; > > There's an example here: Does that work in 8.2, cause i get the same error message as I described above regards thomas
On 02/09/2008 12:18, tfinneid@student.matnat.uio.no wrote: > Does that work in 8.2, cause i get the same error message as I described > above Yep, it does..... I should have mentioned that you call your function like this: select * from my_function() - in other words, a SETOF-returning function takes the place of a table in a SELECT statement. Can you show us more of your code? Ray. ------------------------------------------------------------------ Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals ------------------------------------------------------------------
Raymond O'Donnell wrote:
> Can you show us more of your code?
I figured out how to make it work when using "for" instead of "with".
Here is the code and the error message. I couldnt find anything in the
documentation about "with" but I did find something about "for" which I
managed to make work. In any case here is the code for the "with" code:
create or replace function get_profile() returns setof tableA as
$$
declare
val_list tableA%rowtype;
begin
with val_list in
select * from tableA
do
loop
return next val_list;
end loop;
return;
end;
$$ language 'plpgsql';
the error message is:
psql:functions.sql:116: ERROR: syntax error at or near "with $1"
LINE 1: with $1 in select * from attribute_values_part_seq_1_ff_5 ...
^
QUERY: with $1 in select * from attribute_values_part_seq_1_ff_5 do
loop return next $1
CONTEXT: SQL statement in PL/PgSQL function "get_profile" near line 10
Hi again, I tried to take the "with" form of the function further to
complete the actual method and met with another error message which I
dont understand.
I have a number for tables (partitioned) from which I need to retrieve
data. Another table keeps track of which tables I should read from.
The tables are named table_X, where X is 1-N. from that I want to
retrieve some data from the selected tables and add it all into one
resultset which I return to the client.
The code is as follows:
create function get_profile(se_arg int4, st_arg int4, tr_arg int4)
returns setof table_part as
$$
declare
table_name text;
val_list table_part%rowtype;
num_list table_part_num_list%rowtype;
begin
for num_list in select num
from table_part_num_list
where se=se_arg
loop
table_name := 'table_part_'|| num_list.num;
select * into val_list
from table_name
where st=st_arg and tr=tr_arg;
return next val_list;
end loop;
return;
end;
$$ language 'plpgsql';
the error message I get when I try to create the function is:
psql:functions.sql:159: ERROR: syntax error at or near "$1"
LINE 1: select * from $1 where st= $2 and tr= $3
^
QUERY: select * from $1 where st= $2 and tr= $3
CONTEXT: SQL statement in PL/PgSQL function "get_profile" near line 15
Any ideas what I am doing wrong?
regards
thomas
On 02/09/2008 21:55, Thomas Finneid wrote: > with val_list in > select * from tableA > do > loop > return next val_list; > end loop; Oops - my mistake - it should indeed be FOR, not WITH, hence your error message. One other thing in the above - you don't need the DO, it's just FOR...LOOP...END LOOP. Ray. ------------------------------------------------------------------ Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals ------------------------------------------------------------------
On 02/09/2008 22:19, Thomas Finneid wrote: > for num_list in select num > from table_part_num_list > where se=se_arg > loop > table_name := 'table_part_'|| num_list.num; > > select * into val_list > from table_name > where st=st_arg and tr=tr_arg; > > return next val_list; I think you need to build the query dynamically as a string, then execute it using EXECUTE: http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN So something like this (I haven't tried it): loop execute 'select * into val_list from ' || 'table_part_' || num_list.num || ' where st = st_arg and tr = tr_arg'; return next val_list; end loop; Hopefully this will work. Ray. ------------------------------------------------------------------ Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals ------------------------------------------------------------------
PostgreSQL has table partitioning in it so you don't have to dynamically figure out which table to get the data from. http://www.postgresql.org/docs/8.3/interactive/ddl-partitioning.html However, you can achieve dynamic SQL in plpgsql too. http://www.postgresql.org/docs/8.3/interactive/ecpg-dynamic.html Jon > -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > owner@postgresql.org] On Behalf Of Thomas Finneid > Sent: Tuesday, September 02, 2008 4:19 PM > To: pgsql-general@postgresql.org > Cc: rod@iol.ie > Subject: Re: [GENERAL] plpgsql returning resultset > > > Hi again, I tried to take the "with" form of the function further to > complete the actual method and met with another error message which I > dont understand. > > I have a number for tables (partitioned) from which I need to retrieve > data. Another table keeps track of which tables I should read from. > The tables are named table_X, where X is 1-N. from that I want to > retrieve some data from the selected tables and add it all into one > resultset which I return to the client. > > The code is as follows: > > > create function get_profile(se_arg int4, st_arg int4, tr_arg int4) > returns setof table_part as > $$ > declare > table_name text; > val_list table_part%rowtype; > num_list table_part_num_list%rowtype; > begin > > for num_list in select num > from table_part_num_list > where se=se_arg > loop > table_name := 'table_part_'|| num_list.num; > > select * into val_list > from table_name > where st=st_arg and tr=tr_arg; > > return next val_list; > end loop; > > return; > end; > $$ language 'plpgsql'; > > the error message I get when I try to create the function is: > > > psql:functions.sql:159: ERROR: syntax error at or near "$1" > LINE 1: select * from $1 where st= $2 and tr= $3 > ^ > QUERY: select * from $1 where st= $2 and tr= $3 > CONTEXT: SQL statement in PL/PgSQL function "get_profile" near line 15 > > Any ideas what I am doing wrong? > > regards > > thomas > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
I believe you need to use for execute '...' loop, since the table_name is dynamically composed. Regards, Alex Vinogradovs On Tue, 2008-09-02 at 23:19 +0200, Thomas Finneid wrote: > Hi again, I tried to take the "with" form of the function further to > complete the actual method and met with another error message which I > dont understand. > > I have a number for tables (partitioned) from which I need to retrieve > data. Another table keeps track of which tables I should read from. > The tables are named table_X, where X is 1-N. from that I want to > retrieve some data from the selected tables and add it all into one > resultset which I return to the client. > > The code is as follows: > > > create function get_profile(se_arg int4, st_arg int4, tr_arg int4) > returns setof table_part as > $$ > declare > table_name text; > val_list table_part%rowtype; > num_list table_part_num_list%rowtype; > begin > > for num_list in select num > from table_part_num_list > where se=se_arg > loop > table_name := 'table_part_'|| num_list.num; > > select * into val_list > from table_name > where st=st_arg and tr=tr_arg; > > return next val_list; > end loop; > > return; > end; > $$ language 'plpgsql'; > > the error message I get when I try to create the function is: > > > psql:functions.sql:159: ERROR: syntax error at or near "$1" > LINE 1: select * from $1 where st= $2 and tr= $3 > ^ > QUERY: select * from $1 where st= $2 and tr= $3 > CONTEXT: SQL statement in PL/PgSQL function "get_profile" near line 15 > > Any ideas what I am doing wrong? > > regards > > thomas > >
Roberts, Jon wrote: > PostgreSQL has table partitioning in it so you don't have to dynamically > figure out which table to get the data from. I know, but the super table can't handle the number of partition tables I need, 10K-100K tables. Whenever I do a query on the super table, it just aborts. regards thomas