Return a query from a function?

Question

I am using PostgreSQL 8.4 and I want to create a function that returns a query with many rows.
The following function does not work:

create function get_names(varchar) returns setof record AS $$
declare
    tname alias for $1;
    res setof record;
begin
    select * into res from mytable where name = tname;
    return res;
end;
$$ LANGUAGE plpgsql;

The type record only allows single row.

How to return an entire query? I want to use functions as query templates.

Please note that -though esthetically pleasing- set-of-rows functions tend to perform badly in real life. The point is that they will not be 'broken up' by the query optimiser. A simple query like "select from f() where not exists (select ... from f())" can lead two two, maybe even three sort steps. Use with care. — wildplasser, Sep 25 '11 at 21:44
@wildplasser: While that *can* happen if you actually nest table functions, they *do* perform perfectly fast as long as you don't. Faster than the query itself, actually, because the query plan can be cached. — Erwin Brandstetter, Sep 25 '11 at 21:52
@Erwin Brandstetter: Yes and no. Performed as a singleton, they may spare you the compilation and planning costs; used in combination with other "real" query elements, they can be disastrous, causing too many avoidable scans and sort steps. — wildplasser, Sep 25 '11 at 22:01
@wildplasser: "in combination with other query elements" = to nest. Is there any other way? I don't see a downside if I use a couple of table functions sequentially inside a transaction, or am I missing something here? — Erwin Brandstetter, Sep 25 '11 at 22:25
That would be a "function as in procedure". A void function, in C terms. Or maybe a scalar. But in the OP's sense: if you want a function to return a set of rows, you may actually want to do things with the result, such as combining it with other results. — wildplasser, Sep 25 '11 at 22:41
@wildplasser: I am explicitly talking about table functions. We have a number of web applications that get their data this way. And it works as fast as it gets. No downside, as long as you don't nest (like join a couple of them together). For that I would write a new table function. — Erwin Brandstetter, Sep 26 '11 at 00:07

Erwin Brandstetter · Accepted Answer · 2018-06-14T14:36:56.553

CREATE OR REPLACE FUNCTION get_names(_tname varchar)
  RETURNS TABLE (col_a integer, col_b text) AS
$func$
BEGIN
   RETURN QUERY
   SELECT t.col_a, t.col_b  -- must match RETURNS TABLE
   FROM   mytable t
   WHERE  t.name = _tname;    
END
$func$  LANGUAGE plpgsql;

Call like this:

SELECT * FROM get_names('name')

Major points:

Use RETURNS TABLE, so you don't have to provide a list of column names with every call.
Use RETURN QUERY, much simpler.
Table-qualify column names to avoid naming conflicts with identically named OUT parameters (including columns declared with RETURNS TABLE).
Use a named variable instead of ALIAS. Simpler, doing the same, and it's the preferred way.
A simple function like this could also be written in LANGUAGE sql:

CREATE OR REPLACE FUNCTION get_names(_tname varchar)
  RETURNS TABLE (col_a integer, col_b text) AS
$func$
SELECT t.col_a, t.col_b  --, more columns - must match RETURNS above
FROM   mytable t
WHERE  t.name = $1;
$func$ LANGUAGE sql;

Return a query from a function?

1 Answers1

Linked