plpgsql function not accepting NULL value

From: Kenneth Dombrowski <kdombrowski(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: plpgsql function not accepting NULL value
Date: 2005-09-16 23:04:39
Message-ID: d7ba27305091616046e23e171@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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\');
return x_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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Stephan Szabo 2005-09-16 23:17:21 Re: plpgsql function not accepting NULL value
Previous Message jeff sacksteder 2005-09-16 22:36:48 alter sequence + subqueries