Executing a user created function twice give an error

am Tue, dem 02.12.2008, um 11:11:44 +0000 mailte Wajid Khattak folgendes:

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.

Use EXECUTE for DDL-Statements, for instance, create a temp-table.

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

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.

regards,
----- Original Message -----
From: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
To: pgsql-general@postgresql.org
Sent: 02 December 2008 11:23:20 o'clock (GMT) Europe/London
Subject: Re: [GENERAL] Executing a user created function twice give an error

am Tue, dem 02.12.2008, um 11:11:44 +0000 mailte Wajid Khattak folgendes:

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.

Use EXECUTE for DDL-Statements, for instance, create a temp-table.

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

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

On 02/12/2008 12:50, A. Kretschmer wrote:

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.

This has been fixed in 8.3 - you no longer have to construct dynamically
any statements which touch temporary tables. If you have to do a lot of
this, it would be worth your while upgrading....not to mention the
performance improvements also.

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

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@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

am Thu, dem 04.12.2008, um 9:23:31 +0000 mailte Wajid Khattak folgendes:

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:

You need to execute the insert-statement also:

test=# create or replace function tmp_table() returns int as $$begin
execute 'create temporary table tmp_foo(i int)'; insert into tmp_foo
values(1);drop table tmp_foo; return 1;end;$$language plpgsql;
CREATE FUNCTION
test=*#
test=*#
test=*# select * from tmp_table();
tmp_table
-----------
1
(1 row)

test=*# select * from tmp_table();
ERROR: relation with OID 187431854 does not exist
CONTEXT: SQL statement "insert into tmp_foo values(1)"
PL/pgSQL function "tmp_table" line 1 at SQL statement
test=!# rollback;
ROLLBACK
test=# create or replace function tmp_table() returns int as $$begin
execute 'create temporary table tmp_foo(i int)'; execute 'insert into
tmp_foo values(1)';drop table tmp_foo; return 1;end;$$language plpgsql;
CREATE FUNCTION
test=*# select * from tmp_table();
tmp_table
-----------
1
(1 row)

test=*# select * from tmp_table();
tmp_table
-----------
1
(1 row)

test=*# select * from tmp_table();
tmp_table
-----------
1
(1 row)

test=*#

Peculiar, the drop table works without execute...

Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

On Thu, Dec 4, 2008 at 9:45 AM, A. Kretschmer
<andreas.kretschmer@schollglas.com> wrote:

test=*# select * from tmp_table();
ERROR: relation with OID 187431854 does not exist

that's a known problem, it was fixed in 8.3. I would strongly advice
you to upgrade, shall you depend on temporary tables in plpgsql.

--
GJ

Thanks for all of you help. It's working now :-)