pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcursor "<unnamed portal 1>" already in use
2+ messages / 2 participants
[nested] [flat]
* cursor "<unnamed portal 1>" already in use
@ 2005-09-02 11:17 Stathis Stergou <stathisstergou@yahoo.gr>
0 siblings, 1 reply; 2+ messages in thread
From: Stathis Stergou @ 2005-09-02 11:17 UTC (permalink / raw)
To: pgsql-sql
Hi, list.
I've created a stored function in plpgsql which uses some functions from
postgis.
CREATE OR REPLACE FUNCTION "public"."bufferfeatures" (integer [], text,
text, double precision) RETURNS SETOF "public"."shapedummy" AS
$body$
DECLARE
source_layer_features ALIAS FOR $1;
source_layer ALIAS FOR $2;
target_layer ALIAS FOR $3;
buffer_radius ALIAS FOR $4;
source_rec shapedummy%ROWTYPE;
target_rec record;
return_rec shapedummy%ROWTYPE;
source_curs refcursor;
target_curs refcursor;
str text;
BEGIN
str := array_to_string(source_layer_features, ',');
str := 'ARRAY[' || str || ']';
open source_curs for EXECUTE 'SELECT * from getBuffer(' ||str|| ','
||quote_literal(source_layer)|| ',' ||quote_literal(buffer_radius)|| ')';
loop
fetch source_curs into source_rec;
EXIT WHEN NOT FOUND;
open target_curs for execute 'select gid, the_geom, intersects('
||quote_literal(source_rec.the_geom)|| ', the_geom) as iss from ' ||
target_layer;
loop
fetch target_curs into target_rec;
EXIT WHEN NOT FOUND;
if target_rec.iss = '1' then
return_rec.gid := target_rec.gid;
return_rec.the_geom := target_rec.the_geom;
RETURN NEXT return_rec;
end if;
end loop;
end loop;
CLOSE source_curs;
CLOSE target_curs;
RETURN ;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
I've tested the queries extensively and they return some results if i
run them with my own parameters from psql.
When i run " select * from bufferFeatures(ARRAY[42,31],'countries',
'cities', 2000) " i get the following error :
ERROR: cursor "<unnamed portal #a number here#>" already in use
CONTEXT: PL/pgSQL function "bufferfeatures" line 19 at open
Do you have any ideas ?
Thanks in advance.
Stathis Stergou
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: cursor "<unnamed portal 1>" already in use
@ 2005-09-02 13:12 Michael Fuhr <mike@fuhr.org>
parent: Stathis Stergou <stathisstergou@yahoo.gr>
0 siblings, 0 replies; 2+ messages in thread
From: Michael Fuhr @ 2005-09-02 13:12 UTC (permalink / raw)
To: Stathis Stergou <stathisstergou@yahoo.gr>; +Cc: pgsql-sql
On Fri, Sep 02, 2005 at 02:17:52PM +0300, Stathis Stergou wrote:
> ERROR: cursor "<unnamed portal #a number here#>" already in use
> CONTEXT: PL/pgSQL function "bufferfeatures" line 19 at open
You're trying to open a cursor that's already open. Close the
cursor at the end of the loop in which you opened it, so that it
gets closed before you try to open it again on the next loop
iteration.
--
Michael Fuhr
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-09-02 13:12 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-09-02 11:17 cursor "<unnamed portal 1>" already in use Stathis Stergou <stathisstergou@yahoo.gr>
2005-09-02 13:12 ` Michael Fuhr <mike@fuhr.org>
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