PL/pgSQL how to pass null values to the functions?

From: "Kuvyrkin, Nick" <Nick(at)fns(dot)ru>
To: "'pgsql-hackers(at)postgresql(dot)org'" <pgsql-hackers(at)postgresql(dot)org>
Subject: PL/pgSQL how to pass null values to the functions?
Date: 2000-08-18 12:01:58
Message-ID: B938684AB1B7D31191E20050BA012C67374F@mps.intranet
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I have plpgsql function that updates the table and may have (and also may
not have) null parameters:

create function f_test(int4, int4) returns int4 as
'
declare
v1 alias for $1;
v2 alias for $2;
v3 int4;
begin
raise notice ''v1 = %'', v1;
update _testtable set a = v1 where b = v2;
select into v3 a from _testtable where b = v2;
raise notice ''v3 = %'', v3;
return v3;
end;
' language 'plpgsql';

Column a in _testable can have null value. When I do ‘select f_test(1,1);’
then everything is working fine. The row is updated (v1 = v3 = 1).
But when I do ‘select f_test(null,1);’ nothing happens :-(. No errors
reported, but ‘select a from _testable where b=1;’ returns 1 (v1 = v3 =
<null>).
Where am I wrong?

CONFIDENTIALITY NOTICE
This email and any files transmitted with it are confidential and are
intended solely for the use of the individual or entity to whom they are
addressed. This communication represents the originator's personal views and
opinions, which do not necessarily reflect those of FastNet Solutions
Company. If you are not the original recipient or the person responsible for
delivering the email to the intended recipient, be advised that you have
received this email in error, and that any use, dissemination, forwarding,
printing, or copying of this email is strictly prohibited. If you received
this email in error, please immediately notify postmaster(at)fns(dot)ru(dot)

Browse pgsql-hackers by date

  From Date Subject
Next Message Sevo Stille 2000-08-18 13:25:12 Re: VACUUM optimization ideas.
Previous Message Kaare Rasmussen 2000-08-18 08:16:07 ETA for 7.1 ?