Message-ID: <501B905E.1050706@ringerc.id.au>
Date: Fri, 03 Aug 2012 16:48:30 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:14.0) Gecko/20120717 Thunderbird/14.0
MIME-Version: 1.0
To: Laszlo Nagy <gandalf@shopzeus.com>
CC: pgsql-admin@postgresql.org
Subject: Re: need help to write a function in postgresql
References: <000b01cd658e$e66ae920$b340bb60$@avineonindia.com>
	<ju8nfr$aco$1@dough.gmane.org> <501B8DB7.5050700@shopzeus.com>
In-Reply-To: <501B8DB7.5050700@shopzeus.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

On 08/03/2012 04:37 PM, Laszlo Nagy wrote:
> It is also better because with a view, you can also do " name is null 
> ". But you cannot do that with a function (unless you write 
> unnecessary circumstancial code.)
>
While I agree with you on the view - among other things, it lets the 
query optimiser push conditions down into the view query - there is a 
reasonable answer to the problem of comparing to NULL. Just use IS 
DISTINCT FROM, eg:

    WHERE name IS DISTINCT FROM _test_variable

"IS DISTINCT FROM" is an equality comparison that treats null as a 
comparable value like any other, so "NULL IS DISTINCT FROM NULL" is 
false. Very handy.

--
Craig Ringer