[GENERAL] Executing a user created function twice give an error

[Wajid Khattak]

Hi,

PostgreSQL 8.1.11

I have created a function that works fine when run for the first time after 
that it gives an error until I open another Query window.

The function is as follows

///////////////////////////////

CREATE OR REPLACE FUNCTION func_snappointtonetwork(p_pointtobesnapped 
"varchar", p_currentboundingbox "varchar")
  RETURNS text AS
$BODY$
DECLARE
        v_geom bytea;
        v_snappedPoint varchar;
        v_HAPMSSection varchar;
        v_road varchar;
        v_area varchar;
        v_cWay varchar;
        v_cWayDirection varchar;
BEGIN
        SELECT hapms2.sect_label as 
link_Id,distance(hapms2.geom,ST_GeomFromEWKT(p_pointToBeSnapped)) as distance
        INTO TEMPORARY TABLE __distances__temp
        FROM public.hapms_road hapms2  WHERE (hapms2.geom && 
ST_box2d(ST_GeomFromEWKT(p_currentBoundingBox)));

        SELECT INTO v_geom,v_HAPMSSection,v_road,v_area,v_cWay,v_cWayDirection 
        
asEWKB(hapms1.geom),hapms1.sect_label,hapms1.roa_number,hapms1.area_name,hapms1.funct_name,hapms1.direc_code
        FROM public.hapms_road hapms1 
        WHERE (hapms1.geom && ST_box2d(GeomFromEWKT(p_currentBoundingBox))) AND 
distance(hapms1.geom, GeomFromEWKT(p_pointToBeSnapped))  < all 
        (SELECT distance FROM __distances__temp WHERE public.hapms1.sect_label 
<> link_Id);
        
        SELECT 
ST_AsEWKT(ST_line_interpolate_point(ST_LineMerge(ST_GeomFromEWKB(v_geom)),ST_line_locate_point(ST_LineMerge(ST_GeomFromEWKB(v_geom)),ST_GeomFromEWKT(p_pointToBeSnapped))))
        INTO v_snappedPoint;

        DROP TABLE __distances__temp;

        RETURN v_snappedPoint || '|' || v_HAPMSSection || '|' || v_road || '|' 
|| v_area || '|' || v_cWay || ' ' || v_cWayDirection;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION func_snappointtonetwork(p_pointtobesnapped "varchar", 
p_currentboundingbox "varchar") OWNER TO postgres;

///////////////////////////////

Error: 

ERROR:  relation with OID 100412 does not exist
CONTEXT:  SQL statement "SELECT  
asEWKB(hapms1.geom),hapms1.sect_label,hapms1.roa_number,hapms1.area_name,hapms1.funct_name,hapms1.direc_code
 FROM hapms_road hapms1 WHERE (hapms1.geom && ST_box2d(GeomFromEWKT( $1 ))) AND 
distance(hapms1.geom, GeomFromEWKT( $2 )) < all (SELECT distance FROM 
__distances__temp WHERE hapms1.sect_label <> link_Id)"
PL/pgSQL function "func_snappointtonetwork" line 14 at select into variables

////////////////////////////////////

Any help in this matter would be highly appreciated.

regards,

W Khattak



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general