Thread: 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