FW: Re: create temp in function

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

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-STATEMENTS-EXECUTING-DYN

TIA,
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 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.

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

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

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

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

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

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

I tried that, too, and got a different error.
NOTICE: query: select salary_schedule, pay_column, step from saltab07 where
cp_id = 32

ERROR: syntax error at or near "$2"
SQL state: 42601
Context: PL/pgSQL function "get_salary_schedule" line 15 at execute
statement

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
On 4/22/08, Pavel Stehule <pavel.stehule@gmail.com> wrote:

Hello

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

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

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@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Kerri Reno
Sent: Tuesday, April 22, 2008 8:27 AM
To: Roberts, Jon
Cc: pgsql-general@postgresql.org
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@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
.·:*¨¨*:·. .·:*¨¨*:·. .·:*¨¨*:·.

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

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@comcast.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.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

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

On 4/22/08, Klint Gore <kgore4@une.edu.au> wrote:

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

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