Is CASE WHEN appropriate for optional parameters in a SQL query?

Question

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;
$$

score 4 · Answer 1 · answered Nov 26 '16 at 21:33

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.

Is CASE WHEN appropriate for optional parameters in a SQL query?

1 Answers1