Обсуждение: Executing a user created function twice give an error

Поиск
Список
Период
Сортировка

Executing a user created function twice give an error

От
Wajid Khattak
Дата:
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

Re: Executing a user created function twice give an error

От
"A. Kretschmer"
Дата:
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

Re: Executing a user created function twice give an error

От
Wajid Khattak
Дата:
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

Re: Executing a user created function twice give an error

От
"A. Kretschmer"
Дата:
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

Re: Executing a user created function twice give an error

От
Raymond O'Donnell
Дата:
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
------------------------------------------------------------------

Re: Executing a user created function twice give an error

От
Wajid Khattak
Дата:
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

Re: Executing a user created function twice give an error

От
"A. Kretschmer"
Дата:
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

Re: Executing a user created function twice give an error

От
"Grzegorz Jaśkiewicz"
Дата:
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

Re: Executing a user created function twice give an error

От
Wajid Khattak
Дата:
Thanks for all of you help. It's working now :-)

Чтобы сделать работу с сайтом удобнее, мы используем cookie и аналитический сервис «Яндекс.Метрика». Продолжая пользоваться сайтом, вы соглашаетесь с их использованием.