On Fri, Jan 23, 2009 at 06:32:17PM +0300, Igor Katson wrote: > Sam, I'm not sure if this is correct to do that, as you I don't want to > remember what will happen, if you use NULL = NULL or upper(NULL) etc.: > > WHERE > COALESCE(city_id = i_city_id, TRUE) AND > COALESCE(edu_id = i_edu_id, TRUE) AND > COALESCE(upper(firstname) ~ upper(i_firstname), TRUE) AND > COALESCE(upper(lastname) ~ upper(i_lastname), TRUE) I'm not quite sure what you mean when you say "I don't want to remember what will happen". Here is a state table of the various options you've presented: param tbl Sam's Your's/Michael's NULL NULL TRUE NULL NULL 0 TRUE TRUE NULL 1 TRUE TRUE 0 NULL TRUE NULL 0 0 TRUE TRUE 0 1 FALSE FALSE 1 NULL TRUE NULL 1 0 FALSE FALSE 1 1 TRUE TRUE The "tbl" column is the value for, say, edu_id; the "param" is the matching parameter value, say i_edu_id. "Sam's" is the output of doing a COALESCE and the "Your's/Michael's" column is the output of doing your original CASE statement or Michael's OR variant. The thing to note are the extra NULLs in your variant as these will cause any row with a NULL value in the table to never get returned. This may, or may not, be what you want! -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
