plpgsql function not accepting NULL value - Mailing list pgsql-sql

From Kenneth Dombrowski
Subject plpgsql function not accepting NULL value
Date
Msg-id d7ba27305091616046e23e171@mail.gmail.com
Whole thread Raw
Responses Re: plpgsql function not accepting NULL value
Re: plpgsql function not accepting NULL value
List pgsql-sql
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


pgsql-sql by date:

Previous
From: jeff sacksteder
Date:
Subject: alter sequence + subqueries
Next
From: Stephan Szabo
Date:
Subject: Re: plpgsql function not accepting NULL value