Re:

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