CODEWITHSUNDEEP
sqlpostgresqlplpgsqlpostgresql-9.2
Ben Hamner
Ben Hamner

Reputation: 4715

Passing a ResultSet into a Postgresql Function

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

Upvotes: 10

Views: 8993

Answers (4)

Le Droid
Le Droid

Reputation: 4774

I would take the problem on the other side, calling an aggregate function for each record of the result set. It's not as flexible but can gives you an hint to work on.

As an exemple to follow your sample problem:

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;

Upvotes: 5

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656381

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

-> SQLfiddle demo

Upvotes: 5

Fast Engy
Fast Engy

Reputation: 2011

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

What you can do is pass in an array of a specific user defined TYPE or of a particular table row type. In the example below you could also swap out the argument data type for the table name users[] (though this would obviously mean getting all data in the users table row).

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;

Upvotes: 3

roman
roman

Reputation: 117345

I think there's no way to pass recordset or table into function (but I'd be glad if i'm wrong). Best I could suggest is to pass array:

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

sql fiddle demo

Upvotes: 2

Related Questions