Re: FW: Re: create temp in function

Hello

On 22/04/2008, Kerri Reno <kreno(at)yumaed(dot)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(at)comcast(dot)net> wrote:
> >
> > -------------- Original message ----------------------
> > From: "Kerri Reno" <kreno(at)yumaed(dot)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(at)yumaed(dot)org (928) 502-4240
> .·:*¨¨*:·. .·:*¨¨*:·. .·:*¨¨*:·.