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;