Postgresql – How to use RETURNS TABLE with an existing table in PostgreSQL

postgresqlpostgresql-9.5set-returning-functions

From the PostgreSQL documentation here:

There is another way to declare a function as returning a set, which
is to use the syntax RETURNS TABLE(columns). … This notation is specified in recent versions of the SQL standard, and thus may be more portable than using SETOF.

This sounds as if RETURNS TABLE is a newer, more portable style to return multiple rows. But I am not sure if the two syntaxes are equivalent.

I was wondering if we can actually use RETURNS TABLE to replace RETURNS SETOF?

In particular, one case I haven't figured out is: if we have an existing table foo and its associated composite type, how can we use it in RETURNS TABLE?

Using the example for the above link, can the following be rewritten using RETURNS TABLE:

CREATE TABLE foo (fooid int, foosubid int, fooname text);
CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
    SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;

So far, I tried to use RETURNS TABLE (foo.*) and RETURNS TABLE (foo), which didn't work.

Best Answer

PS: sorry for my comments, I was developing a complex code and I have a small error that seems a stupid PostgreSQL restriction on a "returing table" part... I was stupid, instead of concentrating and solving it, I used the internet (search engine put me here). Now, this wiki-answer is to help other readers, called by search engine and attracted by the title of the question.

Thanks to @dezso (was a correct answer) and, please all readers, you can edit this question to be more didactic, it is a Wiki.

Since PostgreSQL-v8 we can do it! We can RETURNS EXISTING_TABLE_NAME

In its Guide, in all PostgreSQL versions, from since pg v8 to current version, all have a section named "SQL Functions as Table Sources". Let's reproduce the Guide's example with some simplifications:

CREATE TABLE foo (fooid int, foosubid int, fooname text);
INSERT INTO foo VALUES (1, 1, 'Joe'), (1, 2, 'Ed'), (2, 1, 'Mary');

CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
    SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;

SELECT * FROM getfoo(1);

It is running as expected, it is perfect!

 fooid | foosubid | fooname
-------+----------+---------
     1 |        1 | Joe
     1 |        2 | Ed

The question "How to use RETURNS TABLE with an existing table in PostgreSQL?" have a good answer since pg v8... This is the way we do it in the last 15 years, the syntax is:
  RETURNS SETOF <EXISTING_TABLE_NAME>.

Use clause TABLE as instantaneous CREATE TABLE for returning

The @tinlyx's confusion, explainded on his question, is about the use of the clause TABLE instead of SETOF... To think using the "PostgreSQL syntax logic", we must first remember that RETURN <EXISTING_TABLE_NAME> is also valid, and it has the same behavior that RETURN <EXISTING_TYPE_NAME>. Is natural to return only one row.

Next step, remember that we declare a tuple with the CREATE TABLE clause (<tuple_description>), therefore, a good syntax to express an "instant-define-tuple table" is RETURN TABLE (<tuple_description>), and it makes sense to return TABLE -type, which is like an array type, will return several instances (TABLE is a set of tuples).

Next step, remember that we declare a tuple with the CREATE TABLE (<tuple_description>) clause, therefore, a good syntax to express an "instantaneous table-definition" is RETURN TABLE (<tuple_description>); and it makes sense to return Table-type, that is like Array-type, they return multiple instances (TABLE is a set of tuples).

The "modern thing" in PostgreSQL (!) is what @ZiggyCrueltyfreeZeitgeister showed, the RETURNS TABLE (LIKE <table_name>) syntax.

CREATE FUNCTION getfoo2(int) RETURNS TABLE (LIKE foo) AS $$ -- working fine!
    SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;
SELECT * FROM getfoo2(1); -- same result as getfoo(1)

Many ways to do the same, a summary:

Expliciting the table name (two ways) or type name:

  • RETURNS TABLE (LIKE <table_name>) (modern and good)
  • RETURNS SETOF <table_name> (old but good)
  • RETURNS SETOF <type_name> (after CREATE TYPE <type_name> (<tuple_description>))

Implicit/generic ways, by anonymous types:

  • RETURNS SETOF RECORD (generic but somethimes a problem)
  • (exist something as?) RETURNS SETOF ROW?

Instantaneous table-definition:

  • RETURNS TABLE (<tuple_description>)
  • (no RETURNS) using OUT in the parameter list.

For the last case, using our example to illustrate:
CREATE FUNCTION getfoo(int, OUT fooid int, OUT foosubid int, OUT fooname text)

For dynamic and/or polymophic input you must check this explanation.

Best practice?

There are many ways to do the same, so, there are a "best one"?

As syntax I prefer the use of RETURNS TABLE (LIKE <table_name>), that is explicit: no confusion with "implicit RECORD", no fear of incompatibilities...

Important for library management, DROP TABLE foo CASCADE will drop also the function: in any syntax (returns table or returns setof) PostgreSQL will do a good job.

drop table foo cascade;
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to function getfoo(integer)
drop cascades to function getfoo2(integer)
Related Question