|
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; |
