From: | David Crawshaw <david(at)zentus(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | FETCH a cursor inside a SELECT |
Date: | 2002-11-29 13:33:21 |
Message-ID: | 200211292333.21337.david@zentus.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I've been working with this all day, and I'm finally out of ideas.
Hopefully someone has encountered a situation like this before.
Originally I was going to write a function that returned a series of
id's, but this caused no end of confusion on PG 7.2.3. Instead, I've
decided to return cursors.
matchClass1 works as expected. However in matchClass2, I wish to exclude
all of the results from matchClass1. This inline fetch gives me the
following error calling matchClass2:
---
=> BEGIN; SELECT matchClass2('ref1', 0); COMMIT;
BEGIN
NOTICE: Error occurred while executing PL/pgSQL function matchclass2
NOTICE: line 6 at open
ERROR: parser: parse error at or near "ALL"
COMMIT
---
The functions are:
CREATE FUNCTION matchClass1(refcursor, integer) RETURNS refcursor AS '
BEGIN
OPEN $1 FOR SELECT MyId FROM MyTable;
RETURN $1;
END;
' LANGUAGE 'plpgsql';
CREATE FUNCTION matchClass2(refcursor, integer) RETURNS refcursor AS '
DECLARE
class1 refcursor;
BEGIN
class1 := matchClass1(class1, $2);
OPEN $1 FOR SELECT MyId FROM MyTable WHERE
MyId NOT IN (FETCH ALL IN class1);
RETURN $1;
END;
' LANGUAGE 'plpgsql';
For the sake of simplicity, I've stripped away the where clauses (about
25 lines each, hence my want to do this).
Can anyone offer any suggestions?
Thanks,
David
From | Date | Subject | |
---|---|---|---|
Next Message | Erwan DUROSELLE | 2002-11-29 13:34:25 | Re: Rp. : Re: French translation of 7.3 |
Previous Message | pginfo | 2002-11-29 12:01:43 | RPMS for 7.3 |