pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Null function parameters
7+ messages / 3 participants
[nested] [flat]

* Null function parameters
@ 2000-08-22 15:37 Graham Vickrage <graham@digitalplanit.com>
  2000-08-22 17:15 ` Re: Null function parameters Tom Lane <tgl@sss.pgh.pa.us>
  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 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   ` Re: Null function parameters Anatoly K. Lasareff <tolik@aaanet.ru>
  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-22 15:37 Null function parameters Graham Vickrage <graham@digitalplanit.com>
  2000-08-22 17:15 ` Re: Null function parameters Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-08-23 11:46   ` Anatoly K. Lasareff <tolik@aaanet.ru>
  2000-08-23 12:07     ` RE: Null function parameters Graham Vickrage <graham@digitalplanit.com>
  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-22 15:37 Null function parameters Graham Vickrage <graham@digitalplanit.com>
  2000-08-22 17:15 ` Re: Null function parameters Tom Lane <tgl@sss.pgh.pa.us>
  2000-08-23 11:46   ` Re: Null function parameters Anatoly K. Lasareff <tolik@aaanet.ru>
@ 2000-08-23 12:07     ` Graham Vickrage <graham@digitalplanit.com>
  2000-08-23 13:37       ` Re: Null function parameters 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-22 15:37 Null function parameters Graham Vickrage <graham@digitalplanit.com>
  2000-08-22 17:15 ` Re: Null function parameters Tom Lane <tgl@sss.pgh.pa.us>
  2000-08-23 11:46   ` Re: Null function parameters Anatoly K. Lasareff <tolik@aaanet.ru>
  2000-08-23 12:07     ` RE: Null function parameters Graham Vickrage <graham@digitalplanit.com>
@ 2000-08-23 13:37       ` Anatoly K. Lasareff <tolik@aaanet.ru>
  2000-08-23 14:00         ` Re: Null function parameters Tom Lane <tgl@sss.pgh.pa.us>
  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-22 15:37 Null function parameters Graham Vickrage <graham@digitalplanit.com>
  2000-08-22 17:15 ` Re: Null function parameters Tom Lane <tgl@sss.pgh.pa.us>
  2000-08-23 11:46   ` Re: Null function parameters Anatoly K. Lasareff <tolik@aaanet.ru>
  2000-08-23 12:07     ` RE: Null function parameters Graham Vickrage <graham@digitalplanit.com>
  2000-08-23 13:37       ` Re: Null function parameters Anatoly K. Lasareff <tolik@aaanet.ru>
@ 2000-08-23 14:00         ` Tom Lane <tgl@sss.pgh.pa.us>
  2000-08-24 06:39           ` Re: Null function parameters 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-22 15:37 Null function parameters Graham Vickrage <graham@digitalplanit.com>
  2000-08-22 17:15 ` Re: Null function parameters Tom Lane <tgl@sss.pgh.pa.us>
  2000-08-23 11:46   ` Re: Null function parameters Anatoly K. Lasareff <tolik@aaanet.ru>
  2000-08-23 12:07     ` RE: Null function parameters Graham Vickrage <graham@digitalplanit.com>
  2000-08-23 13:37       ` Re: Null function parameters Anatoly K. Lasareff <tolik@aaanet.ru>
  2000-08-23 14:00         ` Re: Null function parameters Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-08-24 06:39           ` Anatoly K. Lasareff <tolik@aaanet.ru>
  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