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
