Thread: Consulta!
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
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;
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
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í
On Tue, 2005-10-18 at 14:26 +0000, Adriana Marcela Aguirre wrote: > Hola a todos!!... Hi! :) That's a known postges 'problem', one which, I guess, every pg-newbie encounters, especialy if he/she came from other RDBMSes. (I personaly came from MSSQL). > 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; > Postgres parsed your function, and compilled it, when first run. When doing so it refferenced your 'test' temp table via the oid. You do drop the table at the end of the proc, but prepared query plan for that function still references the OID that temp table had when it did exists. So, you need to stop postgres to 'prepare' query plan for that table. You do so by EXECUTEing the desired query, in your case: FOR res IN EXECUTE SELECT x FROM test LOOP ... It is more convinient to add the SQL query to a variable, and then exec that variable, like so: _SQL := 'SELECT a, b FROM tmpTable WHERE colText=' || quote_literal(someParametar); FOR res IN EXECUTE _SQL LOOP ... Mike -- Mario Splivalo Mob-Art mario.splivalo@mobart.hr "I can do it quick, I can do it cheap, I can do it well. Pick any two."