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 <[EMAIL PROTECTED]> 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:* [EMAIL PROTECTED] [mailto: > [EMAIL PROTECTED] *On Behalf Of *Kerri Reno > *Sent:* Tuesday, April 22, 2008 7:55 AM > *To:* pgsql-general@postgresql.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* <[EMAIL PROTECTED]> wrote: > > -------------- Original message ---------------------- > From: "Kerri Reno" <[EMAIL PROTECTED]> > > > 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 > [EMAIL PROTECTED] (928) 502-4240 > .·:*¨¨*:·. .·:*¨¨*:·. .·:*¨¨*:·. > -- Yuma Educational Computer Consortium Compass Development Team Kerri Reno [EMAIL PROTECTED] (928) 502-4240 .·:*¨¨*:·. .·:*¨¨*:·. .·:*¨¨*:·.
