DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.gr;
	h=Received:Message-ID:Date:From:User-Agent:X-Accept-Language:MIME-Version:To:Subject:Content-Type:Content-Transfer-Encoding;
	b=cNDfc7jKHsGE/70W2ZsiiYjzfrEXzhubNjbyhiOFrXBhpb+nMO5wp68yoCPytdc5VP3Tu66R6tm0VjDNWdldlNjUGwa4uwarMS68GT751gF/fnbLRJcVw02n8ryV38XKNBGfHyTOKBYUnMXIsc5nFhkKO4SsaS1p2smVabWS4i8=
	;
Message-ID: <431834E0.5010802@yahoo.gr>
Date: Fri, 02 Sep 2005 14:17:52 +0300
From: Stathis Stergou <stathisstergou@yahoo.gr>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.7.11) Gecko/20050728
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: cursor "<unnamed portal 1>" already in use
Content-Type: text/plain; charset=ISO-8859-7; format=flowed
Content-Transfer-Encoding: 7bit

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