1

Basically, at least for proof of concept, I want a function where I can run: SELECT res('table_name'); and this will give me the results of SELECT * FROM table_name;. The issue I am having is schema...in the declaration of the function I have:

CREATE OR REPLACE FUNCTION res(table_name TEXT) RETURNS SETOF THISISTHEPROBLEM AS

The problem is that I do not know how to declare my return, as it wants me to specify a table or a schema, and I won't have that until the function is actually run.

Any ideas?

A Question Asker
  • 3,339
  • 7
  • 31
  • 39
  • will this do http://stackoverflow.com/questions/955167/postgresql-return-setof-record-virtual-table ? – Unreason Mar 28 '11 at 15:22

3 Answers3

7

You can do this, but as mentioned before you have to add a column definiton list in the SELECT query.

CREATE OR REPLACE FUNCTION res(table_name TEXT)  RETURNS SETOF record AS $$
BEGIN
  RETURN QUERY EXECUTE 'SELECT * FROM ' || table_name;
END;
$$ LANGUAGE plpgsql;



SELECT * FROM res('sometable') sometable (col1 INTEGER, col2 INTEGER, col3 SMALLINT, col4 TEXT);
rudi-moore
  • 2,650
  • 1
  • 19
  • 16
  • 2
    +1 useful answer. Worth mentioning that `return query execute` is 8.4 and above? –  Mar 28 '11 at 18:53
4

Why for any real practical purpose would you just want to pass in table and select * from it? For fun maybe?

You can't do it without defining some kind of known output like jack and rudi show. Or doing it like depesz does here using output parameters http://www.depesz.com/index.php/2008/05/03/waiting-for-84-return-query-execute-and-cursor_tuple_fraction/.

A few hack around the wall approachs are to issue raise notices in a loop and print out a result set one row at a time. Or you could create a function called get_rows_TABLENAME that has a definition for every table you want to return. Just use code to generate the procedures creations. But again not sure how much value doing a select * from a table, especially with no constraints is other than for fun or making the DBA's blood boil.

Now in SQL Server you can have a stored procedure return a dynamic result set. This is both a blessing and curse as you can't be certain what comes back without looking up the definition. For me I look at PostgreSQL's implementation to be the more sound way to go about it.

Kuberchaun
  • 29,160
  • 7
  • 51
  • 59
  • depesz site is overall a really great resource if you have never checked it out. He has lots of examples and tests. – Kuberchaun Mar 28 '11 at 21:08
  • I think I was put off by the dodgy English to start with - but you are right - it's a mine of information. wCTE? wow. –  Mar 29 '11 at 08:27
2

Even if you manage to do this (see rudi-moore's answer for a way if you have 8.4 or above), You will have to expand the type explicitly in the select - eg:

SELECT res('table_name') as foo(id int,...)