FETCH a cursor inside a SELECT
От | David Crawshaw |
---|---|
Тема | FETCH a cursor inside a SELECT |
Дата | |
Msg-id | 200211292333.21337.david@zentus.com обсуждение исходный текст |
Ответы |
Re: FETCH a cursor inside a SELECT
|
Список | 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
В списке pgsql-general по дате отправления: