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?
select * from get_sales_overviews('2023-1-2', '2023-1-3', null);there is no optional input parameter in plpgsql and the functionget_sales_overviews(date, date)does not exist.platform_typeis NULL.AND coalesce(sales_items.source_platform_type = platform_type, true)