FETCH a cursor inside a SELECT

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

Responses

Browse pgsql-general by date

  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