cursor "" already in use

Поиск
Список
Период
Сортировка
От Stathis Stergou
Тема cursor "" already in use
Дата
Msg-id 431834E0.5010802@yahoo.gr
обсуждение исходный текст
Ответы Re: cursor "" already in use  (Michael Fuhr <mike@fuhr.org>)
Список 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
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 


В списке pgsql-sql по дате отправления:

Предыдущее
От: Richard Huxton
Дата:
Сообщение: Re: Returning with the inserted id
Следующее
От: Michael Fuhr
Дата:
Сообщение: Re: cursor "" already in use