Обсуждение: ...

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

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
Mailscanner thanks transtec Computers for their support.

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