creating your own function should do the trick:
CREATE OR REPLACE FUNCTION nvl(TEXT, TEXT) RETURNS TEXT AS '
a ALIAS FOR $1;
b ALIAS FOR $2;
SELECT CASE WHEN a IS NULL OR char_length(a)=0 THEN b ELSE a 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
> 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
In response to
- at 2004-06-23 09:33:21 from simonw
pgsql-general by date
|Next:||From: Alvaro Herrera||Date: 2004-06-29 20:22:12|
|Subject: Re: Connection gets into state where all queries fail|
|Previous:||From: Joe Maldonado||Date: 2004-06-29 18:01:51|
|Subject: query failing with out of memory error message.|