Did try by encapsulating the create and drop queries within EXECUTE, but it
seems to be giving the same error. The changed 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
EXECUTE 'CREATE TEMPORARY TABLE __distances__temp (link_Id
varchar,calc_distance float8)';
INSERT INTO __distances__temp(link_Id,calc_distance)
SELECT hapms2.sect_label as
link_Id,distance(hapms2.geom,ST_GeomFromEWKT(p_pointtobesnapped)) as distance
FROM 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 hapms_road hapms1
WHERE (hapms1.geom && ST_box2d(GeomFromEWKT(p_currentBoundingBox))) AND
distance(hapms1.geom, GeomFromEWKT(p_pointToBeSnapped)) < all
(SELECT calc_distance FROM __distances__temp WHERE hapms1.sect_label <>
link_Id);
SELECT INTO v_snappedPoint
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))));
EXECUTE '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;
///////////////////////////
----- Original Message -----
From: "A. Kretschmer" <[EMAIL PROTECTED]>
To: pgsql-general@postgresql.org
Sent: 02 December 2008 12:50:44 o'clock (GMT) Europe/London
Subject: Re: [GENERAL] Executing a user created function twice give an error
am Tue, dem 02.12.2008, um 12:36:26 +0000 mailte Wajid Khattak folgendes:
> Thank for your reply.
>
> Could you please elaborate it a little bit further by referring to the
> fucntion as I am quite new to Postgres.
Sure, read the doku:
http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
PG cached the plan and the also the OID for affected tables. To avoid
this, use EXECUTE 'insert your query here' for create or delete tables
within plpgsql.
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
