Обсуждение: plpgsql function not accepting NULL value

Hi,

I'm using the postgresql 7.4.7-6sarge1 package from debian stable.

I am confused about these two functions, and their behavior when being
given NULL values in place of a parameter declared as integer:

This one works as expected:

create or replace function insert_rate (integer, integer, integer,
integer, numeric)
returns bigint
as '         declare               x_admin_id              alias for $1;               x_developer_id          alias
for$2;               x_client_id             alias for $3;               x_project_id            alias for $4;
    x_rate                  alias for $5;               x_meta_id       int;               -- and the return id
      x_rate_id bigint;       begin       select into x_meta_id insert_meta (x_admin_id);       insert into rates_tbl (
             developer_id,                client_id,                project_id,                rate,
meta_id      ) values (               x_developer_id,                x_client_id,               x_project_id,
   x_rate,               x_meta_id       );       select into x_rate_id currval(\'rates_tbl_rate_id_seq\');
returnx_rate_id;       end;       ' language plpgsql; 

invoicer=> select insert_rate(1, 1, 1, NULL, '60.00');insert_rate
-------------          7
(1 row)

invoicer=> select * from rates_tbl;rate_id |  rate  | developer_id | client_id | project_id | meta_id
---------+--------+--------------+-----------+------------+---------
...other rows snipped...      7 |  60.00 |            1 |         1 |            |     220
(7 rows)


I can't get this one to work at all:

create or replace function update_rate (integer, integer, integer,       integer, numeric, integer)
returns void
as '         declare               x_admin_id              alias for $1;               x_developer_id          alias
for$2;               x_client_id             alias for $3;               x_project_id            alias for $4;
    x_rate                  alias for $5;               x_rate_id               alias for $6;               x_meta_id
           alias for $7;       begin       perform update_lastmod (x_meta_id, x_admin_id);       update rates_tbl set
            developer_id    = x_developer_id ,                client_id       = x_client_id ,                project_id
    = x_project_id ,                rate            = x_rate         where rate_id = x_rate_id ;        return ;
end;      ' language plpgsql; 


invoicer=> select update_rate(1, 1, 10, NULL, numeric '90.00', 6, 216);
ERROR:  function update_rate(integer, integer, integer, "unknown",
numeric, integer, integer) does not exist
HINT:  No function matches the given name and argument types. You may
need to add explicit type casts.

I tried adding an explicit cast to "integer" on the NULL parameter,
but just got a syntax error @ "NULL"

The only notable difference I see between the functions, is that the
problem function returns void; I tried changing the return value to
'integer' and changing the 'return;' line to 'return 1;', but there
was no change.

Can someone please explain to me what is causing the problem?

Thanks,
Kenneth

On Fri, 16 Sep 2005, Kenneth Dombrowski wrote:

> I can't get this one to work at all:
>
> create or replace function update_rate (integer, integer, integer,
>         integer, numeric, integer)
> returns void
> as '
>         declare
>                 x_admin_id              alias for $1;
>                 x_developer_id          alias for $2;
>                 x_client_id             alias for $3;
>                 x_project_id            alias for $4;
>                 x_rate                  alias for $5;
>                 x_rate_id               alias for $6;
>                 x_meta_id               alias for $7;
>         begin
>         perform update_lastmod (x_meta_id, x_admin_id);
>         update rates_tbl set
>                 developer_id    = x_developer_id ,
>                 client_id       = x_client_id ,
>                 project_id      = x_project_id ,
>                 rate            = x_rate
>         where rate_id = x_rate_id ;
>         return ;
>         end;
>         ' language plpgsql;
>
>
> invoicer=> select update_rate(1, 1, 10, NULL, numeric '90.00', 6, 216);
> ERROR:  function update_rate(integer, integer, integer, "unknown",
> numeric, integer, integer) does not exist
> HINT:  No function matches the given name and argument types. You may
> need to add explicit type casts.

Umm, it looks to me like you're trying to pass 7 parameters to a function
that takes 6 in the above.

On Fri, Sep 16, 2005 at 07:04:39PM -0400, Kenneth Dombrowski wrote:
> create or replace function update_rate (integer, integer, integer,
>         integer, numeric, integer)

You've created a function with six arguments.

> invoicer=> select update_rate(1, 1, 10, NULL, numeric '90.00', 6, 216);
> ERROR:  function update_rate(integer, integer, integer, "unknown",
> numeric, integer, integer) does not exist

You've called a function with seven arguments.  Which is it supposed
to be?

-- 
Michael Fuhr

On 9/16/05, Michael Fuhr <mike@fuhr.org> wrote:
> On Fri, Sep 16, 2005 at 07:04:39PM -0400, Kenneth Dombrowski wrote:
> > create or replace function update_rate (integer, integer, integer,
> >         integer, numeric, integer)
>
> You've created a function with six arguments.
>
> > invoicer=> select update_rate(1, 1, 10, NULL, numeric '90.00', 6, 216);
> > ERROR:  function update_rate(integer, integer, integer, "unknown",
> > numeric, integer, integer) does not exist
>
> You've called a function with seven arguments.  Which is it supposed
> to be?
>

Ahhhhhh... thanks to both of you.  My eyes are tired.