Re: Executing a user created function twice give an error

Did try by encapsulating the create and drop queries within EXECUTE, but it seems to be giving the same error. The
changedfunction 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@schollglas.com>
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