return X number of refcursors from a function

From: "Derek Liang" <derek(dot)liang(dot)ca(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: return X number of refcursors from a function
Date: 2008-12-30 22:33:46
Message-ID: 4c723ab70812301433k3310b126i5a42b696c0392736@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I tried to use the following code to retrieve the content of table1 4
times (in my application, the total number of refcursors that will be
returned is determined by the data in the database). I am getting the
error message says "ERROR: cursor "<unnamed portal 2>" already in
use".

Thank you in advance!

dl

--Start of the code
--DROP FUNCTION myfunc(int);

CREATE FUNCTION myfunc(int) RETURNS SETOF refcursor AS $$
DECLARE i int;
r refcursor;
BEGIN
i := $1;
WHILE i>0 LOOP
RAISE NOTICE 'loop count %;', i;
i := i-1;

OPEN r FOR SELECT * FROM table1;
RETURN NEXT r;
END LOOP;

RETURN;
END;
$$ LANGUAGE plpgsql;

BEGIN;
SELECT * FROM myfunc(4);
COMMIT;

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2008-12-30 22:55:14 Re: "disappearing" rows in temp table, in recursing trigger
Previous Message Eric Worden 2008-12-30 20:38:12 Re: "disappearing" rows in temp table, in recursing trigger