pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
PL/PgSQL and NULL
6+ messages / 5 participants
[nested] [flat]

* PL/PgSQL and NULL
@ 2001-03-11 20:38 Andrew Perrin <aperrin@socrates.berkeley.edu>
  2001-03-11 21:38 ` Re: PL/PgSQL and NULL Peter Eisentraut <peter_e@gmx.net>
  2001-03-12 01:58 ` Re: PL/PgSQL and NULL Jie Liang <jliang@ipinc.com>
  0 siblings, 2 replies; 6+ messages in thread

From: Andrew Perrin @ 2001-03-11 20:38 UTC (permalink / raw)
  To: pgsql-sql

Greetings-

I'm trying to write what should be a simple function that returns the
minimim of two integers. The complication is that when one of the two
integers is NULL, it should return the other; and when both are NULL, it
should return NULL.  Here's what I've written:

CREATE FUNCTION min(int4, int4)
RETURNS int4
AS 'BEGIN
    IF $1 ISNULL
    THEN
	RETURN $2;
    ELSE 
	 IF $2 ISNULL
	 THEN
		RETURN $1;
	 ELSE 
	      IF $1 > $2
	      THEN
		RETURN $2;
	      ELSE
	        RETURN $1;
	      END IF;
	 END IF;
    END IF;
    END;'
LANGUAGE 'plpgsql';

and here's what I get:

fgdata=#  select min(10, NULL);
 min 
-----
    
(1 row)

so it looks like, for whatever reason, it's returning NULL when it should
be returning 10. Can anyone offer advice?

Thanks.

----------------------------------------------------------------------
Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology  
Chapel Hill, North Carolina, USA - http://demog.berkeley.edu/~aperrin
        aperrin@socrates.berkeley.edu - aperrin@igc.apc.org




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: PL/PgSQL and NULL
  2001-03-11 20:38 PL/PgSQL and NULL Andrew Perrin <aperrin@socrates.berkeley.edu>
@ 2001-03-11 21:38 ` Peter Eisentraut <peter_e@gmx.net>
  2001-03-11 22:19   ` Re: PL/PgSQL and NULL Ross J. Reedstrom <reedstrm@rice.edu>
  1 sibling, 1 reply; 6+ messages in thread

From: Peter Eisentraut @ 2001-03-11 21:38 UTC (permalink / raw)
  To: Andrew Perrin <aperrin@socrates.berkeley.edu>; +Cc: pgsql-sql

Andrew Perrin writes:

> I'm trying to write what should be a simple function that returns the
> minimim of two integers. The complication is that when one of the two
> integers is NULL, it should return the other; and when both are NULL, it
> should return NULL.

Functions involving NULLs don't work well before version 7.1.

-- 
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: PL/PgSQL and NULL
  2001-03-11 20:38 PL/PgSQL and NULL Andrew Perrin <aperrin@socrates.berkeley.edu>
  2001-03-11 21:38 ` Re: PL/PgSQL and NULL Peter Eisentraut <peter_e@gmx.net>
@ 2001-03-11 22:19   ` Ross J. Reedstrom <reedstrm@rice.edu>
  2001-03-11 23:57     ` Re: PL/PgSQL and NULL Andrew Perrin <aperrin@socrates.berkeley.edu>
  0 siblings, 1 reply; 6+ messages in thread

From: Ross J. Reedstrom @ 2001-03-11 22:19 UTC (permalink / raw)
  To: Andrew Perrin <aperrin@socrates.berkeley.edu>; +Cc: pgsql-sql


On Sun, Mar 11, 2001 at 10:38:10PM +0100, Peter Eisentraut wrote:
> Andrew Perrin writes:
> 
> > I'm trying to write what should be a simple function that returns the
> > minimim of two integers. The complication is that when one of the two
> > integers is NULL, it should return the other; and when both are NULL, it
> > should return NULL.
> 
> Functions involving NULLs don't work well before version 7.1.
> 

True but a little terse, aren't we Peter? Functions all return null if
any of their parameters are null, prior to v 7.1, as Peter pointed out.
In 7.1, they only behave this way if marked 'strict'.

Arguably, that's the _right_ behavior for the case your describing:
in tri-valued logic, NULL means UNKNOWN: it could be any value. So
min(x,NULL) is UNKNOWN for any value of x, since the NULL could be larger
or smaller.  If you want to do it anyway, you'll have to code your logic
directly in the SQL query. You'll find the COALESCE function useful:
it returns the first non-NULL argument. Combined with CASE, you should
be able to do return the minimum, non-null entry.

Exact code left as an excercise for the reader. ;-)

Ross



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: PL/PgSQL and NULL
  2001-03-11 20:38 PL/PgSQL and NULL Andrew Perrin <aperrin@socrates.berkeley.edu>
  2001-03-11 21:38 ` Re: PL/PgSQL and NULL Peter Eisentraut <peter_e@gmx.net>
  2001-03-11 22:19   ` Re: PL/PgSQL and NULL Ross J. Reedstrom <reedstrm@rice.edu>
@ 2001-03-11 23:57     ` Andrew Perrin <aperrin@socrates.berkeley.edu>
  0 siblings, 0 replies; 6+ messages in thread

From: Andrew Perrin @ 2001-03-11 23:57 UTC (permalink / raw)
  To: Ross J. Reedstrom <reedstrm@rice.edu>; +Cc: Andrew Perrin <aperrin@socrates.Berkeley.EDU>; pgsql-sql

Thanks - I'll work on it that way. I know the general-case min() should
probably return NULL if any element is null, but I'm in need of what I
described for a specific case in which the result should be "the minimum
non-null entry", which of course is NULL if all entries are null.

----------------------------------------------------------------------
Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology  
Chapel Hill, North Carolina, USA - http://demog.berkeley.edu/~aperrin
        aperrin@socrates.berkeley.edu - aperrin@igc.apc.org

On Sun, 11 Mar 2001, Ross J. Reedstrom wrote:

> 
> On Sun, Mar 11, 2001 at 10:38:10PM +0100, Peter Eisentraut wrote:
> > Andrew Perrin writes:
> > 
> > > I'm trying to write what should be a simple function that returns the
> > > minimim of two integers. The complication is that when one of the two
> > > integers is NULL, it should return the other; and when both are NULL, it
> > > should return NULL.
> > 
> > Functions involving NULLs don't work well before version 7.1.
> > 
> 
> True but a little terse, aren't we Peter? Functions all return null if
> any of their parameters are null, prior to v 7.1, as Peter pointed out.
> In 7.1, they only behave this way if marked 'strict'.
> 
> Arguably, that's the _right_ behavior for the case your describing:
> in tri-valued logic, NULL means UNKNOWN: it could be any value. So
> min(x,NULL) is UNKNOWN for any value of x, since the NULL could be larger
> or smaller.  If you want to do it anyway, you'll have to code your logic
> directly in the SQL query. You'll find the COALESCE function useful:
> it returns the first non-NULL argument. Combined with CASE, you should
> be able to do return the minimum, non-null entry.
> 
> Exact code left as an excercise for the reader. ;-)
> 
> Ross
> 




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: PL/PgSQL and NULL
  2001-03-11 20:38 PL/PgSQL and NULL Andrew Perrin <aperrin@socrates.berkeley.edu>
@ 2001-03-12 01:58 ` Jie Liang <jliang@ipinc.com>
  2001-03-15 15:02   ` Re: PL/PgSQL and NULL Jan Wieck <janwieck@Yahoo.com>
  1 sibling, 1 reply; 6+ messages in thread

From: Jie Liang @ 2001-03-12 01:58 UTC (permalink / raw)
  To: Andrew Perrin <aperrin@socrates.berkeley.edu>; +Cc: pgsql-sql


I think that is a bug in plpgsql,
when passing a NULL into a plpgsql defined function, it treats
other arguments as NULL also, you can use raise notice in
your function to watch this buggy thing(see following).

Jie LIANG

St. Bernard Software

10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873

jliang@ipinc.com
www.stbernard.com
www.ipinc.com

On Sun, 11 Mar 2001, Andrew Perrin wrote:

> Greetings-
> 
> I'm trying to write what should be a simple function that returns the
> minimim of two integers. The complication is that when one of the two
> integers is NULL, it should return the other; and when both are NULL, it
> should return NULL.  Here's what I've written:
> 
> CREATE FUNCTION min(int4, int4)
> RETURNS int4
> AS 'BEGIN
      raise notice ''arg1 is % arg2 is %'',$1,$2; -- debugging
>     IF $1 ISNULL
>     THEN
> 	RETURN $2;
>     ELSE 
> 	 IF $2 ISNULL
> 	 THEN
> 		RETURN $1;
> 	 ELSE 
> 	      IF $1 > $2
> 	      THEN
> 		RETURN $2;
> 	      ELSE
> 	        RETURN $1;
> 	      END IF;
> 	 END IF;
>     END IF;
>     END;'
> LANGUAGE 'plpgsql';
> 
> and here's what I get:
> 
> fgdata=#  select min(10, NULL);
>  min 
> -----
>     
> (1 row)
> 
> so it looks like, for whatever reason, it's returning NULL when it should
> be returning 10. Can anyone offer advice?
> 
> Thanks.
> 
> ----------------------------------------------------------------------
> Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology  
> Chapel Hill, North Carolina, USA - http://demog.berkeley.edu/~aperrin
>         aperrin@socrates.berkeley.edu - aperrin@igc.apc.org
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: PL/PgSQL and NULL
  2001-03-11 20:38 PL/PgSQL and NULL Andrew Perrin <aperrin@socrates.berkeley.edu>
  2001-03-12 01:58 ` Re: PL/PgSQL and NULL Jie Liang <jliang@ipinc.com>
@ 2001-03-15 15:02   ` Jan Wieck <janwieck@Yahoo.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Jan Wieck @ 2001-03-15 15:02 UTC (permalink / raw)
  To: Jie Liang <jliang@ipinc.com>; +Cc: Andrew Perrin <aperrin@socrates.berkeley.edu>; pgsql-sql

Jie Liang wrote:
>
> I think that is a bug in plpgsql,
> when passing a NULL into a plpgsql defined function, it treats
> other arguments as NULL also, you can use raise notice in
> your function to watch this buggy thing(see following).

    You're  blaming  the wrong code for it. It's an insufficience
    in the pre v7.1 function manager, not  a  bug  in  PL/pgSQL's
    handler.


Jan

>
> Jie LIANG
>
> St. Bernard Software
>
> 10350 Science Center Drive
> Suite 100, San Diego, CA 92121
> Office:(858)320-4873
>
> jliang@ipinc.com
> www.stbernard.com
> www.ipinc.com
>
> On Sun, 11 Mar 2001, Andrew Perrin wrote:
>
> > Greetings-
> >
> > I'm trying to write what should be a simple function that returns the
> > minimim of two integers. The complication is that when one of the two
> > integers is NULL, it should return the other; and when both are NULL, it
> > should return NULL.  Here's what I've written:
> >
> > CREATE FUNCTION min(int4, int4)
> > RETURNS int4
> > AS 'BEGIN
>       raise notice ''arg1 is % arg2 is %'',$1,$2; -- debugging
> >     IF $1 ISNULL
> >     THEN
> >  RETURN $2;
> >     ELSE
> >   IF $2 ISNULL
> >   THEN
> >       RETURN $1;
> >   ELSE
> >        IF $1 > $2
> >        THEN
> >       RETURN $2;
> >        ELSE
> >          RETURN $1;
> >        END IF;
> >   END IF;
> >     END IF;
> >     END;'
> > LANGUAGE 'plpgsql';
> >
> > and here's what I get:
> >
> > fgdata=#  select min(10, NULL);
> >  min
> > -----
> >
> > (1 row)
> >
> > so it looks like, for whatever reason, it's returning NULL when it should
> > be returning 10. Can anyone offer advice?
> >
> > Thanks.
> >
> > ----------------------------------------------------------------------
> > Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology
> > Chapel Hill, North Carolina, USA - http://demog.berkeley.edu/~aperrin
> >         aperrin@socrates.berkeley.edu - aperrin@igc.apc.org
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>


--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com




^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2001-03-15 15:02 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-11 20:38 PL/PgSQL and NULL Andrew Perrin <aperrin@socrates.berkeley.edu>
2001-03-11 21:38 ` Peter Eisentraut <peter_e@gmx.net>
2001-03-11 22:19   ` Ross J. Reedstrom <reedstrm@rice.edu>
2001-03-11 23:57     ` Andrew Perrin <aperrin@socrates.berkeley.edu>
2001-03-12 01:58 ` Jie Liang <jliang@ipinc.com>
2001-03-15 15:02   ` Jan Wieck <janwieck@Yahoo.com>

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