FW: Re: create temp in function

Forgot to hit reply all.

------------- Forwarded Message: --------------
From: aklaver@comcast.net (Adrian Klaver)
To: "Kerri Reno" <kreno@yumaed.org>
Subject: Re: [GENERAL] create temp in function
Date: Mon, 21 Apr 2008 23:01:53 +0000
> -------------- Original message ----------------------
> From: "Kerri Reno" <kreno@yumaed.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?
>

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@comcast.net

-