PostgreSQL: return a select statement in a procedure

Question

Is there any equivalent from MSSQL to PostgreSQL to return a select statement (which result could be from various tables)? In MSSQL it would be like this:

CREATE PROCEDURE MyProc
AS
BEGIN
   SELECT * FROM MyTable
END

MyTable can be any table in MSSQL but in PostgreSQL I have to define from which table I want to return. Like this:

CREATE FUNCTION MyFunction ()
RETURNS setof "MyTable" AS $$
BEGIN
  RETURN QUERY SELECT * FROM "MyTable"
END;
$$ LANGUAGE plpgsql;

But what I want to do is return the result from a table which is passed by a param (of course my procedure is more complex and my intention why to do that is not that simple, this is only an abstract).

The problem is not the passing of the table name, the problem is the definition of the result set. Postgres requires the returned result to be defined at compile time (when using `returns setof` or `returns table`). I think the only way to do this would be to use a function with a ref cursor. — , Jan 22 '14 at 12:55
See this: http://stackoverflow.com/questions/16452844/postgres-dynamic-sql-and-list-result — , Jan 22 '14 at 12:59
Hmm that's kind of pity but I suspected it. I will try the cursor, that's a good idea, so I will give this as the result to php. Write it as answer so I will take this as the accepted answer — kinske, Jan 22 '14 at 13:10
@a_horse_with_no_name cursor does it. Took me a little time how to handle the cursor with PHP (and convert it into an array), but it worked. Thanks! Please write your comment as answer so I can accept. — kinske, Jan 23 '14 at 13:01

Erwin Brandstetter · Accepted Answer · 2017-12-28T05:20:04.230

It is more complex in Postgres, but there is a way with polymorphic types:

CREATE OR REPLACE FUNCTION f_data_of(_tbl_type anyelement)
  RETURNS SETOF anyelement AS
$func$
BEGIN

RETURN QUERY EXECUTE 'SELECT * FROM ' || pg_typeof(_tbl_type);

END
$func$ LANGUAGE plpgsql;

Call (important!):

SELECT * FROM f_data_of(NULL::"MyTable");

Detailed explanation in this related answer (see last paragraph: Various complete table types):

Refactor a PL/pgSQL function to return the output of various SELECT queries

PostgreSQL: return a select statement in a procedure

1 Answers1