cursor "<unnamed portal 1>" already in use

From: Stathis Stergou <stathisstergou(at)yahoo(dot)gr>
To: pgsql-sql(at)postgresql(dot)org
Subject: cursor "<unnamed portal 1>" already in use
Date: 2005-09-02 11:17:52
Message-ID: 431834E0.5010802@yahoo.gr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Michael Fuhr 2005-09-02 13:12:11 Re: cursor "<unnamed portal 1>" already in use
Previous Message Richard Huxton 2005-09-02 09:33:36 Re: Returning with the inserted id