File name
Commit message
Commit date
File name
Commit message
Commit date
DROP TABLE IF EXISTS public.tnmenuauthoinfo;
DROP TABLE IF EXISTS public.tnmenuprograminfo;
DROP TABLE IF EXISTS public.tnmenuinfo;
-- 메뉴권한정보
CREATE TABLE public.tnmenuauthoinfo
(
-- 권한SEQ
author_seq varchar(20) NOT NULL,
-- 권한코드
author_code varchar(30),
-- 권한사용자ID
user_id varchar(20),
-- 메뉴번호
menu_no varchar(20),
-- 프로그램URL
progr_url varchar(60),
-- 최초등록시점
frst_regist_pnttm timestamp,
-- 최초등록자ID
frst_register_id varchar(20),
CONSTRAINT PK_tnmenuauthoinfo PRIMARY KEY (author_seq)
) WITHOUT OIDS;
-- 메뉴정보
CREATE TABLE public.tnmenuinfo
(
-- 메뉴번호
menu_no varchar(20) NOT NULL,
-- 상위메뉴번호
upper_menu_no varchar(20),
-- 메뉴명
menu_nm varchar(60),
-- 메뉴순서
menu_ordr int,
-- 메뉴설명
menu_dc varchar(250),
-- 관련이미지
relate_image varchar(60),
-- 프로그램구분 C:컨텐츠 P:프로그램 B:게시판 D:폴더
progr_gb varchar(1),
-- 메뉴구분
menu_gb varchar(1),
-- 사용유무
use_yn varchar(1),
-- 최초등록시점
frst_regist_pnttm timestamp,
-- 최초등록자ID
frst_register_id varchar(20),
-- 최종수정시점
last_updt_pnttm timestamp,
-- 최종수정자ID
last_updusr_id varchar(20),
CONSTRAINT PK_tnmenuinfo PRIMARY KEY (menu_no)
) WITHOUT OIDS;
-- 메뉴프로그램정보
CREATE TABLE public.tnmenuprograminfo
(
-- 메뉴번호
menu_no varchar(20) NOT NULL,
-- 프로그램URL
progr_url varchar(60) NOT NULL,
-- 프로그램URL명
progr_url_nm varchar(60),
-- 대표URL설정
default_yn varchar(1) DEFAULT '''Y''::character varying',
-- 최초등록시점
frst_regist_pnttm timestamp,
-- 최초등록자ID
frst_register_id varchar(20),
-- 최종수정시점
last_updt_pnttm timestamp,
-- 최종수정자ID
last_updusr_id varchar(20),
CONSTRAINT PK_tnmenuprograminfo PRIMARY KEY (menu_no, progr_url)
) WITHOUT OIDS;
ALTER TABLE public.tnmenuinfo
ADD FOREIGN KEY (upper_menu_no)
REFERENCES public.tnmenuinfo (menu_no)
ON UPDATE NO ACTION
ON DELETE CASCADE
;
ALTER TABLE public.tnmenuprograminfo
ADD CONSTRAINT FK_tnmenuinfo_TO_tnmenuprograminfo FOREIGN KEY (menu_no)
REFERENCES public.tnmenuinfo (menu_no)
ON UPDATE CASCADE
ON DELETE CASCADE
;
ALTER TABLE public.tnmenuauthoinfo
ADD CONSTRAINT FK_tnmenuprograminfo_TO_tnmenuauthoinfo FOREIGN KEY (menu_no, progr_url)
REFERENCES public.tnmenuprograminfo (menu_no, progr_url)
ON UPDATE CASCADE
ON DELETE CASCADE
;
COMMENT ON TABLE public.tnmenuauthoinfo IS '메뉴권한정보';
COMMENT ON COLUMN public.tnmenuauthoinfo.author_seq IS '권한SEQ';
COMMENT ON COLUMN public.tnmenuauthoinfo.author_code IS '권한코드';
COMMENT ON COLUMN public.tnmenuauthoinfo.user_id IS '권한사용자ID';
COMMENT ON COLUMN public.tnmenuauthoinfo.menu_no IS '메뉴번호';
COMMENT ON COLUMN public.tnmenuauthoinfo.progr_url IS '프로그램URL';
COMMENT ON COLUMN public.tnmenuauthoinfo.frst_regist_pnttm IS '최초등록시점';
COMMENT ON COLUMN public.tnmenuauthoinfo.frst_register_id IS '최초등록자ID';
COMMENT ON TABLE public.tnmenuinfo IS '메뉴정보';
COMMENT ON COLUMN public.tnmenuinfo.menu_no IS '메뉴번호';
COMMENT ON COLUMN public.tnmenuinfo.upper_menu_no IS '상위메뉴번호';
COMMENT ON COLUMN public.tnmenuinfo.menu_nm IS '메뉴명';
COMMENT ON COLUMN public.tnmenuinfo.menu_ordr IS '메뉴순서';
COMMENT ON COLUMN public.tnmenuinfo.menu_dc IS '메뉴설명';
COMMENT ON COLUMN public.tnmenuinfo.relate_image IS '관련이미지';
COMMENT ON COLUMN public.tnmenuinfo.progr_gb IS '프로그램구분 C:컨텐츠 P:프로그램 B:게시판 D:폴더';
COMMENT ON COLUMN public.tnmenuinfo.menu_gb IS '메뉴구분';
COMMENT ON COLUMN public.tnmenuinfo.use_yn IS '사용유무';
COMMENT ON COLUMN public.tnmenuinfo.frst_regist_pnttm IS '최초등록시점';
COMMENT ON COLUMN public.tnmenuinfo.frst_register_id IS '최초등록자ID';
COMMENT ON COLUMN public.tnmenuinfo.last_updt_pnttm IS '최종수정시점';
COMMENT ON COLUMN public.tnmenuinfo.last_updusr_id IS '최종수정자ID';
COMMENT ON TABLE public.tnmenuprograminfo IS '메뉴프로그램정보';
COMMENT ON COLUMN public.tnmenuprograminfo.menu_no IS '메뉴번호';
COMMENT ON COLUMN public.tnmenuprograminfo.progr_url IS '프로그램URL';
COMMENT ON COLUMN public.tnmenuprograminfo.progr_url_nm IS '프로그램URL명';
COMMENT ON COLUMN public.tnmenuprograminfo.default_yn IS '대표URL설정';
COMMENT ON COLUMN public.tnmenuprograminfo.frst_regist_pnttm IS '최초등록시점';
COMMENT ON COLUMN public.tnmenuprograminfo.frst_register_id IS '최초등록자ID';
COMMENT ON COLUMN public.tnmenuprograminfo.last_updt_pnttm IS '최종수정시점';
COMMENT ON COLUMN public.tnmenuprograminfo.last_updusr_id IS '최종수정자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$
;
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$
;