Re: FW: Re: create temp in function - Mailing list pgsql-general

-------------- Original message ----------------------
From: "Kerri Reno" <kreno@yumaed.org>
> 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?

plpgsql caches query plans. In versions prior to 8.3 this meant that the first time you ran a function the plans for
thestatements where cached for use by later runs of the function in the same session. The error you are getting about
OIDmissing means the function is looking for the OID of the temp table as it was cached in the first run and not
findingit. To get around this you need to EXECUTE the create temp table statement. This causes the plan not be cached
butrun anew for each call of the function. If you follow the link I included in the previous email you will see some
examples.

>
> Thanks!
> Kerri
>
> On 4/21/08, Adrian Klaver <aklaver@comcast.net> wrote:
> >
> > 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