Sending Results From One Function As Input into Another Function

Jeff Adams wrote:

I need to send the results (SETOF RECORDS) from one function into

another

function, to produce another result (SETOF RECORDS). I am not quite

sure how

to do get this done. The first function filters a large table down a

more

manageable dataset. I want to send the results of this first function

to

another function, where computations are performed. I could combine

into a

single function, but I would lose some flexibility that I would like

to

maintain by keeping the two functions separate. Preliminary research
suggests that cursors might be the way to go, but I am not too

experienced

with the use of cursors and was unable to find good examples. Any help

would

be greatly appreciated...

Here's an example:

SELECT * FROM test;

id | val
----+-------
1 | one
2 | two
3 | three
4 | four
(4 rows)

CREATE FUNCTION filter() RETURNS refcursor
LANGUAGE plpgsql STABLE STRICT AS
$$DECLARE
/* assignment gives the cursor a name */
curs refcursor := 'curs';
BEGIN
OPEN curs FOR
SELECT id, val FROM test WHERE id%2=0;
RETURN curs;
END;$$;

CREATE FUNCTION compute(curs refcursor) RETURNS text
LANGUAGE plpgsql STABLE STRICT AS
$$DECLARE
v test; -- row type for table
r text := '';
BEGIN
LOOP
FETCH curs INTO v;
EXIT WHEN v IS NULL;
r := r || v.val;
END LOOP;
RETURN r;
END;$$;

SELECT compute(filter());

compute
---------
twofour
(1 row)

Yours,
Laurenz Albe

On Mon, Sep 26, 2011 at 6:49 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:

Jeff Adams wrote:

I need to send the results (SETOF RECORDS) from one function into

another

function, to produce another result (SETOF RECORDS). I am not quite

sure how

to do get this done. The first function filters a large table down a

more

manageable dataset. I want to send the results of this first function

to

another function, where computations are performed. I could combine

into a

single function, but I would lose some flexibility that I would like

to

maintain by keeping the two functions separate. Preliminary research
suggests that cursors might be the way to go, but I am not too

experienced

with the use of cursors and was unable to find good examples. Any help

would

be greatly appreciated...

Here's an example:

SELECT * FROM test;

 id |  val
----+-------
 1 | one
 2 | two
 3 | three
 4 | four
(4 rows)

CREATE FUNCTION filter() RETURNS refcursor
  LANGUAGE plpgsql STABLE STRICT AS
$$DECLARE
  /* assignment gives the cursor a name */
  curs refcursor := 'curs';
BEGIN
  OPEN curs FOR
     SELECT id, val FROM test WHERE id%2=0;
  RETURN curs;
END;$$;

CREATE FUNCTION compute(curs refcursor) RETURNS text
  LANGUAGE plpgsql STABLE STRICT AS
$$DECLARE
  v test;  -- row type for table
  r text := '';
BEGIN
  LOOP
     FETCH curs INTO v;
     EXIT WHEN v IS NULL;
     r := r || v.val;
  END LOOP;
  RETURN r;
END;$$;

SELECT compute(filter());

 compute
---------
 twofour
(1 row)

Another method of doing this which I like to point out is via arrays
of composite types. It's suitable when the passed sets are relatively
small (say less than 10k) and is more flexible -- forcing all data
manipulation through FETCH is (let's be frank) pretty awkward and with
some clever work you can also involve the client application in a more
regular way. You can use an implict table type or a specially defined
composite type to convey the data:

create type t as (a int, b text, c timestamptz);

create function filter() returns t[] as
$$
select array(select row(a,b,c)::t from foo);
$$ language sql;

create function do_stuff(_ts t[]) returns void as
$$
declare
_t t;
begin
foreach _t in array _ts
loop
raise notice '%', _t;
end loop;
end;
$$ language plpgsql;

note: foreach in array feature is new to 9.1 -- 8.4+ use unnest() --
before that you have to hand roll unnest().

merlin

Thanks for the response Laurenz. I will give it a go...

Jeff

-----Original Message-----
From: Albe Laurenz [mailto:laurenz.albe@wien.gv.at]
Sent: Monday, September 26, 2011 7:50 AM
To: Jeff Adams *EXTERN*; pgsql-general@postgresql.org
Subject: RE: [GENERAL] Sending Results From One Function As Input into
Another Function

Jeff Adams wrote:

I need to send the results (SETOF RECORDS) from one function into

another

function, to produce another result (SETOF RECORDS). I am not quite

sure how

to do get this done. The first function filters a large table down a

more

manageable dataset. I want to send the results of this first function

to

another function, where computations are performed. I could combine

into a

single function, but I would lose some flexibility that I would like

to

maintain by keeping the two functions separate. Preliminary research
suggests that cursors might be the way to go, but I am not too

experienced

with the use of cursors and was unable to find good examples. Any help

would

be greatly appreciated...

Here's an example:

SELECT * FROM test;

id | val
----+-------
1 | one
2 | two
3 | three
4 | four
(4 rows)

CREATE FUNCTION filter() RETURNS refcursor
LANGUAGE plpgsql STABLE STRICT AS
$$DECLARE
/* assignment gives the cursor a name */
curs refcursor := 'curs';
BEGIN
OPEN curs FOR
SELECT id, val FROM test WHERE id%2=0;
RETURN curs;
END;$$;

CREATE FUNCTION compute(curs refcursor) RETURNS text
LANGUAGE plpgsql STABLE STRICT AS
$$DECLARE
v test; -- row type for table
r text := '';
BEGIN
LOOP
FETCH curs INTO v;
EXIT WHEN v IS NULL;
r := r || v.val;
END LOOP;
RETURN r;
END;$$;

SELECT compute(filter());

compute
---------
twofour
(1 row)

Yours,
Laurenz Albe