Re: FW: Re: create temp in function

Adrian,

I don't understand. Why do I need to use execute? It runs fine the first
time. The second time it bombs, because it's not seeing schedrec
correctly. Which part should be in an execute query statement?

Thanks!
Kerri

On 4/21/08, Adrian Klaver <aklaver(at)comcast(dot)net> wrote:
>
> 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-STATEMENTS-EXECUTING-DYN
>
> >
> > > TIA,
> > > Kerri
> > >
> > >
> >
>
> --
Yuma Educational Computer Consortium
Compass Development Team
Kerri Reno
kreno(at)yumaed(dot)org (928) 502-4240
.·:*¨¨*:·. .·:*¨¨*:·. .·:*¨¨*:·.