This is a multi-part message in MIME format.
--Boundary_(ID_bvUHanHEVT60/w6NsSufQg)
Content-type: text/plain; charset=us-ascii
Content-transfer-encoding: 7BIT
Hello,
I'm using PostgreSQL 7.4
I have a function wich use temporary tables. I read about temporary tables
and they exists during the session.
But i have to call this function many times in the same sesion with
diferents parameters and expecting different results. So, there is a problem
because the temporary table already exists during the second execution of
the funcition.
To avoid this, I used this sintax after de create table statement "ON COMMIT
DROP" which destroy the table in the next commit.
for example, If i run this script many times in the same session there
weren't problems:
begin;
create temporary table test(x integer) ON COMMIT DROP;
INSERT INTO test values(1);
select * from test;
commit;
Then I tried to use this in function:
CREATE OR REPLACE FUNCTION "public"."f_test" () RETURNS SETOF
"pg_catalog"."record" AS'
BEGIN
CREATE TEMPORARY TABLE test( x integer ) ON COMMIT DROP;
INSERT INTO test values (1);
--RETORNA LOS RESULTADOS
FOR res IN SELECT x FROM test LOOP
RETURN NEXT res;
END LOOP;
RETURN;
END;
'LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY DEFINER;
and then I executed the function this way:
BEGIN;
SELECT * FROM f_test() AS R(x INTEGER);
COMMIT;
but in the second execution, it falis with an error wich said that doesn't
exist the relation with OID XXXX... I supose it is because the table doesn't
exist because in the second execution the function couldn't create the table
or it is using an old reference of the dropped table.
I think if I put the begin and the commit inside the function, it will work.
I tried this way, but it doesn't compile:
CREATE OR REPLACE FUNCTION "public"."f_test" () RETURNS SETOF
"pg_catalog"."record" AS'
BEGIN
BEGIN;
CREATE TEMPORARY TABLE test( x integer ) ON COMMIT DROP;
.....
I tried too with START, but without success.
I'd appeciate some help.
Tanks,
Gerardo.
--Boundary_(ID_bvUHanHEVT60/w6NsSufQg)
Content-type: text/html; charset=us-ascii
Content-transfer-encoding: 7BIT
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.2800.1400" name=GENERATOR></HEAD>
<BODY>
<DIV>
<DIV dir=ltr align=left><SPAN class=772173421-21072004><FONT face=Arial><SPAN
class=531441916-22072004>Hello</SPAN>,</FONT></SPAN></DIV>
<DIV><SPAN class=772173421-21072004><FONT face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN class=772173421-21072004><SPAN class=531441916-22072004><FONT
face=Arial>I'm using PostgreSQL 7.4</FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=772173421-21072004><SPAN class=531441916-22072004><FONT
face=Arial></FONT></SPAN></SPAN> </DIV>
<DIV><SPAN class=772173421-21072004><SPAN class=531441916-22072004><FONT
face=Arial>I have a function wich use temporary tables. I read about temporary
tables and they exists during the session.</FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=772173421-21072004><SPAN class=531441916-22072004><FONT
face=Arial>But i have to call this function many times in the same sesion with
diferents parameters and expecting different results. So, there is a problem
because the temporary table already exists during the second execution of the
funcition.</FONT></SPAN></SPAN></DIV>
<DIV><FONT face=Arial><SPAN class=772173421-21072004><SPAN
class=531441916-22072004></SPAN></SPAN><SPAN class=772173421-21072004><SPAN
class=531441916-22072004></SPAN></SPAN></FONT> </DIV>
<DIV><SPAN class=772173421-21072004><SPAN class=531441916-22072004><FONT
face=Arial>To avoid this, I used this sintax after de create table statement "ON
COMMIT DROP" which destroy the table in the next
commit.</FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=772173421-21072004><SPAN class=531441916-22072004><FONT
face=Arial></FONT></SPAN></SPAN> </DIV>
<DIV><SPAN class=772173421-21072004><SPAN class=531441916-22072004><FONT
face=Arial>for example, If i run this script many times in the same session
there weren't problems:</FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=772173421-21072004><SPAN class=531441916-22072004>
<DIV><SPAN class=772173421-21072004><FONT face=Arial>begin;</FONT></SPAN></DIV>
<DIV><SPAN class=772173421-21072004><FONT face=Arial>create temporary
table <SPAN class=531441916-22072004>test</SPAN>(x integer) ON COMMIT
DROP;</FONT></SPAN></DIV>
<DIV><SPAN class=772173421-21072004><FONT face=Arial>INSERT INTO <SPAN
class=531441916-22072004>test</SPAN> values(1);</FONT></SPAN></DIV>
<DIV><SPAN class=772173421-21072004><FONT face=Arial>select * from<SPAN
class=531441916-22072004> test</SPAN>;</FONT></SPAN></DIV>
<DIV><SPAN class=772173421-21072004><FONT
face=Arial>commit;</FONT></SPAN></DIV></SPAN></SPAN></DIV>
<DIV><SPAN class=772173421-21072004><SPAN class=531441916-22072004><FONT
face=Arial></FONT></SPAN></SPAN> </DIV>
<DIV><SPAN class=772173421-21072004><SPAN class=531441916-22072004><FONT
face=Arial>Then I tried to use this in function: </FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=772173421-21072004><SPAN class=531441916-22072004><FONT
face=Arial></FONT></SPAN></SPAN> </DIV>
<DIV><SPAN class=772173421-21072004><FONT face=Arial><SPAN
class=187244115-21072004>CREATE OR REPLACE FUNCTION "public"."f_<SPAN
class=531441916-22072004>test</SPAN>" () RETURNS SETOF "pg_catalog"."record"
AS'<BR></SPAN><SPAN class=187244115-21072004>BEGIN</SPAN></FONT></DIV>
<DIV>
<DIV><FONT face=Arial><SPAN class=187244115-21072004> CREATE
TEMPORARY TABLE <SPAN class=531441916-22072004>test</SPAN>(</SPAN><SPAN
class=187244115-21072004> x integer</SPAN><SPAN class=187244115-21072004>
)<SPAN class=772173421-21072004> </SPAN>ON COMMIT DROP;</SPAN></FONT></DIV>
<DIV><SPAN class=187244115-21072004><FONT face=Arial>
</FONT></SPAN></DIV>
<DIV><SPAN class=187244115-21072004><FONT face=Arial> INSERT
INTO <SPAN class=531441916-22072004>test</SPAN><SPAN
class=772173421-21072004> values (1);</SPAN></FONT></SPAN></DIV>
<DIV><SPAN class=187244115-21072004><FONT face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN class=187244115-21072004><FONT face=Arial>
--RETORNA LOS RESULTADOS<BR> FOR res IN SELECT x
FROM <SPAN
class=531441916-22072004>test</SPAN> LOOP<BR>
RETURN NEXT r<SPAN class=531441916-22072004>es</SPAN>;<BR> END
LOOP;<BR> RETURN;<BR>END;<BR>'LANGUAGE 'plpgsql' IMMUTABLE
CALLED ON NULL INPUT SECURITY DEFINER;</FONT></SPAN></DIV>
<DIV><SPAN class=187244115-21072004><FONT face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN class=187244115-21072004><SPAN class=531441916-22072004><FONT
face=Arial>and then I executed the function this
way:</FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=187244115-21072004><SPAN class=531441916-22072004><FONT
face=Arial>BEGIN;</FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=187244115-21072004><SPAN class=531441916-22072004><FONT
face=Arial>SELECT * FROM f_test() AS R(x INTEGER);</FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=187244115-21072004><SPAN class=531441916-22072004><FONT
face=Arial>COMMIT;</FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=187244115-21072004><SPAN class=531441916-22072004><FONT
face=Arial></FONT></SPAN></SPAN> </DIV>
<DIV><SPAN class=187244115-21072004><SPAN class=531441916-22072004><FONT
face=Arial>but in the second execution, it falis with an error wich said that
doesn't exist the relation with OID XXXX... I supose it is because the table
doesn't exist because in the second execution the function couldn't create the
table or it is using an old reference of the dropped
table.</FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=187244115-21072004><SPAN class=531441916-22072004><FONT
face=Arial></FONT></SPAN></SPAN> </DIV>
<DIV><SPAN class=187244115-21072004><SPAN class=531441916-22072004><FONT
face=Arial>I think if I put the begin and the commit inside the function, it
will work.</FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=187244115-21072004><SPAN class=531441916-22072004><FONT
face=Arial></FONT></SPAN></SPAN> </DIV>
<DIV><SPAN class=187244115-21072004><SPAN class=531441916-22072004><FONT
face=Arial>I tried this way, but it doesn't compile:</FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=187244115-21072004><SPAN class=531441916-22072004><SPAN
class=772173421-21072004><FONT face=Arial><SPAN class=187244115-21072004>CREATE
OR REPLACE FUNCTION "public"."f_<SPAN class=531441916-22072004>test</SPAN>" ()
RETURNS SETOF "pg_catalog"."record" AS'<BR></SPAN><SPAN
class=187244115-21072004>BEGIN</SPAN></FONT></SPAN></DIV>
<DIV>
<DIV><SPAN class=772173421-21072004><SPAN class=187244115-21072004><SPAN
class=531441916-22072004><FONT face=Arial>
BEGIN;</FONT></SPAN></SPAN></DIV>
<DIV>
<DIV><FONT face=Arial><SPAN class=187244115-21072004> CREATE
TEMPORARY TABLE <SPAN class=531441916-22072004>test</SPAN>(</SPAN><SPAN
class=187244115-21072004> x integer</SPAN><SPAN class=187244115-21072004>
)<SPAN class=772173421-21072004> </SPAN>ON COMMIT DROP;</SPAN></FONT></DIV>
<DIV><SPAN class=187244115-21072004><SPAN class=531441916-22072004><FONT
face=Arial> .....</FONT></SPAN></SPAN></DIV></DIV></SPAN></SPAN></SPAN></DIV>
<DIV><SPAN class=187244115-21072004><SPAN class=531441916-22072004><FONT
face=Arial>I tried too with START, but without
success.</FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=187244115-21072004><SPAN class=531441916-22072004><FONT
face=Arial></FONT></SPAN></SPAN> </DIV>
<DIV><SPAN class=187244115-21072004><SPAN class=531441916-22072004><FONT
face=Arial>I'd appeciate some help.</FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=187244115-21072004><SPAN class=531441916-22072004><FONT
face=Arial></FONT></SPAN></SPAN> </DIV>
<DIV><SPAN class=187244115-21072004><SPAN class=531441916-22072004><FONT
face=Arial>Tanks,</FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=187244115-21072004><SPAN class=531441916-22072004><FONT
face=Arial>Gerardo.</FONT></SPAN></SPAN></DIV>
<DIV><FONT face=Arial></FONT></SPAN><SPAN class=772173421-21072004><SPAN
class=772173421-21072004><SPAN class=187244115-21072004><SPAN
class=772173421-21072004></SPAN></SPAN> </DIV></DIV></SPAN></SPAN><!--StartFragment --></DIV></BODY></HTML>
--Boundary_(ID_bvUHanHEVT60/w6NsSufQg)--
