pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedproblem with cursur within a scriptfile
4+ messages / 3 participants
[nested] [flat]
* problem with cursur within a scriptfile
@ 2004-02-22 16:50 Cornelia Boenigk <poppcorn@cornelia-boenigk.de>
0 siblings, 1 reply; 4+ messages in thread
From: Cornelia Boenigk @ 2004-02-22 16:50 UTC (permalink / raw)
To: pgsql-general@postgresql.org; +Cc: pgsql-general@postgresql.org
Hi everybody
I defined a function returning refcursor.
CREATE FUNCTION cursorname (refcursor, character varying, character
varying) RETURNS refcursor ...
which does a select with username and serachphrase.
If I call
BEGIN;
SELECT cursorname(`test`,`username`,`searchphrase`);
MOVE 10 in test;
FETCH 25 from test;
COMMIT;
inside psql everything works well. If I put this code into a
scriptfile and call it, I get the following errormessage:
cannot handle multiple result groups
Why? What does this mean?
I use a webserver which has 'one' persistent connection to the
database. Executing the script the connection breaks. Trying to do a
rollback the database returns that there is no transaction in
progress.
Any help is welcome
Regards
Conni
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: problem with cursur within a scriptfile
@ 2004-02-22 17:11 Bill Moran <wmoran@potentialtech.com>
parent: Cornelia Boenigk <poppcorn@cornelia-boenigk.de>
0 siblings, 1 reply; 4+ messages in thread
From: Bill Moran @ 2004-02-22 17:11 UTC (permalink / raw)
To: Cornelia Boenigk <poppcorn@cornelia-boenigk.de>; +Cc: pgsql-general@postgresql.org
Cornelia Boenigk wrote:
> Hi everybody
>
> I defined a function returning refcursor.
>
> CREATE FUNCTION cursorname (refcursor, character varying, character
> varying) RETURNS refcursor ...
> which does a select with username and serachphrase.
>
> If I call
>
> BEGIN;
> SELECT cursorname(`test`,`username`,`searchphrase`);
> MOVE 10 in test;
> FETCH 25 from test;
> COMMIT;
>
> inside psql everything works well. If I put this code into a
> scriptfile and call it, I get the following errormessage:
>
> cannot handle multiple result groups
>
> Why? What does this mean?
>
> I use a webserver which has 'one' persistent connection to the
> database. Executing the script the connection breaks. Trying to do a
> rollback the database returns that there is no transaction in
> progress.
>
> Any help is welcome
Sure seems like whatever database client you're using doesn't really
support transactions. Is is possible that each SQL command is being
issued seperatly to the backend in "unchained" mode? You might want
to provide the list with some details on the Postgres client you're
using and how you're using it. Then, someone with experience with
that client can make a specific suggestion.
--
Bill Moran
Potential Technologies
http://www.potentialtech.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: problem with cursur within a scriptfile
@ 2004-02-22 21:54 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bill Moran <wmoran@potentialtech.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2004-02-22 21:54 UTC (permalink / raw)
To: Bill Moran <wmoran@potentialtech.com>; +Cc: Cornelia Boenigk <poppcorn@cornelia-boenigk.de>; pgsql-general@postgresql.org
Bill Moran <wmoran@potentialtech.com> writes:
> Cornelia Boenigk wrote:
>> BEGIN;
>> SELECT cursorname(`test`,`username`,`searchphrase`);
>> MOVE 10 in test;
>> FETCH 25 from test;
>> COMMIT;
>>
>> inside psql everything works well. If I put this code into a
>> scriptfile and call it, I get the following errormessage:
>>
>> cannot handle multiple result groups
> Sure seems like whatever database client you're using doesn't really
> support transactions.
I'm guessing that the client-side library thinks the above is a single
SQL statement, and so it's surprised when two result sets (the SELECT
and then the FETCH) come back. This is probably due to misuse of the
library API, but without any details it's hard to offer advice.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: problem with cursur within a scriptfile
@ 2004-02-22 23:10 Cornelia Boenigk <poppcorn@cornelia-boenigk.de>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Cornelia Boenigk @ 2004-02-22 23:10 UTC (permalink / raw)
To: pgsql-general@postgresql.org
Hi Bill and Tom
Thank you for the hints. I think you pointed me in the right
direction.
Regards
Conni
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-02-22 23:10 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-22 16:50 problem with cursur within a scriptfile Cornelia Boenigk <poppcorn@cornelia-boenigk.de>
2004-02-22 17:11 ` Bill Moran <wmoran@potentialtech.com>
2004-02-22 21:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-22 23:10 ` Cornelia Boenigk <poppcorn@cornelia-boenigk.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox