Thread: Null function parameters
Hi All, I am trying to create a function that takes an int as its param and insert the value into a table. The problem occurs when the value passed is NULL, the error message returned is - Execute failed ERROR: ExecAppend: Fail to add null value in not null attribute type However my understanding was that if the default value is SQL NULL then any values passed into the function that are null would be treated as 'NULL'. This doesn't seem to be the case. Chances are I am overlooking something, could any one point me in the right direction? Cheers Graham
"Graham Vickrage" <graham@digitalplanit.com> writes:
> However my understanding was that if the default value is SQL NULL then any
> values passed into the function that are null would be treated as 'NULL'.
Not sure what you think you meant by that, but a null is a null. If you
declared the table column as NOT NULL then Postgres is doing exactly
what it should. You may wish to code the insert along the lines of
INSERT INTO table VALUES (..., COALESCE($1, suitable-default), ...)
COALESCE is a handy notation for "value1 unless it's NULL, in which case
value2".
regards, tom lane
>>>>> "TL" == Tom Lane <tgl@sss.pgh.pa.us> writes: TL> "Graham Vickrage" <graham@digitalplanit.com> writes:>> However my understanding was that if the default value is SQLNULL then any>> values passed into the function that are null would be treated as 'NULL'. TL> Not sure what you think you meant by that, but a null is a null. If youTL> declared the table column as NOT NULL thenPostgres is doing exactlyTL> 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 caseTL> value2". TL> regards, tom lane But unfortunately we have no answer for primary question: | Why if we pass to function ONLY ONE null agument all the oters | | argumenta in function's boby are null too? | | Or: is it possible to pass null arguments into plpgsql functions? | Example. create function a(int, int) returns int as ' begin raise notice ''1: % 2: %'', $1, $2; if $1 is null then return $2; end if; return $1; end; ' language 'plpgsql'; tolik=# select a(1,2); NOTICE: 1: 1 2: 2a ---1 (1 row) tolik=# select a(null,2); NOTICE: 1: <NULL> 2: <NULL>a --- (1 row) -- Anatoly K. Lasareff Email: tolik@aaanet.ru
Thanks Anatoly So if I understand you correctly you can't pass more than one NULL int into a function? Therefore Newbe DBA type question: - Is this a shortcoming in postgres or is it to be expected when dealing with transactions? If it is a shotcoming are there any plans to include it in future releases? Regards Graham -----Original Message----- From: tolik@tolikus.hq.aaanet.ru [mailto:tolik@tolikus.hq.aaanet.ru]On Behalf Of Anatoly K. Lasareff Sent: 23 August 2000 12:46 To: Tom Lane Cc: Graham Vickrage; postgresql Subject: Re: [SQL] Null function parameters >>>>> "TL" == Tom Lane <tgl@sss.pgh.pa.us> writes: TL> "Graham Vickrage" <graham@digitalplanit.com> writes:>> However my understanding was that if the default value is SQLNULL then any>> values passed into the function that are null would be treated as 'NULL'. TL> Not sure what you think you meant by that, but a null is a null. If youTL> declared the table column as NOT NULL then Postgres is doing exactlyTL> what it should. You may wish to code theinsert 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 caseTL> value2". TL> regards, tom lane But unfortunately we have no answer for primary question: | Why if we pass to function ONLY ONE null agument all the oters | | argumenta in function's boby are null too? | | Or: is it possible to pass null arguments into plpgsql functions? | Example. create function a(int, int) returns int as ' begin raise notice ''1: % 2: %'', $1, $2; if $1 is null then return $2; end if; return $1; end; ' language 'plpgsql'; tolik=# select a(1,2); NOTICE: 1: 1 2: 2a ---1 (1 row) tolik=# select a(null,2); NOTICE: 1: <NULL> 2: <NULL>a --- (1 row) -- Anatoly K. Lasareff Email: tolik@aaanet.ru
>>>>> "GV" == Graham Vickrage <graham@digitalplanit.com> writes: GV> Thanks AnatolyGV> So if I understand you correctly you can't pass more than one NULL int intoGV> 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 withGV>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]OnGV> Behalf Of AnatolyK. LasareffGV> Sent: 23 August 2000 12:46GV> To: Tom LaneGV> Cc: Graham Vickrage; postgresqlGV> Subject: Re: [SQL]Null function parameters >>>>> "TL" == Tom Lane <tgl@sss.pgh.pa.us> writes: TL> "Graham Vickrage" <graham@digitalplanit.com> writes:>>> However my understanding was that if the default value is SQLNULL thenGV> any>>> values passed into the function that are null would be treated asGV> 'NULL'. TL> Not sure what you think you meant by that, but a null is a null. IfGV> youTL> declared the table column as NOT NULLthen Postgres is doing exactlyTL> 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 whichGV> caseTL> 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> beginGV> raise notice ''1: % 2: %'', $1, $2; GV> if $1 is null thenGV> return $2;GV> end if; GV> return $1;GV> end;GV> ' language 'plpgsql'; GV> tolik=# select a(1,2);GV> NOTICE: 1: 1 2: 2GV> aGV> ---GV> 1GV> (1 row) GV> tolik=# select a(null,2);GV> NOTICE: 1: <NULL> 2: <NULL>GV> aGV> --- GV> (1 row) -- Anatoly K. Lasareff Email: tolik@aaanet.ru
tolik@aaanet.ru (Anatoly K. Lasareff) writes:
> 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.
Yes --- and not only that, but the function's result will be taken to be
null whether you want it to be or not. This has been gone over *many*
times before on this mail list, so I didn't think I needed to repeat it.
This will be fixed in 7.1 (is already fixed in current sources).
regards, tom lane
>>>>> "TL" == Tom Lane <tgl@sss.pgh.pa.us> writes: TL> tolik@aaanet.ru (Anatoly K. Lasareff) writes:>> 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. TL> Yes --- and not only that, but the function's result will be taken to beTL> null whether you want it to be or not. Thishas been gone over *many*TL> times before on this mail list, so I didn't think I needed to repeat it. TL> This will be fixed in 7.1 (is already fixed in current sources). Thank you. I'll be waiting :) -- Anatoly K. Lasareff Email: tolik@aaanet.ru