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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
