From: Stathis Stergou <stathisstergou@yahoo.gr>
To: pgsql-sql@postgresql.org
Subject: cursor "<unnamed portal 1>" already in use
Date: Fri, 02 Sep 2005 14:17:52 +0300
Message-ID: <431834E0.5010802@yahoo.gr> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: stathisstergou@yahoo.gr
Subject: Re: cursor "<unnamed portal 1>" already in use
In-Reply-To: <431834E0.5010802@yahoo.gr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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