{"id":12017,"date":"2022-02-15T12:09:39","date_gmt":"2022-02-15T03:09:39","guid":{"rendered":"http:\/\/www.gisdeveloper.co.kr\/?p=12017"},"modified":"2022-02-15T12:21:47","modified_gmt":"2022-02-15T03:21:47","slug":"postresql%ec%9d%98-pl-pgsql%ec%97%90%ec%84%9c-%ec%b5%9c%eb%8c%80%ea%b0%92%ec%9d%84-%ea%b5%ac%ed%95%b4-%eb%8b%a4%ec%9d%8c-%ea%b0%92%ec%9c%bc%eb%a1%9c-%eb%8d%b0%ec%9d%b4%ed%84%b0-%ec%b6%94%ea%b0%80","status":"publish","type":"post","link":"http:\/\/www.gisdeveloper.co.kr\/?p=12017","title":{"rendered":"PostreSQL\uc758 PL\/pgSQL\uc5d0\uc11c, \ucd5c\ub300\uac12\uc744 \uad6c\ud574 \ub2e4\uc74c \uac12\uc73c\ub85c \ub370\uc774\ud130 \ucd94\uac00\ud558\uae30"},"content":{"rendered":"

\"\"<\/p>\n

\uba3c\uc800 \ub2e4\uc74c\uacfc \uac19\uc740 \ud14c\uc774\ube14\uc774 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n

\r\n-- DIRECTORY\r\nCREATE TABLE directory (\r\n    id INTEGER, -- \ub514\ub809\ud1a0\ub9ac ID\r\n    parent_id INTEGER, -- \ubd80\ubaa8 \ub514\ub809\ud1a0\ub9ac (null \uac12\uc77c \ub54c \ub8e8\ud2b8)\r\n    user_id INTEGER NOT NULL, -- \uc0ac\uc6a9\uc790 ID\r\n\r\n    PRIMARY KEY (id, user_id),\r\n    CONSTRAINT fk_parent_i FOREIGN KEY(user_id, parent_id) REFERENCES directory(user_id, id) ON DELETE CASCADE\r\n);\r\n<\/pre>\n
\uadf8\ub9ac\uace0 \uc774 \ud14c\uc774\ube14\uc5d0 \ub370\uc774\ud130\ub97c \ucd94\uac00\ud558\ub294 \ud568\uc218\ub294 \ub2e4\uc74c\uacfc \uac19\uc2b5\ub2c8\ub2e4.<\/p>\n
\r\n-- \uc2e0\uaddc \ub370\uc774\ud130 \ucd94\uac00 \ud568\uc218\r\nCREATE OR REPLACE FUNCTION add(_parent_id INTEGER, _user_id INTEGER) RETURNS INTEGER\r\nLANGUAGE plpgsql AS $$\r\nDECLARE\r\n    max_id INTEGER;\r\n    next_id INTEGER;\r\nBEGIN\r\n    -- \ud574\ub2f9 user\uc5d0 \ub300\ud574\uc11c \ucd5c\ub300\uac12\uc758 id \uac12\uc744 \uad6c\ud574\uc11c max_id \ubcc0\uc218\uc5d0 \ud560\ub2f9\r\n    SELECT MAX(id) INTO max_id FROM directory WHERE user_id = _user_id;\r\n\r\n    IF max_id IS NULL THEN -- max_id \uac12\uc774 \uc5c6\ub2e4\uba74\r\n        next_id := 0; -- \ub2e4\uc74c id \uac12\uc740 0\uc73c\ub85c \ud560\ub2f9\r\n    ELSE\r\n        next_id := max_id + 1; -- \ub2e4\uc74c id \uac12\uc740 \ucd5c\ub300\uac12\uc5d0 1\uc744 \ub354\ud574\uc11c \ud560\ub2f9\r\n    END IF;\r\n\t\r\n    -- \ub2e4\uc74c id \uac12\uc744 \ucf58\uc194\uc5d0 \ucd9c\ub825\ud574\uc11c \ud655\uc778\r\n    RAISE NOTICE 'next_id = %', next_id;\r\n\t\r\n    -- \ub2e4\uc74c id \uac12\uc73c\ub85c \ub370\uc774\ud130 \ucd94\uac00\r\n    INSERT INTO directory VALUES (next_id, _parent_id, _user_id);\r\n\r\n    -- \ub2e4\uc74c id \uac12 \ubc18\ud658\r\n    RETURN next_id;\r\nEND $$\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
\uba3c\uc800 \ub2e4\uc74c\uacfc \uac19\uc740 \ud14c\uc774\ube14\uc774 \uc788\uc2b5\ub2c8\ub2e4. — DIRECTORY CREATE TABLE directory ( id INTEGER, — \ub514\ub809\ud1a0\ub9ac ID parent_id INTEGER, — \ubd80\ubaa8 \ub514\ub809\ud1a0\ub9ac (null \uac12\uc77c \ub54c \ub8e8\ud2b8) user_id INTEGER NOT NULL, — \uc0ac\uc6a9\uc790 ID PRIMARY KEY (id, user_id), CONSTRAINT fk_parent_i FOREIGN KEY(user_id, parent_id) REFERENCES directory(user_id, id) ON DELETE CASCADE ); \uadf8\ub9ac\uace0 \uc774 \ud14c\uc774\ube14\uc5d0 \ub370\uc774\ud130\ub97c \ucd94\uac00\ud558\ub294 \ud568\uc218\ub294 \ub2e4\uc74c\uacfc … <\/p>\n
\ub354 \ubcf4\uae30 “PostreSQL\uc758 PL\/pgSQL\uc5d0\uc11c, \ucd5c\ub300\uac12\uc744 \uad6c\ud574 \ub2e4\uc74c \uac12\uc73c\ub85c \ub370\uc774\ud130 \ucd94\uac00\ud558\uae30”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-12017","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=\/wp\/v2\/posts\/12017","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=12017"}],"version-history":[{"count":3,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=\/wp\/v2\/posts\/12017\/revisions"}],"predecessor-version":[{"id":12020,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=\/wp\/v2\/posts\/12017\/revisions\/12020"}],"wp:attachment":[{"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=12017"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=12017"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=12017"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}