Postgreql 8.0.3 temporary Table Strange behaviour
If you have a look to the following code, I'm trying to work around the limitation about execute, which doesn't accept "select into". So I call the function with a table name as parameter, the function insert a record in a temporary table, retrieve it and return it.
The strange thing, is that the table is created if it doesn't exist, and is dropped after a commit. Just like the sql is already parsed and cannot be re-parsed.
The log
log=# select my_func ('log');
NOTICE: Create the temp table
my_func
---------
52203
(1 row)
log=# select my_func ('log');
NOTICE: Create the temp table
ERROR: relation with OID 1203803 does not exist
CONTEXT: SQL statement "SELECT nb_ligne from tt where $1 = $2 "
PL/pgSQL function "my_func" line 18 at select into variables
The code
CREATE OR REPLACE FUNCTION my_func("varchar")
RETURNS int4 AS
$BODY$
declare
table_name alias for $1;
m_sql varchar;
nb int;
a varchar;
mTable varchar;
begin
mTable:='tt';
begin
select count(*) into nb from tt;
exception when undefined_table then
raise notice 'Create the temp table';
execute 'create temp table '||mTable||' (nb_ligne int8,table_name text) on
commit drop';
end;
m_sql:='insert into '||mTable||' select count(*),'''||table_name||''' from
'||table_name;
execute m_sql;
select nb_ligne into nb from tt where table_name=table_name;
-- commit;
return nb;
end;
$BODY$
LANGUAGE 'plpgsql' volatile;
Dany De Bontridder wrote:
If you have a look to the following code, I'm trying to work around
the limitation about execute, which doesn't accept "select into".
http://www.postgresql.org/docs/8.0/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
The results from SELECT commands are discarded by EXECUTE, and SELECT
INTO is not currently supported within EXECUTE. So there is no way to
extract a result from a dynamically-created SELECT using the plain
EXECUTE command. There are two other ways to do it, however: one is to
use the FOR-IN-EXECUTE loop form described in Section 35.7.4, and the
other is to use a cursor with OPEN-FOR-EXECUTE, as described in Section
35.8.2.
Do these two not solve your problem?
So
I call the function with a table name as parameter, the function
insert a record in a temporary table, retrieve it and return it.The strange thing, is that the table is created if it doesn't exist,
and is dropped after a commit. Just like the sql is already parsed
and cannot be re-parsed.
The final "select ... from tt ..." is just that.
--
Richard Huxton
Archonet Ltd
On Monday 24 April 2006 10:43, Richard Huxton wrote:
(...)
Do these two not solve your problem?
I knew those solutions, I was looking for a new one ;-) Because the " open in
execute" forces you to scan the whole table, that's what I wish to avoid, and
the "select count(*)" will be able to use index scan (faster) (in version
8.1 ?)
Thank you for helping,
Regards,
D.
2006/4/24, Dany De Bontridder <dany@alchimerys.be>:
and the "select count(*)" will be able to use index scan (faster) (in version 8.1 ?)
No, it won't.
--
Nicolas Barbier
http://www.gnu.org/philosophy/no-word-attachments.html