Search Postgresql Archives

Re: Sending Results From One Function As Input into Another Function

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux