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

[g�nter strubinsky]

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
 <[EMAIL PROTECTED]>
 Tel: 402.212.0196
 

-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane
Sent: Wednesday, 24 March, 2004 15:39
To: g�nter strubinsky
Cc: [EMAIL PROTECTED]
Subject: Re: [GENERAL] ERROR: column "currec" does not exist while calling
function with 'record' (or %rowtype% argument 

=?iso-8859-1?Q?g=FCnter_strubinsky?= <[EMAIL PROTECTED]> 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


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster