creating your own function should do the trick:
CREATE OR REPLACE FUNCTION nvl(TEXT, TEXT) RETURNS TEXT AS '
DECLARE
a ALIAS FOR $1;
b ALIAS FOR $2;
result TEXT;
BEGIN
SELECT CASE WHEN a IS NULL OR char_length(a)=0 THEN b ELSE a END INTO result;
RETURN result;
END;
' LANGUAGE 'plpgsql';
SELECT nvl('a', 'b'), nvl('', 'b'), nvl(NULL, 'b');
nvl | nvl | nvl
-----+-----+-----
a | b | b
About efficience I can only tell you that I've never had performance problems with plpgsql, I know that plpgsql caches query plans, but I really don't know how they compare to, for example, C functions.
On Wed, 2004-06-23 at 06:33, simonw@cornfield.org.uk wrote:
Subject: Coalesce, isEmpty and nvl
Message-Id: <E1Bd43P-000535-00@gaul.cornfield.org.uk>
From: simonw@cornfield.org.uk
Date: Wed, 23 Jun 2004 10:28:27 +0100
Hi
I have an application that I am porting from MySQL to Postgres, and have hit a
problem with coalesce.
I assumed that coalesce() works like nvl() and ifnull and will return the 2nd
argument if the first is NULL or Empty String, just like Orcale/SQLServer with nvl()
or MySQL with ifnull().
Is there a simple way to implement this function other creating an nvl() function
with pg/PLSQL.
Finally, how efficient are pg/PLSQL functions at runtime? Are they re-parsed with
every call, or are parsed once only?
Many thanks
Simon
