pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNull function parameters
7+ messages / 3 participants
[nested] [flat]
* Null function parameters
@ 2000-08-22 15:37 Graham Vickrage <graham@digitalplanit.com>
0 siblings, 1 reply; 7+ messages in thread
From: Graham Vickrage @ 2000-08-22 15:37 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Null function parameters
@ 2000-08-22 17:15 Tom Lane <tgl@sss.pgh.pa.us>
parent: Graham Vickrage <graham@digitalplanit.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2000-08-22 17:15 UTC (permalink / raw)
To: Graham Vickrage <graham@digitalplanit.com>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Null function parameters
@ 2000-08-23 11:46 Anatoly K. Lasareff <tolik@aaanet.ru>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: tolik@aaanet.ru @ 2000-08-23 11:46 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Graham Vickrage <graham@digitalplanit.com>; pgsql-sql
>>>>> "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 SQL NULL 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 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 case
TL> 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: 2
a
---
1
(1 row)
tolik=# select a(null,2);
NOTICE: 1: <NULL> 2: <NULL>
a
---
(1 row)
--
Anatoly K. Lasareff Email: tolik@aaanet.ru
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: Null function parameters
@ 2000-08-23 12:07 Graham Vickrage <graham@digitalplanit.com>
parent: Anatoly K. Lasareff <tolik@aaanet.ru>
0 siblings, 1 reply; 7+ messages in thread
From: Graham Vickrage @ 2000-08-23 12:07 UTC (permalink / raw)
To: Anatoly K. Lasareff <tolik@aaanet.ru>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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 SQL NULL 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
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
case
TL> 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: 2
a
---
1
(1 row)
tolik=# select a(null,2);
NOTICE: 1: <NULL> 2: <NULL>
a
---
(1 row)
--
Anatoly K. Lasareff Email: tolik@aaanet.ru
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Null function parameters
@ 2000-08-23 13:37 Anatoly K. Lasareff <tolik@aaanet.ru>
parent: Graham Vickrage <graham@digitalplanit.com>
0 siblings, 1 reply; 7+ messages in thread
From: tolik@aaanet.ru @ 2000-08-23 13:37 UTC (permalink / raw)
To: Graham Vickrage <graham@digitalplanit.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
>>>>> "GV" == Graham Vickrage <graham@digitalplanit.com> 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 <tgl@sss.pgh.pa.us> writes:
TL> "Graham Vickrage" <graham@digitalplanit.com> 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: <NULL> 2: <NULL>
GV> a
GV> ---
GV> (1 row)
--
Anatoly K. Lasareff Email: tolik@aaanet.ru
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Null function parameters
@ 2000-08-23 14:00 Tom Lane <tgl@sss.pgh.pa.us>
parent: Anatoly K. Lasareff <tolik@aaanet.ru>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2000-08-23 14:00 UTC (permalink / raw)
To: tolik@aaanet.ru; +Cc: Graham Vickrage <graham@digitalplanit.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Null function parameters
@ 2000-08-24 06:39 Anatoly K. Lasareff <tolik@aaanet.ru>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: tolik@aaanet.ru @ 2000-08-24 06:39 UTC (permalink / raw)
To: pgsql-sql
>>>>> "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 be
TL> null whether you want it to be or not. This has 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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2000-08-24 06:39 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-08-22 15:37 Null function parameters Graham Vickrage <graham@digitalplanit.com>
2000-08-22 17:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-08-23 11:46 ` Anatoly K. Lasareff <tolik@aaanet.ru>
2000-08-23 12:07 ` Graham Vickrage <graham@digitalplanit.com>
2000-08-23 13:37 ` Anatoly K. Lasareff <tolik@aaanet.ru>
2000-08-23 14:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-08-24 06:39 ` Anatoly K. Lasareff <tolik@aaanet.ru>
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