Обсуждение: create temp in function
Hi All! I'm new to this list, but I've been using PG for a couple of years now. I'm trying to do something in a function that I just can't seem to do.
If I do the following in psql or pgadmin:
I can do it over and over and over again without problem;
But if I create and run the following function, it bombs on the second run. It gives me:
Here is my function:
create or replace function testtemp()
returns boolean as
$body$
declare
query text;
relid integer;
cnt integer;
begin
create temp table schedrec (sch text, cl text, st text);
select into relid distinct(attrelid) from pg_attribute where attrelid = 'schedrec'::regclass;
raise notice 'relid: %', relid;
select into cnt count(*) from schedrec;
raise notice 'count: %', cnt;
drop table schedrec;
if relid is null then
return false;
else
return true;
end if;
end;
$body$
language plpgsql security definer;
Can anyone please help me with this?
TIA,
Kerri
--
Yuma Educational Computer Consortium
Compass Development Team
Kerri Reno
kreno@yumaed.org (928) 502-4240
.·:*¨¨*:·. .·:*¨¨*:·. .·:*¨¨*:·.
If I do the following in psql or pgadmin:
create temp table schedrec (sch text, cl text, st text);
select distinct(attrelid) from pg_attribute where attrelid = 'schedrec'::regclass;
select * from schedrec;
drop table schedrec;
select distinct(attrelid) from pg_attribute where attrelid = 'schedrec'::regclass;
select * from schedrec;
drop table schedrec;
I can do it over and over and over again without problem;
But if I create and run the following function, it bombs on the second run. It gives me:
compassdevel_lb=# select testtemp();
NOTICE: relid: 186270497
NOTICE: count: 0
testtemp
----------
t
(1 row)
compassdevel_lb=# select testtemp();
NOTICE: relid: <NULL>
ERROR: relation with OID 186270497 does not exist
CONTEXT: SQL statement "SELECT count(*) from schedrec"
PL/pgSQL function "testtemp" line 9 at select into variables
NOTICE: relid: 186270497
NOTICE: count: 0
testtemp
----------
t
(1 row)
compassdevel_lb=# select testtemp();
NOTICE: relid: <NULL>
ERROR: relation with OID 186270497 does not exist
CONTEXT: SQL statement "SELECT count(*) from schedrec"
PL/pgSQL function "testtemp" line 9 at select into variables
Here is my function:
create or replace function testtemp()
returns boolean as
$body$
declare
query text;
relid integer;
cnt integer;
begin
create temp table schedrec (sch text, cl text, st text);
select into relid distinct(attrelid) from pg_attribute where attrelid = 'schedrec'::regclass;
raise notice 'relid: %', relid;
select into cnt count(*) from schedrec;
raise notice 'count: %', cnt;
drop table schedrec;
if relid is null then
return false;
else
return true;
end if;
end;
$body$
language plpgsql security definer;
Can anyone please help me with this?
TIA,
Kerri
--
Yuma Educational Computer Consortium
Compass Development Team
Kerri Reno
kreno@yumaed.org (928) 502-4240
.·:*¨¨*:·. .·:*¨¨*:·. .·:*¨¨*:·.
am Mon, dem 21.04.2008, um 15:22:52 -0600 mailte Kerri Reno folgendes: > But if I create and run the following function, it bombs on the second run. It > gives me: > compassdevel_lb=# select testtemp(); > NOTICE: relid: 186270497 > NOTICE: count: 0 > testtemp > ---------- > t > (1 row) > > compassdevel_lb=# select testtemp(); > NOTICE: relid: <NULL> > ERROR: relation with OID 186270497 does not exist > CONTEXT: SQL statement "SELECT count(*) from schedrec" > PL/pgSQL function "testtemp" line 9 at select into variables > > Here is my function: > create or replace function testtemp() > returns boolean as > $body$ > declare > query text; > relid integer; > cnt integer; > begin > create temp table schedrec (sch text, cl text, st text); Use EXECUTE for DDL-Statements within function, because the planner cached the OID's. Other solution: use 8.3. More details about that: http://merlinmoncure.blogspot.com/2007/09/as-previously-stated-postgresql-8.html Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net