How to COALESCE for empty strings and NULL values?

Question

I'm trying to make the fastest COALESCE() that accepts two or more arguments, and returns the first non-null AND non-empty ("") value.

I'm using this:

CREATE OR REPLACE FUNCTION coalescenonempty(VARIADIC in_ordered_actual varchar[]) RETURNS varchar AS $$   SELECT i   FROM (SELECT unnest($1) AS i) t   WHERE i IS NOT NULL AND i <> ''   LIMIT 1; $$ LANGUAGE sql; 

It's pretty fast, but still nowhere as fast as COALESCE or CASE WHEN statements.

What do you think?

Answer 1

Do not create a user function is you want speed. Instead of this:

coalescenonempty(col1,col2||'blah',col3,'none'); 

do this:

COALESCE(NULLIF(col1,''),NULLIF(col2||'blah',''),NULLIF(col3,''),'none'); 

That is, for each non-constant parameter, surround the actual parameter with NULLIF( x ,'').