Hi all,
When you create a temporary table using the CREATE TEMPORARY TABLE
AS... syntax, does the table get created (but left empty) if the
query returns no rows?
I'm seeing funny behaviour, and don't know whether it's my incomplete
understanding or whether something weird really is happening. Here's
a test case.....
-----[begin]-----
create table t1(f1 int4);
create or replace function testfunc() returns int4 as
$$
declare
TempInt int4;
begin
create temporary table TestTbl as
select * from t1;
select count(*) into TempInt from TestTbl;
drop table TestTbl;
return TempInt;
end;
$$ language 'plpgsql';
-----[end]-----
The first time I call "select testfunc();", I get 0 as expected.
However, subsequent calls return an error:
ERROR: relation with OID 80845 does not exist
CONTEXT: SQL statement "SELECT count(*) from TestTbl"
PL/pgSQL function "testfunc" line 7 at select into variables
Why should the temporary table be there the first time around and not
after that, since it's created anew at the start of the function?
Thanks for any help!
--Ray O'Donnell
-------------------------------------------------------------
Raymond O'Donnell http://www.galwaycathedral.org/recitals
rod@iol.ie Galway Cathedral Recitals
-------------------------------------------------------------
