Thread: FW: Re: create temp in function

Forgot to hit reply all.

------------- Forwarded Message: --------------
From: aklaver@comcast.net (Adrian Klaver)
To: "Kerri Reno" <kreno@yumaed.org>
Subject: Re: [GENERAL] create temp in function
Date: Mon, 21 Apr 2008 23:01:53 +0000
> -------------- Original message ----------------------
> From: "Kerri Reno" <kreno@yumaed.org>
> > Hi All!  I'm new to this list, but I've been using PG for a couple of years
> > now.  I'm trying to do something in a function that I just can't seem to do.
> >
> >
> > If I do the following in psql or pgadmin:
> > create temp table schedrec (sch text, cl text, st text);
> > select distinct(attrelid) from pg_attribute where attrelid =
> > 'schedrec'::regclass;
> > select * from schedrec;
> > drop table schedrec;
> >
> > I can do it over and over and over again without problem;
> >
> > But if I create and run the following function, it bombs on the second run.
> > It gives me:
> > compassdevel_lb=# select testtemp();
> > NOTICE:  relid: 186270497
> > NOTICE:  count: 0
> >  testtemp
> > ----------
> >  t
> > (1 row)
> >
> > compassdevel_lb=# select testtemp();
> > NOTICE:  relid: <NULL>
> > ERROR:  relation with OID 186270497 does not exist
> > CONTEXT:  SQL statement "SELECT  count(*) from schedrec"
> > PL/pgSQL function "testtemp" line 9 at select into variables
> >
> > Here is my function:
> > create or replace function testtemp()
> >     returns boolean as
> > $body$
> > declare
> >     query text;
> >     relid integer;
> >     cnt integer;
> > begin
> >     create temp table schedrec (sch text, cl text, st text);
> >     select into relid distinct(attrelid) from pg_attribute where attrelid =
> > 'schedrec'::regclass;
> >     raise notice 'relid: %', relid;
> >     select into cnt count(*) from schedrec;
> >     raise notice 'count: %', cnt;
> >     drop table schedrec;
> >     if relid is null then
> >         return false;
> >     else
> >         return true;
> >     end if;
> > end;
> > $body$
> > language plpgsql security definer;
> >
> > Can anyone please help me with this?
>

If you are running a version <8.3 you will need to use EXECUTE. See:
 http://www.postgresql.org/docs/8.2/interactive/plpgsql-statements.html#PLPGSQL-S TATEMENTS-EXECUTING-DYN

>
> > TIA,
> > Kerri
> >
> >
>


--
Adrian Klaver
aklaver@comcast.net

-

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?

Thanks!
Kerri

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

Kerri Reno wrote:
> Adrian,
>
> I don't understand.  Why do I need to use execute?

It's a Postgres shortcoming.  It's fixed in 8.3 so if it annoys you too
much you can just upgrade.

> 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?

All sentences referring to the temp table.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

-------------- 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
thestatements where cached for use by later runs of the function in the same session. The error you are getting about
OIDmissing means the function is looking for the OID of the temp table as it was cached in the first run and not
findingit. To get around this you need to EXECUTE the create temp table statement. This causes the plan not be cached
butrun anew for each call of the function. If you follow the link I included in the previous email you will see some
examples.

>
> Thanks!
> Kerri
>
> On 4/21/08, Adrian Klaver <aklaver@comcast.net> wrote:
> >
> > Forgot to hit reply all.
> >
> > ------------- Forwarded Message: --------------
> > From: aklaver@comcast.net (Adrian Klaver)
> > To: "Kerri Reno" <kreno@yumaed.org>
> > Subject: Re: [GENERAL] create temp in function
> > Date: Mon, 21 Apr 2008 23:01:53 +0000
> > > -------------- Original message ----------------------
> > > From: "Kerri Reno" <kreno@yumaed.org>
> > > > Hi All!  I'm new to this list, but I've been using PG for a couple of
> > years
> > > > now.  I'm trying to do something in a function that I just can't seem
> > to do.
> > > >
> > > >
> > > > If I do the following in psql or pgadmin:
> > > > create temp table schedrec (sch text, cl text, st text);
> > > > select distinct(attrelid) from pg_attribute where attrelid =
> > > > 'schedrec'::regclass;
> > > > select * from schedrec;
> > > > drop table schedrec;
> > > >
> > > > I can do it over and over and over again without problem;
> > > >
> > > > But if I create and run the following function, it bombs on the second
> > run.
> > > > It gives me:
> > > > compassdevel_lb=# select testtemp();
> > > > NOTICE:  relid: 186270497
> > > > NOTICE:  count: 0
> > > >  testtemp
> > > > ----------
> > > >  t
> > > > (1 row)
> > > >
> > > > compassdevel_lb=# select testtemp();
> > > > NOTICE:  relid: <NULL>
> > > > ERROR:  relation with OID 186270497 does not exist
> > > > CONTEXT:  SQL statement "SELECT  count(*) from schedrec"
> > > > PL/pgSQL function "testtemp" line 9 at select into variables
> > > >
> > > > Here is my function:
> > > > create or replace function testtemp()
> > > >     returns boolean as
> > > > $body$
> > > > declare
> > > >     query text;
> > > >     relid integer;
> > > >     cnt integer;
> > > > begin
> > > >     create temp table schedrec (sch text, cl text, st text);
> > > >     select into relid distinct(attrelid) from pg_attribute where
> > attrelid =
> > > > 'schedrec'::regclass;
> > > >     raise notice 'relid: %', relid;
> > > >     select into cnt count(*) from schedrec;
> > > >     raise notice 'count: %', cnt;
> > > >     drop table schedrec;
> > > >     if relid is null then
> > > >         return false;
> > > >     else
> > > >         return true;
> > > >     end if;
> > > > end;
> > > > $body$
> > > > language plpgsql security definer;
> > > >
> > > > Can anyone please help me with this?
> > >
> >
> > If you are running a version <8.3 you will need to use EXECUTE. See:
> >
> >
> http://www.postgresql.org/docs/8.2/interactive/plpgsql-statements.html#PLPGSQL-S
> TATEMENTS-EXECUTING-DYN
> >
> > >
> > > > TIA,
> > > > Kerri




--
Adrian Klaver
aklaver@comcast.net

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



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

Hello

On 22/04/2008, Kerri Reno <kreno@yumaed.org> wrote:
> 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.
>

you don't need upgrade to 8.3. Just use dynamic statements. Like:

BEGIN
  EXECUTE 'CREATE TEMP TABLE a ...';
  a)
    EXECUTE 'SELECT * FROM a WHERE ...' INTO somevars;
  b)
    FOR vars IN EXECUTE 'SELECT * FROM .. ' LOOP

Regards
Pavel Stehule

> 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
>  .·:*¨¨*:·.   .·:*¨¨*:·.   .·:*¨¨*:·.

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.




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

I tried that, too, and got a different error.
In the PG log file, it says:
syntax error at or near "$2"
SELECT $1 into $2, $3, $4

My code is below

CREATE OR REPLACE FUNCTION private.get_salary_schedule(integer, text)
  RETURNS record AS
$BODY$
declare
    tcp_id alias for $1;
    tfy alias for $2;
    tbl text := 'saltab' || tfy;
    arow record;
    sched text;
    cl text;
    st integer;
    query text;
begin
    query = 'select ' || 'salary_schedule, pay_column, step from ' || tbl ||
        ' where cp_id = ' || to_char(tcp_id,'99999999');
    raise notice 'query: %', query;
    execute query into sched, cl, st;
    arow := (sched, cl, st);
    return arow;
end;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;

I aslo tried:
    execute query into (sched, cl, st);
which gave me  'syntax error on ('

and
    execute query into arow;
which gave me 'record "arow" is not assigned yet'

Thanks!
Kerri

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

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

So the reason I'm getting the error is that I'm running it in 8.0.  Thanks so much for your help!
Kerri




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

Kerri Reno wrote:
> So the reason I'm getting the error is that I'm running it in 8.0.
> Thanks so much for your help!
> Kerri
use FOR instead

CREATE OR REPLACE FUNCTION private.get_salary_schedule(integer, text)
  RETURNS record AS
$BODY$
declare
    tcp_id alias for $1;
    tfy alias for $2;
    tbl text := 'saltab' || tfy;
    arow record;
    query text;
begin
    query = 'select ' || 'salary_schedule, pay_column, step from ' || tbl ||
        ' where cp_id = ' || to_char(tcp_id,'99999999');
    raise notice 'query: %', query;
    for arow in execute query loop
        return arow;
    end loop;
    return null;
end;
$BODY$
  LANGUAGE 'plpgsql' STABLE SECURITY DEFINER;

you probably need to protect tfy from sql injection too.  see quote_ident.

klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au

Thanks for all who helped me with this.  I just upgraded our one remaining database to 8.2 and EXECUTE INTO worked great.

THANKS!
Kerri




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