How to use an optional parameter to filter results in a postgresql function?-CodePudding

I am trying to create a function that has 3 parameters. Two of them are required dates and one is an optional string.

CREATE OR REPLACE FUNCTION get_sales_overviews (
  date_one DATE,
  date_two Date,
  platform_type text default null
) returns table (
  total_margin float8,
  margin_currency text
) as $$
begin
return query SELECT
  sum(margin)::float8 as total_margin,
  sales_items.margin_currency
FROM
  sales_items
  WHERE sales_items.order_date  >= date_one
  AND sales_items.order_date <= date_two
  AND sales_items.source_platform_type = platform_type
GROUP BY
  sales_items.margin_currency
ORDER BY
  total_margin DESC;
END;
$$ language plpgsql;

When I run

select * from get_sales_overviews('2023-1-2', '2023-1-3', 'myPlatform');

I get the correct result but leaving the marketplace out like this:

select * from get_sales_overviews('2023-1-2', '2023-1-3');

returns nothing. I thought postgres automatically skips NULL where clauses? If that's not the case, how do I skip the platform_type query, if it is null?

CodePudding user response：

You are still comparing the rows to platform_type even if it is NULL, and of course that fails. You will need to adjust the query:

  AND (platform_type IS NULL OR sales_items.source_platform_type = platform_type)