From: | "Kerri Reno" <kreno(at)yumaed(dot)org> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Fwd: create temp in function |
Date: | 2008-04-21 21:24:14 |
Message-ID: | a5b8c7860804211424k493e930ewa764cb306c8373e3@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
BTW, this happens the same in PostgreSQL 8.0 and 8.2.
Thanks!
Kerri
---------- Forwarded message ----------
From: Kerri Reno <kreno(at)yumaed(dot)org>
Date: Apr 21, 2008 3:22 PM
Subject: create temp in function
To: pgsql-general(at)postgresql(dot)org
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:
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;
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
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(at)yumaed(dot)org (928) 502-4240
.·:*¨¨*:·. .·:*¨¨*:·. .·:*¨¨*:·.
--
Yuma Educational Computer Consortium
Compass Development Team
Kerri Reno
kreno(at)yumaed(dot)org (928) 502-4240
.·:*¨¨*:·. .·:*¨¨*:·. .·:*¨¨*:·.
From | Date | Subject | |
---|---|---|---|
Next Message | Dirk Verleysen | 2008-04-21 21:31:55 | PostgreSQL on Vista |
Previous Message | Kerri Reno | 2008-04-21 21:22:52 | create temp in function |