I need to perform a SELECT in postgres that filters records only when a certain argument value is not null. If the argument is null then the argument is ignored. I am experimenting with a function...

create or replace function my_func(argument INT)
returns table
            field1   VARCHAR(50),
            field2 VARCHAR(2),
            field3 BIGINT
language plpgsql
select d.field1, d.field2, COUNT(*) as record_count
             from my_table d
             group by d.field1, d.field2;

if argument IS NOT NULL
    -- TODO: How to filter the first recordset here?

How do I perform a second, optional SELECT in a function please? Or perhaps there's a slick way to do an option where clause?

  • 1
    use a temporary table – nbk Feb 10 at 21:33
  • temp tables worked. Thanks. Wondering if there are other approaches that involve less overhead? – narmaps Feb 10 at 21:50
select d.field1, d.field2, COUNT(*) as record_count

WHERE d.fieldX = argument OR argument IS NULL

from my_table d
group by d.field1, d.field2;

Be aware that this may not always produce an efficient access plan. If you experience performance issues, you'll likely be better off executing one of the two query variants conditionally, something like

IF argument IS NULL THEN
   SELECT ... -- without conditions
   SELECT ... WHERE fieldX = argument
  • Been writing SQL for 20 years and some of the most obvious things are hidden in plain sight. Thank you! – narmaps Feb 10 at 22:21
  • In reality I have 2 or 3 conditions any of which might be NULL. This is why I didn't pursue the conditional approach because it requires lots of code duplication to repeat all the possible query combinations. But I also need performance, so might resort to this in the end. Thanks. – narmaps Feb 11 at 0:30

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