Обсуждение: cursor "" already in use

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
ALIASFOR $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 

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