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
