如何在PostgreSql的RETURNQUERYEXECUTE中撰寫IFELSE陳述句-有解無憂

在 PostgreSQL 11 中撰寫IF ELSE陳述句時出現語法錯誤。RETURN QUERY EXECUTE

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$;

呼叫功能：

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

輸出：

如何在 PostgreSql 的 RETURN QUERY EXECUTE 中撰寫 IF ELSE 陳述句

uj5u.com熱心網友回復：

閱讀評論中提示的有關 PL/pgSQL 語法的資訊。

但是，您可以合并您的查詢變體，并OR為可能為空/空的輸入引數使用純文本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$;

簡單得多。同樣的表現。

我使用format()，這是干凈的方法。當然，雖然您只連接一個整數值，但普通連接是相當安全的。

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/467473.html

標籤：PostgreSQL plpgsql postgresql-11

上一篇：兩個整數值之間的距離

下一篇：liquibasesql遷移問題