Re: FW: Re: create temp in function

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

I’ll ask again in a different way. 

 

What is the purpose of your dynamic sql and/or temp table?  Don’t tell me anything about using select into. 

 

What is the business purpose of the function?  An appropriate answer would be “I’m trying calculate x” or “I’m trying to determine y by looking at x”.

 

It looks like you are trying to use a temp table when an inline view would be more appropriate.

 

 

Jon

 

---

From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Kerri Reno
Sent: Tuesday, April 22, 2008 8:27 AM
To: Roberts, Jon
Cc: pgsql-general@xxxxxxxxxxxxxx
Subject: Re: FW: Re: [GENERAL] create temp in function

 

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.

On 4/22/08, Roberts, Jon <Jon.Roberts@xxxxxxxxxxx> 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@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
.·:*¨¨*:·.   .·:*¨¨*:·.   .·:*¨¨*:·.

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