Re: plpgsql returning resultset

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
>
>