Passing a ResultSet into a Postgresql Function
plpgsql, postgresql, postgresql-9.2, sql
Solution
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
Problem
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 ```