Re: FW: Re: create temp in function

["Roberts, Jon" <Jon.Roberts@xxxxxxxxxxx>]

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@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Kerri Reno
Sent: Tuesday, April 22, 2008 7:55 AM
To: pgsql-general@xxxxxxxxxxxxxx
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@xxxxxxxxxxx> wrote:

 -------------- Original message ----------------------
From: "Kerri Reno" <kreno@xxxxxxxxxx>

> 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@xxxxxxxxxx      (928) 502-4240
.·:*¨¨*:·.   .·:*¨¨*:·.   .·:*¨¨*:·.