Received: from tolikus.hq.aaanet.ru (tolikus.hq.aaanet.ru [213.59.98.206]) by hub.org (8.10.1/8.10.1) with ESMTP id e7NDcAT07336 for ; Wed, 23 Aug 2000 09:38:10 -0400 (EDT) Received: (from tolik@localhost) by tolikus.hq.aaanet.ru (8.9.3/8.9.3) id RAA06699; Wed, 23 Aug 2000 17:37:36 +0400 (MSD) (envelope-from tolik) To: "Graham Vickrage" Cc: "Tom Lane" , "postgresql" Subject: Re: Null function parameters References: Mime-Version: 1.0 (generated by tm-edit 1.5) Content-Type: text/plain; charset=US-ASCII From: tolik@aaanet.ru (Anatoly K. Lasareff) Date: 23 Aug 2000 17:37:36 +0400 In-Reply-To: "Graham Vickrage"'s message of "Wed, 23 Aug 2000 13:07:31 +0100" Message-ID: <86em3gumhr.fsf@tolikus.hq.aaanet.ru> Lines: 92 X-Mailer: Gnus v5.6.45/XEmacs 21.1 - "Canyonlands" X-Archive-Number: 200008/241 >>>>> "GV" == Graham Vickrage writes: GV> Thanks Anatoly GV> So if I understand you correctly you can't pass more than one NULL int into GV> a function? I'afraid no. My question is: if I pass one null argument into function then all other argumens, which are not null, became null inside function body. GV> Therefore Newbe DBA type question: - GV> Is this a shortcoming in postgres or is it to be expected when dealing with GV> transactions? GV> If it is a shotcoming are there any plans to include it in future releases? GV> Regards GV> Graham GV> -----Original Message----- GV> From: tolik@tolikus.hq.aaanet.ru [mailto:tolik@tolikus.hq.aaanet.ru]On GV> Behalf Of Anatoly K. Lasareff GV> Sent: 23 August 2000 12:46 GV> To: Tom Lane GV> Cc: Graham Vickrage; postgresql GV> Subject: Re: [SQL] Null function parameters >>>>> "TL" == Tom Lane writes: TL> "Graham Vickrage" writes: >>> However my understanding was that if the default value is SQL NULL then GV> any >>> values passed into the function that are null would be treated as GV> 'NULL'. TL> Not sure what you think you meant by that, but a null is a null. If GV> you TL> declared the table column as NOT NULL then Postgres is doing exactly TL> what it should. You may wish to code the insert along the lines of TL> INSERT INTO table VALUES (..., COALESCE($1, suitable-default), ...) TL> COALESCE is a handy notation for "value1 unless it's NULL, in which GV> case TL> value2". TL> regards, tom lane GV> But unfortunately we have no answer for primary question: GV> | Why if we pass to function ONLY ONE null agument all the oters | GV> | argumenta in function's boby are null too? | GV> | Or: is it possible to pass null arguments into plpgsql functions? | GV> Example. GV> create function a(int, int) returns int as ' GV> begin GV> raise notice ''1: % 2: %'', $1, $2; GV> if $1 is null then GV> return $2; GV> end if; GV> return $1; GV> end; GV> ' language 'plpgsql'; GV> tolik=# select a(1,2); GV> NOTICE: 1: 1 2: 2 GV> a GV> --- GV> 1 GV> (1 row) GV> tolik=# select a(null,2); GV> NOTICE: 1: 2: GV> a GV> --- GV> (1 row) -- Anatoly K. Lasareff Email: tolik@aaanet.ru