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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
