Re: FW: Re: create temp in function

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 <aklaver(at)comcast(dot)net> 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 <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
> > > .·:*¨¨*:·. .·:*¨¨*:·. .·:*¨¨*:·.
>
> --
> Adrian Klaver
> aklaver(at)comcast(dot)net
>

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