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

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@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 17:51
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:
> 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