From: | Wajid Khattak <wajid(dot)khattak(at)keynetix(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Executing a user created function twice give an error |
Date: | 2008-12-02 11:11:44 |
Message-ID: | 12965269.19071228216304682.JavaMail.root@zimbra.keynetix.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Stéphane A. Schildknecht | 2008-12-02 11:19:11 | Re: Trigger before delete does fire before, but delete doesn't not happen |
Previous Message | Simon Riggs | 2008-12-02 10:48:43 | Re: Favorite Tom Lane quotes |