From: Mario Splivalo <mario.splivalo@mobart.hr>
To: Adriana Marcela Aguirre <ing_adriana_aguirre@yahoo.com.ar>
Cc: pgsql-sql@postgresql.org
Subject: Re: Consulta!
Date: Tue, 18 Oct 2005 16:49:59 +0200
Message-ID: <1129646999.7511.5.camel@ekim> (raw)
In-Reply-To: <20051018142700.79582.qmail@web36706.mail.mud.yahoo.com>
References: <20051018142700.79582.qmail@web36706.mail.mud.yahoo.com>
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."
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mario.splivalo@mobart.hr, ing_adriana_aguirre@yahoo.com.ar
Subject: Re: Consulta!
In-Reply-To: <1129646999.7511.5.camel@ekim>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox