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