Re: 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