Включить условие IF.. ELSE в plpgsql и создать динамический запрос

Привет всем, у меня есть 2 таблицы dcrhd (которая содержит текущие данные) и dcrhd_arc (которая содержит исторические данные), и я создал функцию для получения некоторых данных из этих таблиц. Но эта функция удовлетворяет только половину моего требования (она проверяет данные из dcrhd только стол) я поделюсь своей функцией здесь..

CREATE OR REPLACE FUNCTION dcr_report( --fin_year_flag,
 finid integer, prdid integer, comp_cd CHARACTER varying, divid integer, fsid integer) RETURNS refcursor LANGUAGE 'plpgsql' AS $BODY$
    DECLARE
    ref refcursor;
    BEGIN
    open ref for SELECT hd.report_no,
       hd.dcr_date,
       coalesce(pr2.para_descr,' ') work_type,
       coalesce(pr1.para_descr,' ') hq_type,
       coalesce(rm.route_name,' ') route_name,
       coalesce(hd.doctor_visits,0) doctor_visits,
       coalesce(hd.stockist_visits,0) stockist_visits,
       coalesce(hd.retailer_visits,0) retailer_visits,
       hd.dcr_id,
       fm.fs_name,
       hd.fstaff_id,
       CASE hd.status
            WHEN 'A'   THEN 'APPROVED'
            WHEN 'D'   THEN 'DISCARDED'
            WHEN 'F'   THEN 'FORWARDED'
            WHEN 'E'   THEN 'DRAFT'
       END
  status,
       zsm.fs_name report1,
       rsm.fs_name report2,
       fm.geog_lvl1_hq,
       fm.level_code,
       coalesce(pm.para_descr,'SELF') joint_work,
       fm.fs_code,
       fm.emp_code,
       coalesce(hd.doc_other,0) doc_other
FROM dcrhd hd
LEFT OUTER JOIN parameters pm ON hd.jfw = pm.para_code AND pm.para_type = 'JFW'
LEFT OUTER JOIN route_master rm ON rm.fstaff_id = hd.fstaff_id AND rm.route_id = hd.route_id AND rm.company_cd
= comp_cd
LEFT OUTER JOIN parameters pr1 ON pr1.para_code = hd.hq_exhq AND pr1.para_type = 'HQ_',
     parameters pr2,
     field_master fm,
     field_master zsm,
     field_master rsm
WHERE hd.period_id = prdid AND hd.fin_year_id = finid AND hd.fstaff_id = fm.fs_id AND fm.mgr_level4 =
zsm.fs_id AND fm.mgr_level3 = rsm.fs_id AND fm.fs_id =
     CASE
          WHEN fsid = 0  THEN fm.fs_id
          ELSE fsid
     END
      AND fm.div_id =
     CASE
          WHEN divid = 0 THEN fm.div_id
          ELSE divid
     END
      AND fm.fs_id = hd.fstaff_id AND fm.level_code = '005' AND pr2.para_code = hd.work_type AND pr2.
      para_type = 'WTP' AND hd.company = comp_cd AND fm.company_cd = comp_cd
ORDER BY fm.fs_name,
         dcr_date;
    RETURN REF;
    END;
    $BODY$;

Мое требование: я просто хочу добавить новый параметр с именем 'fin_year_flag' и соответственно выбрать главную таблицу (например, if fin_year_flag='current', затем перейти к dcrhd, иначе перейти к dcrhd_arc, могу ли я добиться этого??? Не могли бы вы, ребята, поделиться своими идеями по этому поводу??? и Есть ли другой способ полностью выполнить мое требование? Я новичок в PostgreSQL, много раз гуглил в Интернете, но не нашел ничего полезного.


postgresql plpgsql postgresql-11
person vishnudas    schedule 04.12.2018    source источник
comment
Привет, если вы собираетесь публиковать такой массивный запрос, по крайней мере сделайте в нем правильный отступ, чтобы его было легко читать. Прямо сейчас это просто блок текста, который я не могу легко понять.   -  person 404    schedule 04.12.2018
comment
... AND hd.fstaff_id = fm.fs_id AND fm.mgr_level4 = zsm.fs_id AND fm.mgr_level3 = rsm.fs_id AND fm.fs_id ... эти условия сделают ваше ЛЕВОЕ СОЕДИНЕНИЕ простым соединением. Также: я сомневаюсь, что здесь нужен курсор.   -  person joop    schedule 04.12.2018
comment
Приносим извинения за доставленные неудобства. я буду более осторожен в своем следующем посте и спасибо за ваш комментарий ..   -  person vishnudas    schedule 06.12.2018

Ответы (1)


arrow_upward
0
arrow_downward

Код, который вы разместили, огромен, поэтому позвольте мне продемонстрировать вам, как динамически использовать имя таблицы и возвращать для него CURSOR, упрощая его.

Я создаю две таблицы с образцом строки.

create table dcrhd     as select 'CURRENT' ::TEXT  as col;
create table dcrhd_arc as select 'ARCHIVED'::TEXT  as col;

Это функция, которая использует OPEN <refcursor> FOR EXECUTE вместо динамически сгенерированного запроса. Вам нужно избежать одинарных кавычек в вашем основном SQL, используя другую кавычку или использовать котировка доллара.

Имя таблицы задается с помощью fin_year_flag из выражения CASE.

CREATE OR REPLACE FUNCTION dcr_report( fin_year_flag TEXT) 
RETURNS refcursor LANGUAGE plpgsql 
AS $BODY$
    DECLARE
    ref refcursor;
    v_table_name TEXT := CASE fin_year_flag
                                   WHEN 'current' THEN 'dcrhd'
                                 ELSE 'dcrhd_arc' END;

    v_sql  text := format('select col from %s',v_table_name );
    BEGIN
    open ref for EXECUTE v_sql ;
    RETURN REF;
    END;
$BODY$;
person Kaushik Nayak    schedule 04.12.2018
comment
ваш код мне очень помог. И у меня есть сомнения, могу ли я добавить больше переменных в тот же запрос ?? если я поделюсь одной из моих процедур (которая написана на oracle pl/sql), не могли бы вы помочь мне преобразовать ее в формат postgreSQL?? - person vishnudas; 06.12.2018
comment
@vishnudas: Да, можно использовать более одной переменной. Прочтите документацию или выполните SO ответы типа этот, чтобы понять больше - person Kaushik Nayak; 06.12.2018
comment
@vishnudas: я считаю, что это ответ на ваш первоначальный вопрос. Если вы считаете, что это помогло вам, отметьте его как принятое, чтобы оно также помогло другим. Прочтите: stackoverflow.com/help/someone-answers, чтобы узнать, почему это важно. Если у вас есть дополнительные требования, вам следует подумайте о том, чтобы задавать их как отдельные вопросы и не публиковать их в комментариях. - person Kaushik Nayak; 06.12.2018
comment
Обратите внимание, что вы должны использовать %I для идентификаторов (вместо %s для строк) при использовании format(...) для построения запросов - person Gerard van Helden; 24.06.2021