Re: FW: Re: create temp in function - Mailing list pgsql-general

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.Roberts@asurion.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@postgresql.org [mailto:
> > pgsql-general-owner@postgresql.org] *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* <aklaver@comcast.net> wrote:
> >
> >  -------------- Original message ----------------------
> > From: "Kerri Reno" <kreno@yumaed.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@yumaed.org      (928) 502-4240
> > .·:*¨¨*:·.   .·:*¨¨*:·.   .·:*¨¨*:·.

--
Adrian Klaver
aklaver@comcast.net