File name
Commit message
Commit date
File name
Commit message
Commit date
-- 권한코드로 권한명을 반환
CREATE OR REPLACE FUNCTION fncauthorcodenm(param1 character varying)
RETURNS character varying
LANGUAGE plpgsql
AS $function$BEGIN
RETURN (SELECT array_to_string(array_agg(b.author_nm), ',')
FROM unnest(string_to_array(param1, ',')) as a
, comtnauthorinfo as b
WHERE a.a = b.author_code);
end; $function$
;
COMMENT ON FUNCTION fncauthorcodenm(varchar) IS '권한코드 명을 반환한다.
fncauthorcodenm(author_code)';
-- 파일ID로 링크정보를 반환
CREATE OR REPLACE FUNCTION fnconvertfileid(param character varying)
RETURNS character varying
LANGUAGE plpgsql
AS $function$BEGIN
RETURN (
select array_to_string(array_agg(xx.link), ',')
from tnfile xx, (
select unnest(string_to_array(param, ',')) as fileid
) yy
where xx.file_id = yy.fileid
);
end; $function$
;
COMMENT ON FUNCTION fncmenudownurl(varchar) IS '파일아이디목록을 파일링크목록으로 변환한다.
fnconvertfileid(fileIdList)';
-- 메뉴ID, 사용자고유ID로 접속가능한 메뉴 반환(dir 제외)
CREATE OR REPLACE FUNCTION fncmenudownurl(param1 character varying, param2 character varying)
RETURNS character varying
LANGUAGE plpgsql
AS $function$BEGIN
RETURN (
with recursive menuView as (
select a.menu_no
, upper_menu_no
, b.progr_url
, 1 as depth
, array[b.progr_url::text] as path
, array[a.menu_ordr] as ordr
, false as cycle
from TNMENUINFO a
inner join TNMENUPROGRAMINFO b on a.menu_no = b.menu_no
where a.menu_no = param1
and a.use_yn = 'Y'
and b.default_yn = 'Y'
union all
select b.menu_no
, b.upper_menu_no
, c.progr_url
, depth + 1
, array_append(path, c.progr_url::text)
, array_append(ordr, b.menu_ordr)
, c.progr_url::text <> any(a.path)
from menuView a
inner join TNMENUINFO b on a.menu_no = b.upper_menu_no
inner join TNMENUPROGRAMINFO c on b.menu_no = c.menu_no
inner join TNMENUAUTHOINFO d on c.progr_url = d.progr_url
where b.use_yn = 'Y'
and c.default_yn = 'Y'
and a.upper_menu_no <> 'ROOT'
and not a.cycle
and (
d.author_code IN
(
select unnest(string_to_array(coalesce(
(
select author_code
from comtnemplyrscrtyestbs c
where scrty_dtrmn_trget_id = param2
)
, 'ROLE_ANONYMOUS'), ','))
)
or d.USER_ID = param2
)
)
select a.progr_url
from menuView a
order by a.cycle desc, a.ordr
limit 1
);
end; $function$
;
COMMENT ON FUNCTION fncmenudownurl(varchar,varchar) IS '하위 프로그램URL 을 반환한다.
fncmenudownurl(menu_no, estntl_id)';
-- 공통코드로 공통코드명을 반환
CREATE OR REPLACE FUNCTION fnccomcodenm(param1 character varying, param2 character varying)
RETURNS character varying
LANGUAGE plpgsql
AS $function$BEGIN
RETURN (SELECT CODE_NM
FROM COMTCCMMNDETAILCODE
WHERE CODE = PARAM1
AND CODE_ID = PARAM2);
end; $function$
;
COMMENT ON FUNCTION fnccomcodenm(varchar,varchar) IS '공통코드 명을 반환한다.
fnccomcodenm(code, code_id)';