Обсуждение: FETCH a cursor inside a SELECT
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
On 29 Nov 2002 at 23:33, David Crawshaw wrote: > 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: How about defining two views? One view that returns matchclass1 and another selects on this view to eliminate duplicates. i.e. using views as functions/cursors returning multiple recordset.. Just a thought.. Did not follow earlier thread so might be next to useless as well.. Bye Shridhar -- "We'll look into it": By the time the wheels make a full turn, we assume you will have forgotten about it, too.
On Fri, 29 Nov 2002 11:42 pm, Shridhar Daithankar wrote: > How about defining two views? One view that returns matchclass1 and > another selects on this view to eliminate duplicates. That would be a nice clean solution, but the functions are a search system. The integer being passed into the functions is a comparing id from another table, and then values are compared. As far as I know, a view is just a static definition. Reading the archives, I see 7.3 has just come out. Is it considered a production environment solution? There aren't any references on the website yet to anything other than 7.2.3. If so, I could rewrite these functions with SETOF and RETURN NEXT... David
On 29 Nov 2002 at 23:51, David Crawshaw wrote: > On Fri, 29 Nov 2002 11:42 pm, Shridhar Daithankar wrote: > > How about defining two views? One view that returns matchclass1 and > > another selects on this view to eliminate duplicates. > > That would be a nice clean solution, but the functions are a search > system. The integer being passed into the functions is a comparing id > from another table, and then values are compared. As far as I know, a > view is just a static definition. Not too sure but how about inserting the variable to be passed in a single row table and select on that table from view definition? Agreed not as straight forward but it cleanly splits the job between modules.. Tell us if this works.. I just pulled it out of thin air..;-) Bye Shridhar -- QOTD: "It wouldn't have been anything, even if it were gonna be a thing."
On Sat, 30 Nov 2002 12:00 am, Shridhar Daithankar wrote: > Not too sure but how about inserting the variable to be passed in a > single row table and select on that table from view definition? Now that's a hairy one! I bet it would work too, but I have multiple users on this db. :-( Another possibility would be removing all the logic from my function, and using a table join in a simple 'sql' function. However I was hoping to run more than one query a day, and being able to read my own code has always been a plus. Thanks anyway, David
On 30 Nov 2002 at 0:09, David Crawshaw wrote: > On Sat, 30 Nov 2002 12:00 am, Shridhar Daithankar wrote: > > Not too sure but how about inserting the variable to be passed in a > > single row table and select on that table from view definition? > > Now that's a hairy one! I bet it would work too, but I have multiple > users on this db. :-( I realised that after I shot the answer. Not to sure what I am saying here but how about the join on that view and the single column table? Will that solve your problem? Essentially same as what you have said but just in a different code organisation and that certainly would be readable all along..:-) Bye Shridhar -- Every living thing wants to survive. -- Spock, "The Ultimate Computer", stardate 4731.3
On Sat, 30 Nov 2002 00:23, you wrote: > I realised that after I shot the answer. Not to sure what I am saying > here but how about the join on that view and the single column table? > Will that solve your problem? Hmm, I don't quite see what you're saying here. I've rewritten my queries as 'Table Functions' (that name is a little ambiguous), and they work. So returning a SETOF MyTable, and then executing the query as: SELECT * FROM matchClass1(3); Works. I'll just have to upgrade my production environment to 7.3 ahead of schedule. Thanks for your ideas, David