-1

I'm wondering if I'm handling CASE WHEN appropriately below. I have optional parameters and want to make sure this is the best way to handle them.

Postgres version 9.3.14.

CREATE FUNCTION sp_contacts_get_all_by_id (user_id integer DEFAULT NULL::integer, vendor_id integer DEFAULT NULL::integer, filter_contact_type integer DEFAULT NULL::integer) 
  RETURNS TABLE(...)
    LANGUAGE plpgsql
AS $$

  BEGIN

    IF($1 IS NULL AND $2 IS NULL) THEN 
      RAISE EXCEPTION 'userId or vendorId is required!';
    END IF;

    RETURN QUERY
      SELECT
        *
      FROM
        myapp_contacts
      JOIN myapp_contacts_assoc ON
        myapp_contacts_assoc.contact_id=myapp_contacts.contact_id
      WHERE
        (CASE WHEN $1 IS NOT NULL THEN myapp_contacts_assoc.user_id=$1 END) AND
        (CASE WHEN $2 IS NOT NULL THEN myapp_contacts_assoc.vendor_id=$2 END) AND
        myapp_contacts.expired_at IS NULL AND
        (CASE WHEN $3 IS NOT NULL THEN myapp_contacts.type=$3 END);
  END;
$$
4
  • Have you tried this? Do you realize that if any of the 3 parameters is null, the query will return 0 rows? Nov 26, 2016 at 21:26
  • yeah I just posted below an alternative solution because CASE WHEN doesn't appear to work in this manner.
    – Dan
    Nov 26, 2016 at 21:35
  • 1
    It doesn't work because CASE expressions have a default of ELSE NULL. So when a parameter is null, the CASE expression goes to the default ELSE and the result (of the expression) is NULL=UNKNOWN. And only rows that the WHERE evaluates to TRUE go to the result of a query. Nov 26, 2016 at 21:39
  • Your solution/answer seems correct in that matter (but I don't know why you had that myapp_contacts.expired_at IS NULL in the first place or why you removed it). Nov 26, 2016 at 21:40

1 Answer 1

4

After some research I think this may be the best solution.

CREATE FUNCTION sp_contacts_get_all_by_id (user_id integer DEFAULT NULL::integer, vendor_id integer DEFAULT NULL::integer, filter_contact_type integer DEFAULT NULL::integer) 
  RETURNS TABLE(...)
    LANGUAGE plpgsql
AS $$

  BEGIN

    IF($1 IS NULL AND $2 IS NULL) THEN 
      RAISE EXCEPTION 'userId or vendorId is required!';
    END IF;

    RETURN QUERY
      SELECT
        *
      FROM
        myapp_contacts
      JOIN myapp_contacts_assoc ON
        myapp_contacts_assoc.contact_id=myapp_contacts.contact_id
      WHERE
        ($1 IS NULL OR myapp_contacts_assoc.user_id=$1) AND
        ($2 IS NULL OR myapp_contacts_assoc.vendor_id=$2) AND
        ($3 IS NULL OR myapp_contacts.type=$3);
  END;
$$

If someone knows of a better way to handle optional parameters, please feel free to post your solution.

2
  • 1
    You took the exact advice given by ypercube here in the question that I marked this as a duplicate of. Nov 27, 2016 at 0:23
  • 1
    I did not. It does look similar but the context is different. Maybe if you're a SQL master it all looks the same to you but if you're still learning, there is a big distinction in the two queries.
    – Dan
    Nov 27, 2016 at 21:56

Not the answer you're looking for? Browse other questions tagged or ask your own question.