FW: Re: create temp in function

Forgot to hit reply all.

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

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(at)comcast(dot)net

-