How to COALESCE for empty strings and NULL values?
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?
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 ,'')
.