Re:
От | Franco Bruno Borghesi |
---|---|
Тема | Re: |
Дата | |
Msg-id | 1088540479.1859.8.camel@taz.oficina обсуждение исходный текст |
Ответ на | ... (simonw@cornfield.org.uk) |
Список | 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@cornfield.org.uk wrote:
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
Вложения
В списке pgsql-general по дате отправления: