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