95

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?

user2864740
  • 60,010
  • 15
  • 145
  • 220
MikeCW
  • 951
  • 1
  • 6
  • 3
  • 1
    Don't worry about "fastest" until you get "working" - even though there is working here, keep the question open and let the answers/suggestions come in. (And maybe the "better solution" is simply to not allow empty strings in the model ;-) – user2864740 Mar 13 '14 at 23:30
  • 1
    I agree with user2864740: if you want to prevent null and empty strings, why not disallow to put them into the database in the first place. –  Mar 13 '14 at 23:34

1 Answers1

162

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 ,'').

Dwayne Towell
  • 8,154
  • 4
  • 36
  • 49