Passing a ResultSet into a Postgresql Function

Question

Is it possible to pass the results of a postgres query as an input into another function?

As a very contrived example, say I have one query like

SELECT id, name
FROM users
LIMIT 50

and I want to create a function my_function that takes the resultset of the first query and returns the minimum id. Is this possible in pl/pgsql?

SELECT my_function(SELECT id, name FROM Users LIMIT 50); --returns 50

Answer 1

You could use a cursor, but that very impractical for computing a minimum.

I would use a temporary table for that purpose, and pass the table name for use in dynamic SQL:

CREATE OR REPLACE FUNCTION f_min_id(_tbl regclass, OUT min_id int) AS 
$func$
BEGIN

EXECUTE 'SELECT min(id) FROM ' || _tbl
INTO min_id;

END  
$func$ LANGUAGE plpgsql;

Call:

CREATE TEMP TABLE foo ON COMMIT DROP AS
SELECT id, name
FROM   users
LIMIT  50;

SELECT f_min_id('foo');

Major points

• The first parameter is of type regclass to prevent SQL injection. More info in this related answer on dba.SE.

• I made the temp table ON COMMIT DROP to limit its lifetime to the current transaction. May or may not be what you want.

• You can extend this example to take more parameters. Search for code examples for dynamic SQL with EXECUTE.

-> SQLfiddle demo