0

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?

3
  • try this : select * from get_sales_overviews('2023-1-2', '2023-1-3', null); there is no optional input parameter in plpgsql and the function get_sales_overviews(date, date) does not exist. Commented Jan 6, 2023 at 9:46
  • Still returns nothing Commented Jan 6, 2023 at 9:47
  • Do not skip it but rather coalesece it to TRUE if platform_type is NULL. AND coalesce(sales_items.source_platform_type = platform_type, true) Commented Jan 6, 2023 at 10:00

1 Answer 1

3

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)
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.