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
<[EMAIL PROTECTED]>
Tel: 402.212.0196
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
