Thread: FETCH a cursor inside a SELECT

FETCH a cursor inside a SELECT

From
David Crawshaw
Date:
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

Re: FETCH a cursor inside a SELECT

From
"Shridhar Daithankar"
Date:
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.


Re: FETCH a cursor inside a SELECT

From
David Crawshaw
Date:
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

Re: FETCH a cursor inside a SELECT

From
"Shridhar Daithankar"
Date:
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."


Re: FETCH a cursor inside a SELECT

From
David Crawshaw
Date:
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


Re: FETCH a cursor inside a SELECT

From
"Shridhar Daithankar"
Date:
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


Re: FETCH a cursor inside a SELECT

From
David Crawshaw
Date:
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