Re: plpgsql returning resultset

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(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of Thomas Finneid
> Sent: Tuesday, September 02, 2008 4:19 PM
> To: pgsql-general(at)postgresql(dot)org
> Cc: rod(at)iol(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general