From: | Adriana Marcela Aguirre <ing_adriana_aguirre(at)yahoo(dot)com(dot)ar> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Consulta! |
Date: | 2005-10-18 14:26:59 |
Message-ID: | 20051018142700.79582.qmail@web36706.mail.mud.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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í
From | Date | Subject | |
---|---|---|---|
Next Message | Mario Splivalo | 2005-10-18 14:49:59 | Re: Consulta! |
Previous Message | Richard Huxton | 2005-10-17 16:57:52 | Re: Design problemi : using the same primary keys for inherited |