CODEWITHSUNDEEP
postgresqlstored-proceduresplpgsqldynamic-sqlpostgresql-9.2
kinske
kinske

Reputation: 607

PostgreSQL: return a select statement in a procedure

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).

Upvotes: 3

Views: 10293

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659247

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):

Upvotes: 3

Related Questions