Re: Executing a user created function twice give an error - Mailing list pgsql-general

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