Re: ERROR: column "currec" does not exist while calling function with 'record' (or %rowtype% argument

The example does not make a lot of sense; it has been created for
demonstration purposes only:
-------------------------------------------------------------
-- One Table:
-- Table: public.denorm

-- DROP TABLE public.denorm;

CREATE TABLE public.denorm
(
thekey int8 NOT NULL DEFAULT nextval('public."denorm_theKey_seq"'::text),
cat1 int8 NOT NULL,
cat2 int8 NOT NULL,
cat3 int8 NOT NULL,
thedata varchar(128),
CONSTRAINT pk_denorm PRIMARY KEY (thekey)
) WITHOUT OIDS;
GRANT ALL ON TABLE public.denorm TO god WITH GRANT OPTION;
GRANT ALL ON TABLE public.denorm TO public;
----------------------------------------------------------

----------------------------------------------------------
-- fill it:
insert into denorm (theKey, cat1, cat2, cat3, theData) values
(1,10,11,0,'one');
insert into denorm (theKey, cat1, cat2, cat3, theData) values
(10,100,101,102,'10');
insert into denorm (theKey, cat1, cat2, cat3, theData) values
(11,110,111,112,'11');

insert into denorm (theKey, cat1, cat2, cat3, theData) values
(100,0,0,0,'100');
insert into denorm (theKey, cat1, cat2, cat3, theData) values
(101,0,0,0,'101');
insert into denorm (theKey, cat1, cat2, cat3, theData) values
(102,0,0,0,'102');
insert into denorm (theKey, cat1, cat2, cat3, theData) values
(103,0,0,0,'103');
insert into denorm (theKey, cat1, cat2, cat3, theData) values
(110,0,0,0,'110');
insert into denorm (theKey, cat1, cat2, cat3, theData) values
(111,0,0,0,'111');
insert into denorm (theKey, cat1, cat2, cat3, theData) values
(112,0,0,0,'112');
----------------------------------------------------------

-- called function:
create or replace function norm(int8,record) returns int8 as '
declare
prim alias for $1;
catrec alias for $2;
currrec record;
begin
raise warning \'Key=%; data=[%];\',catrec.cat1,catrec.theData;
if(catrec.cat1>0) then
select * into currec from denorm where theKey=catrec.cat1;
select norm(catrec.cat1,currec) into dmy;
else
return(0);
end if;

if(catrec.cat2>0) then
select * into currec from denorm where theKey=catrec.cat2;
select norm(catrec.cat2,currec) into dmy;
else
return(1);
end if;

if(catrec.cat3>0) then
select * into currec from denorm where theKey=catrec.cat3;
select norm(catrec.cat1,currec) into dmy;
else
return(2);
end if;
return 3;
end
' language 'plpgsql'
-----------------------------------------------------------------
-- calling function
create or replace function doTree() returns int8 as '
declare
currec record;
catrec record;
dmy int8;
rdct int8;
begin
rdct:=0;
for catrec in select * from denorm loop
rdct:=rdct+1;
raise warning \'Start Key=%;
data=[%];\',catrec.cat1,catrec.theData;
loop
if(catrec.cat1>0) then
select * into currec from denorm where
theKey=catrec.cat1;
raise warning \'before call:%;\',
currec.cat1;
select norm(catrec.cat1,currec) into dmy;
raise warning \'after call:%;\',
currec.cat1;
else
exit;
end if;

if(catrec.cat2>0) then
select * into currec from denorm where
theKey=catrec.cat2;
select norm(catrec.cat2,currec) into dmy;
else
exit;
end if;

if(catrec.cat3>0) then
select * into currec from denorm where
theKey=catrec.cat3;
select norm(catrec.cat1,currec) into dmy;
end if;
exit;
end loop;
end loop;
return rdct;
end;
' language 'plpgsql'
----------------------------------------------------------

--CALLING:
select doTree();

RESULT:
WARNING: Start Key=10; data=[one];
WARNING: before call:100;

ERROR: column "currec" does not exist
CONTEXT: PL/pgSQL function "dotree" line 15 at select into variables

==============================================================

I call a function with one data element and a record; I receive the error
message that the record is a column which is a fraud since the system shows:
CREATE OR REPLACE FUNCTION public.norm(int8, record)
RETURNS int8 AS ...

When I try to use denorm%ROWTYPE% I get already a syntax error in the
declaration of the function:
create or replace function norm(int8,denorm%ROWTYPE%) returns int8 as ' :

ERROR: syntax error at or near "%" at character 44

Where am I going wrong; what has to change to hand over a record to a called
function??

with kind regards

günter strubinsky
<strubinsky(at)acm(dot)org>
Tel: 402.212.0196

=?iso-8859-1?Q?g=FCnter_strubinsky?= <strubinsky(at)acm(dot)org> writes:

> currrec record;
^^^
> select * into currec from denorm where theKey=catrec.cat1;
^^

> Where am I going wrong;

Count the "r"s ...

regards, tom lane

Sorry, wrong copy!

----------------------------------------------------------------------------
-- Function: public.norm(int8, record)

-- DROP FUNCTION public.norm(int8, record);

CREATE OR REPLACE FUNCTION public.norm(int8, record)
RETURNS int8 AS
'
declare
prim alias for $1;
catrec alias for $2;
currec record;
begin
raise warning \'Key=%; data=[%];\',catrec.cat1,catrec.theData;
if(catrec.cat1>0) then
select * into currec from denorm where theKey=catrec.cat1;
select norm(catrec.cat1,currec) into dmy;
else
return(0);
end if;

if(catrec.cat2>0) then
select * into currec from denorm where theKey=catrec.cat2;
select norm(catrec.cat2,currec) into dmy;
else
return(1);
end if;

if(catrec.cat3>0) then
select * into currec from denorm where theKey=catrec.cat3;
select norm(catrec.cat1,currec) into dmy;
else
return(2);
end if;
return 3;
end
'
LANGUAGE 'plpgsql' VOLATILE;
----------------------------------------------
-- Function: public.dotree()

-- DROP FUNCTION public.dotree();

CREATE OR REPLACE FUNCTION public.dotree()
RETURNS int8 AS
'
declare
currec record;
catrec record;
dmy int8;
rdct int8;
begin
rdct:=0;
for catrec in select * from denorm loop
rdct:=rdct+1;
raise warning \'Start Key=%;
data=[%];\',catrec.cat1,catrec.theData;
loop
if(catrec.cat1>0) then
select * into currec from denorm where
theKey=catrec.cat1;
raise warning \'before call:%;\',
currec.cat1;
select norm(catrec.cat1,currec) into dmy;
raise warning \'after call:%;\',
currec.cat1;
else
exit;
end if;

if(catrec.cat2>0) then
select * into currec from denorm where
theKey=catrec.cat2;
select norm(catrec.cat2,currec) into dmy;
else
exit;
end if;

if(catrec.cat3>0) then
select * into currec from denorm where
theKey=catrec.cat3;
select norm(catrec.cat3,currec) into dmy;
end if;
exit;
end loop;
end loop;
return rdct;
end;
'
LANGUAGE 'plpgsql' VOLATILE;
==============================================================
select doTree();
--------------------------------------------------------------
RESULT:
WARNING: Start Key=10; data=[one];
WARNING: before call:100;

ERROR: column "currec" does not exist
CONTEXT: PL/pgSQL function "dotree" line 15 at select into variables

with kind regards

günter strubinsky
<strubinsky(at)acm(dot)org>
Tel: 402.212.0196

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Tom Lane
Sent: Wednesday, 24 March, 2004 15:39
To: günter strubinsky
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] ERROR: column "currec" does not exist while calling
function with 'record' (or %rowtype% argument

=?iso-8859-1?Q?g=FCnter_strubinsky?= <strubinsky(at)acm(dot)org> writes:

> currrec record;
^^^
> select * into currec from denorm where theKey=catrec.cat1;
^^

> Where am I going wrong;

Count the "r"s ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

=?iso-8859-1?Q?g=FCnter_strubinsky?= <strubinsky(at)acm(dot)org> writes:
> Sorry, wrong copy!

Okay, looking more closely, you've got two problems here:

> CREATE OR REPLACE FUNCTION public.norm(int8, record)
> RETURNS int8 AS
> ...
> LANGUAGE 'plpgsql' VOLATILE;

plpgsql functions don't support inputs declared as type "record"; you
have to use a more specific type name. norm() would have failed at
runtime had you ever gotten that far.

> declare
> currec record;
> ...
> select norm(catrec.cat1,currec) into dmy;

plpgsql also doesn't support passing whole record variables into SQL
commands. The error message about this is unhelpful :-(. (I just
improved the message in CVS tip, but actually making it work seems like
a much larger project...)

In this particular case I think you can end-run around it by not
importing the row into plpgsql at all. Can't you combine

select * into currec from denorm where theKey=catrec.cat1;
select norm(catrec.cat1,currec) into dmy;

into

select norm(catrec.cat1,denorm.*) into dmy
from denorm where theKey=catrec.cat1;

This should work if norm()'s second argument is declared as type
"denorm" (equivalent to denorm%rowtype).

If you don't want to tie norm() to the denorm row type, you'll need to
pass in the values it needs as separate scalars.

regards, tom lane

Hmm, yes, that worked in my example.

I have however a master-detail query (in a denormalized tree). I am applying
some calculations to each detail.

I run through the master via
for master in mastertable loop
select * into denorm from denorm where
master.detailkey=denorm.cat_id
Inner loop
if(denorm.cat1>0)
select detailproc(denorm.cat1,master);
^^^^^^
else
exit;
end if;

if(denorm.cat2>0)
select detailproc(denorm.cat2,master);
^^^^^^
else
exit;
end if;

if(denorm.cat3>0)
select detailproc(denorm.cat3,master);
^^^^^^
else
exit;
end if;
end inner loop;
end master loop;

I want to avoid (redundantly) to reread the master three times (since it
hasn't changed). This however seems to be the only solution, or to place 2
dozens of variables (the master row) into each call.

Your advice, Tom, is perfect (and works wonderfully) on the example.

Sadly my example was incomplete:
a) Can I somehow make the local variables of my detail function static so
that the data survives prior calls and if that can be done,

b) can two functions with the same name but different number of parameters
share that local/static vars?

IMMUTABLE ------+
STABLE += does anythese have my
VOLATILE ------+ requirement as a side effect?

If b is not possible, no biggie, because I would then call with (cat_id,
master.*) the first time and (cat_id, null) for the details.

My solution would be

Select detailproc(master.*)...--fills the local copy with master elements)

-- use local copy of master.*
If(denorm.cat1>0) then
Select detailproc(denorm.cat1::int8);
else exit 1; end if

If(denorm.cat2>0) then
-- use local copy of master.*
Select detailproc(denorm.cat2::int8);
else exit 1; end

if If(denorm.cat3>0) then
-- use local copy of master.*
Select detailproc(denorm.cat3::int8);
else exit 1; end if

with kind regards

günter strubinsky
<strubinsky(at)acm(dot)org>
Tel: 402.212.0196

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Tom Lane
Sent: Wednesday, 24 March, 2004 17:51
To: günter strubinsky
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] ERROR: column "currec" does not exist while calling
function with 'record' (or %rowtype% argument

=?iso-8859-1?Q?g=FCnter_strubinsky?= <strubinsky(at)acm(dot)org> writes:
> Sorry, wrong copy!

Okay, looking more closely, you've got two problems here:

> CREATE OR REPLACE FUNCTION public.norm(int8, record)
> RETURNS int8 AS
> ...
> LANGUAGE 'plpgsql' VOLATILE;

plpgsql functions don't support inputs declared as type "record"; you
have to use a more specific type name. norm() would have failed at
runtime had you ever gotten that far.

> declare
> currec record;
> ...
> select norm(catrec.cat1,currec) into dmy;

plpgsql also doesn't support passing whole record variables into SQL
commands. The error message about this is unhelpful :-(. (I just
improved the message in CVS tip, but actually making it work seems like
a much larger project...)

In this particular case I think you can end-run around it by not
importing the row into plpgsql at all. Can't you combine

select * into currec from denorm where theKey=catrec.cat1;
select norm(catrec.cat1,currec) into dmy;

into

select norm(catrec.cat1,denorm.*) into dmy
from denorm where theKey=catrec.cat1;

This should work if norm()'s second argument is declared as type
"denorm" (equivalent to denorm%rowtype).

If you don't want to tie norm() to the denorm row type, you'll need to
pass in the values it needs as separate scalars.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match