Forgot to hit reply all.
------------- Forwarded Message: --------------
From: aklaver@xxxxxxxxxxx (Adrian Klaver)
To: "Kerri Reno" <kreno@xxxxxxxxxx>
Subject: Re: [GENERAL] create temp in function
Date: Mon, 21 Apr 2008 23:01:53 +0000
> -------------- Original message ----------------------
> From: "Kerri Reno" <kreno@xxxxxxxxxx>
> > 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?
>
If you are running a version <8.3 you will need to use EXECUTE. See:
http://www.postgresql.org/docs/8.2/interactive/plpgsql-statements.html#PLPGSQL-S TATEMENTS-EXECUTING-DYN
>
> > TIA,
> > Kerri
> >
> >
>
--
Adrian Klaver
aklaver@xxxxxxxxxxx
-
--- Begin Message ---
--- Begin Message ---
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@xxxxxxxxxx (928) 502-4240
.·:*¨¨*:·. .·:*¨¨*:·. .·:*¨¨*:·.
--- End Message ---
--- End Message ---
