Appending query with if-else condition in PostgreSQL

Question

I have my function to run SELECT query with 3 condition of HAVING CLAUSE:

1. having sum() > 0
2. having sum() <= 0
3. dont have HAVING CLAUSE

Here is my function:

    DROP function getf(arg int);

    create or replace function getf(arg int) 

    returns table (

    option_id bigint,

    importQuantity bigint,

    sold bigint,

    remain bigint

    )

    as $$

    begin 

        if arg = 1 then

            return query select b.option_id, SUM(b.import_quantity)::bigint as importQuantity, SUM(b.sold_quantity)::bigint as sold, SUM(b.remaining_quantity)::bigint as remain from batch b where b.product_id = 220 and b.option_id in (select o.id from "option" o where o.barcode like '%%' or o.barcode is null) group by b.option_id having sum(b.remaining_quantity) > 0;
    
        elsif arg = 2 then 

            return query select b.option_id, SUM(b.import_quantity)::bigint as importQuantity, SUM(b.sold_quantity)::bigint as sold, SUM(b.remaining_quantity)::bigint as remain from batch b where b.product_id = 220 and b.option_id in (select o.id from "option" o where o.barcode like '%%' or o.barcode is null) group by b.option_id having sum(b.remaining_quantity) <= 0;
    
        elsif arg = 3 then

            return query select b.option_id, SUM(b.import_quantity)::bigint as importQuantity, SUM(b.sold_quantity)::bigint as sold, SUM(b.remaining_quantity)::bigint as remain from batch b where b.product_id = 220 and b.option_id in (select o.id from "option" o where o.barcode like '%%' or o.barcode is null) group by b.option_id;

        end if;

    end; $$ language plpgsql;

And I call my function:

  select getf(3);

Question

The function work fine. But SELECT query only different at HAVING CLAUSE How can I use dynamic query to appending HAVING with if-else condition?

Answer 1

try using with CTE clause. you can have your query (till group by) in with clause.

Then use can use your if conditions and select from with CTE and and append having clause.

For reference you can check How to declare a variable in a PostgreSQL query

Answer 2

Since you need to change the structure of the query not just replace a parameter value within the query you need dynamic SQL. But first lets put on a diet. That is remove unnecessary parts.

 b.option_id in (select o.id from "option" o where o.barcode like '%%' or o.barcode is null) 

This is unnecessary the WHERE clause contains a tautology (a statement that in always true). Why is this. Well the predicate o.barcode like '%%' actually check if the barcode contains 0 or more characters. Only NULL makes this false, but in that case the other predicate barcode is NULL will evaluate true,so the overall condition is always true. As a result the sub-query always returns as ALL ids in options table and b.option_id is always in the list. (That of course assumes batch.option_id is properly defined as not null and a FK to options).

Now lets consider that HAVING clause. The first two are trivial replacements, just replace the rvalue with '< 0' or '>= 0'. The third option presents a problem, as you need to remove the entire clause rather change the rvalue. Its not all that difficult, however it also can be turned into a simple rvalue change. The HAVING predicate simply needs to evaluate true and we accomplish the same thing. That is achieved by replacing it by 'is not null'. Finally we can create an array of the rvalue replacements avoid any if then logic on the parameter (arg). Other that validating it contains a valid value. So: see demo

create or replace function getf(arg int)
    returns table (option_id      bigint 
                  ,importQuantity bigint 
                  ,sold           bigint 
                  ,remain         bigint
                  )
   language plpgsql
 as $$
 declare

    k_having_arg constant text[] = array['> 0','<= 0', 'is not null']; -- Replacement values for RVALUE below
    k_base_query constant text =
         'select b.option_id'
              ', SUM(b.import_quantity)::bigint '
              ', SUM(b.sold_quantity)::bigint  '
              ', SUM(b.remaining_quantity)::bigint ' 
          ' from batch b' 
         ' where b.product_id = 220'
         ' group by b.option_id ' 
        ' having sum(b.remaining_quantity) %s; ';  -- expressions RVALUE to be replaces 
     
   l_exec_query text; 
   
begin 
    if arg not between 1 and 3 then
       raise exception 'Invalid Arg value (%) out of range, Must be 1, 2, or 3.', arg;  
    end if; 

    l_exec_query = format (k_base_query,k_having_arg[arg]);
    raise notice E'Running query\n%',l_exec_query;
    return query execute l_exec_query;
end;
$$;