How to Write IF ELSE Statements inside RETURN QUERY EXECUTE in PostgreSql

Question

I'm getting syntax error while writing IF ELSE statements inside RETURN QUERY EXECUTE in PostgreSQL 11.

CREATE OR REPLACE FUNCTION tds_master.a_report(
    i_entity_id integer,
    i_client_id integer,
    i_branch_id integer,
    i_name text,
    i_finyear integer)
    RETURNS TABLE(employee_name character varying(100), pan character varying(10), optfor115bac character varying(1), 
                  taxable_income numeric, income_tax numeric, credit_us_87a numeric, surcharge numeric, education_cess numeric) 
    LANGUAGE 'plpgsql'AS
$BODY$
BEGIN
   RETURN QUERY EXECUTE 
    
   'if i_name != '' then   
     select
       ed.name as employee_name,
       ed.pan,
       sd.opting_for_us115bac,
       sd.total_taxable_income,
       sd.income_tax_on_total_income,
       sd.rebate_us87a,
       sd.surcharge,
       sd.education_cess
    from tds' || i_finyear || '.saldet sd
       inner join tds' || i_finyear || '.employee_deductee ed on ed.ed_id = sd.employee_id
       where sd.entity_id = $1 and sd.client_id = $2 and sd.branch_id = $3 and upper(ed.name)=upper(i_name);
       
    else
      
    select 
       ed.name as employee_name,
       ed.pan,
       sd.opting_for_us115bac,
       sd.total_taxable_income,
       sd.income_tax_on_total_income,
       sd.rebate_us87a,
       sd.surcharge,
       sd.education_cess
    from tds' || i_finyear || '.saldet sd
       inner join tds' || i_finyear || '.employee_deductee ed on ed.ed_id = sd.employee_id
       where sd.entity_id = $1 and sd.client_id = $2 and sd.branch_id = $3'
  
    USING i_entity_id, i_client_id, i_branch_id;
    
END;
$BODY$;

CALL Function:

select * 
from tds_master.a_report(1547,6393,0,'MADAKE VINOD BABURAO',2021);

OUTPUT:

EXECUTE executes SQL - and there is no IF in SQL. You need to use an IF inside PL/pgSQL and run different queries. if (...) then execute '...' else execute '...' end if; — user330315
– user330315, Commented Apr 28, 2022 at 12:22
Or just and (i_name ='' OR upper(ed.name)=upper(i_name)) and omit the else part. — wildplasser
– wildplasser, Commented Apr 28, 2022 at 12:24
from tds' || i_finyear || '.saldet sd <<-- you cannot use integers in string concatenation. Also: use the format() function to avoid quoting errors. — wildplasser
– wildplasser, Commented Apr 28, 2022 at 12:31

Erwin Brandstetter · Accepted Answer · 2022-04-28 15:57:31Z

0

Read up about PL/pgSQL syntax as hinted in the comments.

However, you can just merge your query variants and use a plain OR for the possibly empty / null input parameter i_name:

CREATE OR REPLACE FUNCTION tds_master.a_report(
    i_entity_id integer,
    i_client_id integer,
    i_branch_id integer,
    i_name text,
    i_finyear integer)
  RETURNS TABLE(employee_name varchar(100), pan varchar(10), optfor115bac varchar(1)
              , taxable_income numeric, income_tax numeric, credit_us_87a numeric
              , surcharge numeric, education_cess numeric) 
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE format(
   $q$
   SELECT ed.name -- AS employee_name
        , ed.pan
        , sd.opting_for_us115bac
        , sd.total_taxable_income
        , sd.income_tax_on_total_income
        , sd.rebate_us87a
        , sd.surcharge
        , sd.education_cess
   FROM   %1$I.saldet sd
   JOIN   %1$I.employee_deductee ed ON ed.ed_id = sd.employee_id
   WHERE  sd.entity_id = $1
   AND    sd.client_id = $2
   AND    sd.branch_id = $3       
   AND   (i_name = '' OR  i_name IS NULL OR upper(ed.name) = upper(i_name))  -- !
   $q$, 'tds' || i_finyear::text)
   USING i_entity_id, i_client_id, i_branch_id;
END
$func$;

Much simpler. Same performance.

I use format(), which is the clean approach. Granted, while you only concatenate an integer value, plain concatenation is reasonably safe.

answered Apr 28, 2022 at 15:57

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ABHISHEK Over a year ago

sorry for the late reply,, Thanks a lot it's perfectly works for me

Collectives™ on Stack Overflow

How to Write IF ELSE Statements inside RETURN QUERY EXECUTE in PostgreSql

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related