So the reason I'm getting the error is that I'm running it in 8.0. Thanks so much for your help! Kerri
On 4/22/08, Adrian Klaver <[EMAIL PROTECTED]> wrote: > > On Tuesday 22 April 2008 6:26 am, Kerri Reno wrote: > > > http://www.postgresql.org/docs/8.2/static/plpgsql-statements.html#PLPGSQL-S > >TATEMENTS-EXECUTING-DYN says > > SELECT INTO is not currently supported within EXECUTE. > > > In 8.2 EXECUTE INTO is supported.; > > The INTO clause specifies where the results of a SQL command returning > rows > should be assigned. If a row or variable list is provided, it must exactly > match the structure of the query's results (when a record variable is > used, > it will configure itself to match the result structure automatically). If > multiple rows are returned, only the first will be assigned to the INTO > variable. If no rows are returned, NULL is assigned to the INTO variable. > If > no INTO clause is specified, the query results are discarded. > > > > > > > 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 > > > .·:*¨¨*:·. .·:*¨¨*:·. .·:*¨¨*:·. > > -- > Adrian Klaver > [EMAIL PROTECTED] > -- Yuma Educational Computer Consortium Compass Development Team Kerri Reno [EMAIL PROTECTED] (928) 502-4240 .·:*¨¨*:·. .·:*¨¨*:·. .·:*¨¨*:·.