Passing a ResultSet to a Postgresql Function

Question

Is it possible to pass the results of a postgres request as input to another function?

As a very far-fetched example, let's say I have one request, for example

SELECT id, name FROM users LIMIT 50 

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

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

Answer 1

It is not possible to pass an array of a typical RECORD type to the plpgsql function, which is essentially what you are trying to do.

What you can do is pass an array of a specific user to TYPE or a specific type of table row. In the example below, you can also change the data type of the argument for the username of the table [] (although this obviously means getting all the data in the row of the user table).

 CREATE TYPE trivial { "ID" integer, "NAME" text } CREATE OR REPLACE FUNCTION trivial_func(data trivial[]) RETURNS integer AS $BODY$ DECLARE BEGIN --Implementation here using data return 1; END$BODY$ LANGUAGE 'plpgsql' VOLATILE; 

Answer 2

I would take the problem on the other hand by naming the aggregate function for each record in the result set. It is not so flexible, but it may give you a hint of work.

As an example, run your trial issue:

 CREATE OR REPLACE FUNCTION myMin ( int,int ) RETURNS int AS $$ SELECT CASE WHEN $1 < $2 THEN $1 ELSE $2 END; $$ LANGUAGE SQL STRICT IMMUTABLE; CREATE AGGREGATE my_function ( int ) ( SFUNC = myMin, STYPE = int, INITCOND = 2147483647 --maxint ); SELECT my_function(id) from (SELECT * FROM Users LIMIT 50) x; 

Answer 3

You can use the cursor, but it is very impractical to calculate the minimum.

I would use a temporary table for this 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'); 

Basic moments

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

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

• You can expand this example to get more options. Search for sample code for dynamic SQL with EXECUTE .

→ SQLfiddle demo

Answer 4

I think there is no way to pass a recordset or table into a function (but I would be glad if I made a mistake). Best of all, I can suggest passing an array:

 create or replace function my_function(data int[]) returns int as $$ select min(x) from unnest(data) as x $$ language SQL; 

sql script demonstration