Search Postgresql Archives

Re: How to call a function that returns a refcursor ?

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

 



2009/12/19 Postgres User <postgres.developer@xxxxxxxxx>:
> Hi,
>
> I have a function that returns a refcursor that I need to call from a
> second function.  In the second function, I'd like to read a column
> value from each row.  However, I'm having a problem accessing the rows
> of the refcursor.
> Can anyone point me to a working example of how to pull this off?
>
> This is the latest iteration of the function code that I've t

You cannot use SELECT FROM cursor. Look on FETCH statement. If you
need iterate over cursor in other function, then you have to use cycle
over cursor - some like

FETCH FROM refcursor_variable INTO recvariable;
LOOP
  EXIT WHEN NOT FOUND;
  do some with values
  FETCH FROM refcursor_variable INTO recvariable;
END LOOP;

see
http://www.postgresql.org/docs/8.3/static/plpgsql-cursors.html

Regards
Pavel Stehule

>
> CREATE OR REPLACE FUNCTION "return_cursor" (
> )
> RETURNS SETOF "pg_catalog"."refcursor" AS
> $body$
> DECLARE
>  rf refcursor;
> BEGIN
>  OPEN rf FOR
>    SELECT * FROM category;
>  RETURN Next rf;
> END;
> $body$
> LANGUAGE 'plpgsql' VOLATILE;
>
> CREATE OR REPLACE FUNCTION "test"."read_cursor" (
> )
> RETURNS integer [] AS
> $body$
> DECLARE
>  r record;
>  cat_list integer[];
> BEGIN
>  FOR r IN SELECT * FROM test.return_cursor() LOOP
>    cat_list = cat_list || r.category_id;
>  END LOOP;
>  Return cat_list;
> END;
> $body$
> LANGUAGE 'plpgsql';
>
> with this table struct:
>
> CREATE TABLE "category" (
>  "category_id" SERIAL,
>  "parent_id" INTEGER,
>  "category_name" VARCHAR(50)
> ) WITHOUT OIDS;
>
> --
> Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

-- 
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