X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 9C685D95CD for ; Tue, 18 Oct 2005 11:27:01 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 68657-06 for ; Tue, 18 Oct 2005 14:26:55 +0000 (GMT) Received: from web36706.mail.mud.yahoo.com (web36706.mail.mud.yahoo.com [209.191.85.40]) by svr1.postgresql.org (Postfix) with SMTP id 7D1D1D94DD for ; Tue, 18 Oct 2005 11:26:55 -0300 (ADT) Received: (qmail 79584 invoked by uid 60001); 18 Oct 2005 14:27:00 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com.ar; h=Message-ID:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=bz0k5MAWjKXV244QFMbCUvnzKvL0/IEj7YGVM1Vw0SQLaxWFoGAsxNH0+nJpEz2OK9UTgRD2H4N6GheHU+ctJ/mGsdlosDurbZ6G3TvsQYZPnOCViNOVJt0lmcPyoIDER0eiaLEDX9+H/OaSV0l1CI6stVbilAPbjcL3em7nuRI= ; Message-ID: <20051018142700.79582.qmail@web36706.mail.mud.yahoo.com> Received: from [201.252.168.50] by web36706.mail.mud.yahoo.com via HTTP; Tue, 18 Oct 2005 14:26:59 GMT Date: Tue, 18 Oct 2005 14:26:59 +0000 (GMT) From: Adriana Marcela Aguirre Subject: Consulta! To: pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1779873410-1129645619=:79557" Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.001 required=5 tests=[HTML_MESSAGE=0.001] X-Spam-Level: X-Archive-Number: 200510/185 X-Sequence-Number: 23038 --0-1779873410-1129645619=:79557 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Hola a todos!!... El día 22-07-2004 Gerardo Castillo escribió lo siguiente: /******************************************** 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. *********************************************************************/ Yo tengo el mismo problema y no lo pude solucionar tampoco con Execute como se sugiere en otro mail... Alguien sabe cómo puedo solucionar este problema? --------------------------------- 1GB gratis, Antivirus y Antispam Correo Yahoo!, el mejor correo web del mundo Abrí tu cuenta aquí --0-1779873410-1129645619=:79557 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit
Hola a todos!!...
El día 22-07-2004 Gerardo Castillo escribió lo siguiente:
 
/********************************************
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.
*********************************************************************/
Yo tengo el mismo problema y no lo pude solucionar tampoco con Execute como se sugiere en otro mail...
Alguien sabe cómo puedo solucionar este problema?

1GB gratis, Antivirus y Antispam
Correo Yahoo!, el mejor correo web del mundo
Abrí tu cuenta aquí --0-1779873410-1129645619=:79557--