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

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:

CodePudding user response：

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.