Thread: Executing a user created function twice give an error
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
Querywindow.
The 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
SELECT hapms2.sect_label as link_Id,distance(hapms2.geom,ST_GeomFromEWKT(p_pointToBeSnapped)) as distance
INTO TEMPORARY TABLE __distances__temp
FROM public.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 public.hapms_road hapms1
WHERE (hapms1.geom && ST_box2d(GeomFromEWKT(p_currentBoundingBox))) AND distance(hapms1.geom,
GeomFromEWKT(p_pointToBeSnapped)) < all
(SELECT distance FROM __distances__temp WHERE public.hapms1.sect_label <> link_Id);
SELECT
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))))
INTO v_snappedPoint;
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;
///////////////////////////////
Error:
ERROR: relation with OID 100412 does not exist
CONTEXT: SQL statement "SELECT
asEWKB(hapms1.geom),hapms1.sect_label,hapms1.roa_number,hapms1.area_name,hapms1.funct_name,hapms1.direc_codeFROM
hapms_roadhapms1 WHERE (hapms1.geom && ST_box2d(GeomFromEWKT( $1 ))) AND distance(hapms1.geom, GeomFromEWKT( $2 )) <
all(SELECT distance FROM __distances__temp WHERE hapms1.sect_label <> link_Id)"
PL/pgSQL function "func_snappointtonetwork" line 14 at select into variables
////////////////////////////////////
Any help in this matter would be highly appreciated.
regards,
W Khattak
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 anotherQuery 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 anotherQuery 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
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
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
changedfunction 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 :-)