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. — user330315
– user330315, Commented Jan 22, 2014 at 12:55
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
– kinske, Commented Jan 22, 2014 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
– kinske, Commented Jan 23, 2014 at 13:01

Erwin Brandstetter · Accepted Answer · 2017-12-28 05:20:04Z

3

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

edited Dec 28, 2017 at 5:20

answered Feb 16, 2014 at 4:03

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL: return a select statement in a procedure

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related