Обсуждение: coalesce function

Torsdag 20. juni 2013 21.45.02 skrev itishree sukla:
> Hi All,
>
> I am using coalesce(firstname,lastname), to get the result if first name is
> 'NULL' it will give me lastname or either way. I am having data like
> instead of NULL,  blank null ( i mean something like '' ) for which
> coalesce is not working, is there any workaround or other function
> available in postgresql, please do let me know.

CASE WHEN firstname NOT IN (NULL, '') THEN firstname ELSE lastname END;

regards, Leif

itishree sukla wrote
> Hi All,
>
> I am using coalesce(firstname,lastname), to get the result if first name
> is
> 'NULL' it will give me lastname or either way. I am having data like
> instead of NULL,  blank null ( i mean something like '' ) for which
> coalesce is not working, is there any workaround or other function
> available in postgresql, please do let me know.
>
>
> Regards,
> Itishree

This is the solution I am currently using in my work:

Runs in 9.0

CREATE OR REPLACE FUNCTION coalesce_emptystring(VARIADIC in_ordered_actual
varchar[])
RETURNS varchar
AS $$

    SELECT input
    FROM ( SELECT unnest($1) AS input ) src
    WHERE input IS NOT NULL AND input <> ''
    LIMIT 1;

$$
LANGUAGE sql
STABLE
;

Same usage syntax as the built-in COALESCE but skips NULL and the
empty-string.  Note a string with only whitespace (i.e.,  '   ') is not
considered empty.

The problem with the "CASE" example provided is that while it works in the
specific case you are solving it does not readily generalize to more than 2
inputs.

Are you positive the "lastname" will always have a value?  You should
consider a last-resort default to ensure that the column never returns a
NULL.

coalesce_emptystring(firstname, lastname, 'Name Unknown')







--
View this message in context: http://postgresql.1045698.n5.nabble.com/coalesce-function-tp5760161p5760205.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Fri, Jun 21, 2013 at 7:36 AM, David Johnston <polobo@yahoo.com> wrote:
>         SELECT input
>         FROM ( SELECT unnest($1) AS input ) src
>         WHERE input IS NOT NULL AND input <> ''
>         LIMIT 1;

Does this guarantee the order of the results returned? Using LIMIT
without ORDER BY is something I've learned to avoid.

ChrisA

Chris Angelico wrote
> On Fri, Jun 21, 2013 at 7:36 AM, David Johnston <

> polobo@

> > wrote:
>>         SELECT input
>>         FROM ( SELECT unnest($1) AS input ) src
>>         WHERE input IS NOT NULL AND input <> ''
>>         LIMIT 1;
>
> Does this guarantee the order of the results returned? Using LIMIT
> without ORDER BY is something I've learned to avoid.
>
> ChrisA

I have thought about this and while I'm not 100% positive on the guarantee
the fact the input data is small means the planner should not be re-ordering
"src" in order to apply the where clause (if it would anyway...I think
re-ordering may only happen during joins).  To my knowledge the result of
unnest returns in the same order as the array so "src" already has an
implicit "ORDER BY" attached to it.  It is only when return physical
relation data is the order undefined.  Arrays and "VALUES" both are returned
in the order defined.

David J.





--
View this message in context: http://postgresql.1045698.n5.nabble.com/coalesce-function-tp5760161p5760342.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.