X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 756F73A3F6A
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 10 Nov 2004 05:12:52 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 02125-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 10 Nov 2004 05:12:46 +0000 (GMT)
Received: from mail.mailpen.net (mail.mailpen.net [66.13.45.202])
	by svr1.postgresql.org (Postfix) with ESMTP id AC52C3A2BC3
	for <pgsql-sql@postgresql.org>; Wed, 10 Nov 2004 05:12:45 +0000 (GMT)
Received: from TpA22p1.ultimeth.com (TpA22p1.internal.mailpen.net
	[192.168.128.199])
	(using SSLv3 with cipher DES-CBC3-SHA (168/168 bits))
	(No client certificate requested)
	by mail.mailpen.net (Postfix) with ESMTP id 6D3BE31C01E
	for <pgsql-sql@postgresql.org>; Tue,  9 Nov 2004 21:12:44 -0800 (PST)
Message-Id: <5.1.0.14.2.20041109211131.00a89d00@imaps.mailpen.net>
X-Sender: mailer@postgresql4.ultimeth.com (Unverified)
X-Mailer: QUALCOMM Windows Eudora Version 5.1
Date: Tue, 09 Nov 2004 21:12:43 -0800
To: pgsql-sql@postgresql.org
From: "Dean Gibson (DB Administrator)" <postgresql4@ultimeth.com>
Subject: Re: Is NULLIF nullable?
In-Reply-To: <5.1.0.14.2.20041109192441.00ae3f68@imaps.mailpen.net>
References: <25393.1095739813@sss.pgh.pa.us>
	<5.1.0.14.2.20040920182022.00adf5e8@imaps.mailpen.net>
	<5.1.0.14.2.20040920164348.00a99868@imaps.mailpen.net>
	<5.1.0.14.2.20040920121606.00a89c68@imaps.mailpen.net>
	<414F02FF.8000603@numerixtechnology.de>
	<414F02FF.8000603@numerixtechnology.de>
	<5.1.0.14.2.20040920121606.00a89c68@imaps.mailpen.net>
	<5.1.0.14.2.20040920164348.00a99868@imaps.mailpen.net>
	<5.1.0.14.2.20040920182022.00adf5e8@imaps.mailpen.net>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"; format=flowed
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=
X-Spam-Level: 
X-Archive-Number: 200411/96
X-Sequence-Number: 19415

You can ignore my question below, since I just put the field definition 
below in an SQL FUNCTION and marked it STRICT.

-- Dean

Dean Gibson (DB Administrator) wrote on 2004-11-09 19:29:
>Recently I asked about why a field from the nullable side of an OUTER JOIN 
>was causing the JOIN to be inefficient, and was told that it was because 
>that field had a CASE statement as part of its definition, and that CASE 
>(and by extension COALESCE) were non-nullable constructs.
>
>Is NULLIF nullable, in that sense?
>
>Reason:  I'd like to define a VIEW with a field definition thusly:
>
>'P.O. Box' || NULLIF( po_box, ' '::char ) AS ...
>
>And I would like the field thusly defined to be nullable.
>
>-- Dean