pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedplpgsql function not accepting NULL value
4+ messages / 3 participants
[nested] [flat]
* plpgsql function not accepting NULL value
@ 2005-09-16 23:04 Kenneth Dombrowski <kdombrowski@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Kenneth Dombrowski @ 2005-09-16 23:04 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: plpgsql function not accepting NULL value
@ 2005-09-16 23:17 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Kenneth Dombrowski <kdombrowski@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Stephan Szabo @ 2005-09-16 23:17 UTC (permalink / raw)
To: Kenneth Dombrowski <kdombrowski@gmail.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: plpgsql function not accepting NULL value
@ 2005-09-16 23:18 Michael Fuhr <mike@fuhr.org>
parent: Kenneth Dombrowski <kdombrowski@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Michael Fuhr @ 2005-09-16 23:18 UTC (permalink / raw)
To: Kenneth Dombrowski <kdombrowski@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: plpgsql function not accepting NULL value
@ 2005-09-16 23:27 Kenneth Dombrowski <kdombrowski@gmail.com>
parent: Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 4+ messages in thread
From: Kenneth Dombrowski @ 2005-09-16 23:27 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-09-16 23:27 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-09-16 23:04 plpgsql function not accepting NULL value Kenneth Dombrowski <kdombrowski@gmail.com>
2005-09-16 23:17 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2005-09-16 23:18 ` Michael Fuhr <mike@fuhr.org>
2005-09-16 23:27 ` Kenneth Dombrowski <kdombrowski@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox