Subject: Re: Consulta!
From: Mario Splivalo <mario.splivalo@mobart.hr>
Reply-To: mario.splivalo@mobart.hr
To: Adriana Marcela Aguirre <ing_adriana_aguirre@yahoo.com.ar>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <20051018142700.79582.qmail@web36706.mail.mud.yahoo.com>
References: <20051018142700.79582.qmail@web36706.mail.mud.yahoo.com>
Content-Type: text/plain
Organization: Mob-Art
Date: Tue, 18 Oct 2005 16:49:59 +0200
Message-Id: <1129646999.7511.5.camel@ekim>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

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."