Received: from golem.jpl.nasa.gov (root@hectic-4.jpl.nasa.gov [128.149.68.206]) by hub.org (8.8.8/8.8.8) with ESMTP id JAA11907; Thu, 24 Sep 1998 09:49:54 -0400 (EDT) (envelope-from lockhart@alumni.caltech.edu) Received: from alumni.caltech.edu (localhost [127.0.0.1]) by golem.jpl.nasa.gov (8.8.5/8.8.5) with ESMTP id NAA19299; Thu, 24 Sep 1998 13:50:22 GMT Sender: tgl@mythos.jpl.nasa.gov Message-ID: <360A4E1E.9F5C65FD@alumni.caltech.edu> Date: Thu, 24 Sep 1998 13:50:22 +0000 From: "Thomas G. Lockhart" Organization: Caltech/JPL X-Mailer: Mozilla 4.05 [en] (X11; I; Linux 2.0.30 i686) MIME-Version: 1.0 To: "Jose' Soares" CC: Colin Dick , pgsql-sql@postgresql.org, pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Re: [SQL] 2 questions. References: <14656.980923@bo.nettuno.it> <3609AFB0.A525E25D@alumni.caltech.edu> <360A2323.9396B4B5@sferacarta.com> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit > > 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