Re: FW: Re: create temp in function

http://www.postgresql.org/docs/8.2/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
says
SELECT INTO is not currently supported within EXECUTE.

I was using a temp table to get around the above problem.

On 4/22/08, Roberts, Jon <Jon(dot)Roberts(at)asurion(dot)com> wrote:
>
> Can you explain what you mean by the "restriction to do SELECT INTO"?
>
>
>
> Why are you using a temp table to begin with?
>
>
>
>
>
>
>
> Jon
>
>
> ------------------------------
>
> *From:* pgsql-general-owner(at)postgresql(dot)org [mailto:
> pgsql-general-owner(at)postgresql(dot)org] *On Behalf Of *Kerri Reno
> *Sent:* Tuesday, April 22, 2008 7:55 AM
> *To:* pgsql-general(at)postgresql(dot)org
> *Subject:* Re: FW: Re: [GENERAL] create temp in function
>
>
>
> 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.
>
> 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
> .·:*¨¨*:·. .·:*¨¨*:·. .·:*¨¨*:·.
>

--
Yuma Educational Computer Consortium
Compass Development Team
Kerri Reno
kreno(at)yumaed(dot)org (928) 502-4240
.·:*¨¨*:·. .·:*¨¨*:·. .·:*¨¨*:·.