Re: SQL subquery to supply table name? - Mailing list pgsql-general

Stephan Szabo wrote:
>
> Yep, foo has a table_quest column.  In 7.3 (now in beta) you probably
> could make a function that returns a rowset from the table given as
> its argument.

It will work, but you need to use an anonymous return type (i.e. record) and
specify the columns you are actually returning:

# create table foo(f1 int, f2 text);
CREATE TABLE
# insert into foo values (1,'a');
INSERT 1223680 1
# insert into foo values (2,'b');
INSERT 1223681 1
# CREATE OR REPLACE FUNCTION select_from(text) RETURNS SETOF record AS '
# DECLARE
#   sql text;
#   rec record;
# BEGIN
#   sql := ''SELECT * FROM '' || $1;
#   FOR rec IN EXECUTE sql LOOP
#     RETURN NEXT rec;
#   END LOOP;
#   RETURN;
# END;
# ' LANGUAGE 'plpgsql';
CREATE FUNCTION
# select * from select_from('foo') as t(col1 int, col2 text);
  col1 | col2
------+------
     1 | a
     2 | b
(2 rows)


Joe