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" <andreas.kretschmer@xxxxxxxxxxxxxx> To: pgsql-general@xxxxxxxxxxxxxx Sent: 02 December 2008 12:50:44 o'clock (GMT) Europe/London Subject: Re: 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
