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

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@acm.org>
 Tel: 402.212.0196


-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Wednesday, 24 March, 2004 15:39
To: günter strubinsky
Cc: pgsql-general@postgresql.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@acm.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