pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Thomas G. Lockhart <lockhart@alumni.caltech.edu>
To: Jose' Soares <jose@sferacarta.com>
Cc: Colin Dick <cdick@mail.ocis.net>
Cc: pgsql-sql@postgresql.org
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Re: [SQL] 2 questions.
Date: Thu, 24 Sep 1998 13:50:22 +0000
Message-ID: <360A4E1E.9F5C65FD@alumni.caltech.edu> (raw)
References: <Pine.LNX.3.96.980922095650.17825A-100000@ocis.ocis.net>
	<14656.980923@bo.nettuno.it>
	<3609AFB0.A525E25D@alumni.caltech.edu>
	<360A2323.9396B4B5@sferacarta.com>

> > 1) functions taking or returning "pass by value" arguments
> > cannot signal that the returned value should
> > actually be NULL. So they must throw an error instead. That's why
> > char_length() doesn't behave gracefully with nulls.

1a) functions taking only "pass by value" arguments are never called
with NULL arguments, since Postgres cannot indicate to the routine that
there is a NULL value (it uses a null pointer to indicate this with
"pass by reference" arguments, such as float8 or text).

> However SQL92 has some functions to help with nulls and I think we 
> NEED to have these on PostgreSQL.
> For example, I don't know how to compute fields containing nulls,
> because the result is always null.
> 
> AFAIK SQL92 has at least two functions that one can use to decide what
> to do with nulls. (i.e.:)
>         o   CASE WHEN ... ELSE ... END
> and
>         o   COALESCE()
> other databases have similar functions, for example Oracle has the
> function NVL() to translate nulls to an arbitrary value.

So Oracle does not have the CASE construct? That seems to be a rich area
for work in v6.5...

> I tried to write a function to emulate NVL() but it doesn't work 
> because nulls have a strange behavior.
> 
> int nvl(int arg)
> {
>         if(arg) return arg;
>         return 0;
> }
> 
> Any ideas?

You are running into the "pass by value" item (1a) above. And anyway,
you would not be able to do what you want since there is currently no
way to signal back that the input is NULL rather than zero.

In the long run, we would need to either convert all user-accessible
types to be "pass by reference", or would need to implement another
mechanism for signalling NULLness as a return condition (perhaps with a
global variable since each Postgres backend is single-threaded).

This would be a good topic to revisit after v6.4 is released...

                          - Tom



view thread (6+ messages)

Message-ID: <360A4E1E.9F5C65FD@alumni.caltech.edu>
Permalink:  ../360A4E1E.9F5C65FD@alumni.caltech.edu/
Also on:    postgresql.org/message-id/360A4E1E.9F5C65FD@alumni.caltech.edu

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: lockhart@alumni.caltech.edu, jose@sferacarta.com, cdick@mail.ocis.net, pgsql-hackers@postgresql.org
  Subject: Re: [HACKERS] Re: [SQL] 2 questions.
  In-Reply-To: <360A4E1E.9F5C65FD@alumni.caltech.edu>
  References: <Pine.LNX.3.96.980922095650.17825A-100000@ocis.ocis.net> <14656.980923@bo.nettuno.it> <3609AFB0.A525E25D@alumni.caltech.edu> <360A2323.9396B4B5@sferacarta.com> <360A4E1E.9F5C65FD@alumni.caltech.edu>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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