Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group