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

Hello

On 22/04/2008, Kerri Reno <kreno@yumaed.org> wrote:
> Thanks to all who responded.  I now know why execute will help this problem,
> but then it causes a new problem.  The example I sent you was trivial,
> trying to get to the bottom of the issue.  What I'm really trying to is get
> past the restriction of execute to do SELECT INTO.  That's why I created a
> temporary table, so that that command could be dynamic, and then do a SELECT
> INTO from that table.  Because of the planning issue that won't work.  I
> can't upgrade to 8.3 at this time (long story).  Any ideas how to do this?
> Here is a section of my code.
>

you don't need upgrade to 8.3. Just use dynamic statements. Like:

BEGIN
  EXECUTE 'CREATE TEMP TABLE a ...';
  a)
    EXECUTE 'SELECT * FROM a WHERE ...' INTO somevars;
  b)
    FOR vars IN EXECUTE 'SELECT * FROM .. ' LOOP

Regards
Pavel Stehule

> begin
>     query = 'create temp table schedrec as select ' ||
>         'salary_schedule, pay_column, step from ' || tbl ||
>         ' where cp_id = ' || to_char(tcp_id,'99999999');
>      raise notice 'query: %', query;
>     execute query;
>     select into relid distinct(attrelid) from pg_attribute where
>         attrelid='schedrec'::regclass;
>     raise notice 'relid: %', relid;
>      raise notice 'about to do select';
>     select into arow * from schedrec limit 1;
>     drop table schedrec;
>     return arow;
> end;
>
> Thanks so much!
> Kerri
>
> On 4/21/08, Adrian Klaver <aklaver@comcast.net> wrote:
> >
> >  -------------- 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 the statements where cached for
> use by later runs of the function in the same session. The error you are
> getting about OID missing means the function is looking for the OID of the
> temp table as it was cached in the first run and not finding it. To get
> around this you need to EXECUTE the create temp table statement. This causes
> the plan not be cached but run anew for each call of the function. If you
> follow the link I included in the previous email you will see some examples.
> >
> >
>
>
> --
> Yuma Educational Computer Consortium
> Compass Development Team
> Kerri Reno
> kreno@yumaed.org      (928) 502-4240
>  .·:*¨¨*:·.   .·:*¨¨*:·.   .·:*¨¨*:·.