Skip site navigation (1) Skip section navigation (2)

Re:

From: Franco Bruno Borghesi <franco(at)akyasociados(dot)com(dot)ar>
To: simonw(at)cornfield(dot)org(dot)uk
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re:
Date: 2004-06-29 20:21:20
Message-ID: 1088540479.1859.8.camel@taz.oficina (view raw or flat)
Thread:
Lists: pgsql-general
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(at)cornfield(dot)org(dot)uk wrote:

> Subject: Coalesce, isEmpty and nvl
> 
> Message-Id: <E1Bd43P-000535-00(at)gaul(dot)cornfield(dot)org(dot)uk>
> From: simonw(at)cornfield(dot)org(dot)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

In response to

  • at 2004-06-23 09:33:21 from simonw

pgsql-general by date

Next:From: Alvaro HerreraDate: 2004-06-29 20:22:12
Subject: Re: Connection gets into state where all queries fail
Previous:From: Joe MaldonadoDate: 2004-06-29 18:01:51
Subject: query failing with out of memory error message.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group