{"id":4560,"date":"2017-02-22T10:18:11","date_gmt":"2017-02-22T01:18:11","guid":{"rendered":"http:\/\/www.gisdeveloper.co.kr\/?p=4560"},"modified":"2020-05-28T15:54:06","modified_gmt":"2020-05-28T06:54:06","slug":"postgresql%ec%9d%98-plpgsql-%ed%8a%9c%ed%86%a0%eb%a6%ac%ec%96%bc-2-%ed%95%a8%ec%88%98-%ec%9d%b8%ec%9e%90","status":"publish","type":"post","link":"http:\/\/www.gisdeveloper.co.kr\/?p=4560","title":{"rendered":"PostgreSQL\uc758 PL\/pgSQL \ud29c\ud1a0\ub9ac\uc5bc \u2013 2 : \ud568\uc218 \uc778\uc790"},"content":{"rendered":"

\uc548\ub155\ud558\uc138\uc694, GIS Developer \uae40\ud615\uc900\uc785\ub2c8\ub2e4. 2\ubc88\uc9f8 PostgreSQL\uc758 Stored Procedure\uc758 \ud29c\ud1a0\ub9ac\uc5bc\uc785\ub2c8\ub2e4. \uc774\ubc88\uc5d0\ub294 \uc0ac\uc6a9\uc790 \uc815\uc758 \ud568\uc218\ub97c \uc0dd\uc131\ud558\ub294 CREATE FUNCTION \uad6c\ubb38\uc758 \ud55c\uac00\uc9c0 \uc608\ub85c \uc544\ub798\uc758 \ucf54\ub4dc\ub97c \uc0b4\ud3b4\ubcf4\uaca0\uc2b5\ub2c8\ub2e4.<\/p>\n

\r\nCREATE FUNCTION add(a INTEGER, b INTEGER)\r\nRETURNS INTEGER AS\r\n$$ BEGIN\r\n    RETURN a+b;\r\nEND; $$\r\nLANGUAGE PLPGSQL;<\/pre>\n
\uc704\uc758 \ucf54\ub4dc \uc911 CREATE FUNCTION \ucf54\ub4dc \ub300\uc2e0 CREATE OR REPLACE FUNCTION \ucf54\ub4dc\ub85c \ub300\uccb4\ud558\uba74 \uc544\ub798\uc640 \uac19\uc740\ub370\uc694.<\/p>\n
\r\nCREATE OR REPLACE FUNCTION add(a INTEGER, b INTEGER)\r\nRETURNS INTEGER AS\r\n$$ BEGIN\r\n    RETURN a+b;\r\nEND; $$\r\nLANGUAGE PLPGSQL;<\/pre>\n
\uc704\uc758 CREATE OR REPLACE FUNCTION \ucf54\ub4dc\ub294 CREATE FUNCTION\uc640 \ub2e4\ub974\uac8c \uc774\ubbf8 \ub3d9\uc77c\ud55c \ud568\uc218\uac00 \uc874\uc7ac\ud560 \uacbd\uc6b0 \ud568\uc218\ub97c \uc0c8\ub86d\uac8c \uad50\uccb4\ud558\ub77c\ub294 \uc758\ubbf8\ub97c \uac16\uc2b5\ub2c8\ub2e4. \uc774 add \ud568\uc218\ub294 \uc815\uc218\ud615 \ud0c0\uc785\uc778 a\uc640 b \uc778\uc790\ub97c \uac16\uc2b5\ub2c8\ub2e4. \uae30\ubcf8\uc801\uc73c\ub85c \uc778\uc790\ub294 IN \ud615\uc2dd\uc785\ub2c8\ub2e4. IN \ud615\uc2dd \uc774\uc678\uc5d0 OUT\uacfc INOUT \ud615\uc2dd\uc758 \uc778\uc790\ub3c4 \uc9c0\uc815\uc774 \uac00\ub2a5\ud569\ub2c8\ub2e4. IN \ud615\uc2dd\uc73c\ub85c \uc9c0\uc815\ub41c \uc778\uc790\ub294 \ub2e8\uc9c0 \ud568\uc218\uc5d0 \uac12\ub9cc\uc744 \uc804\ub2ec\ud574 \uc8fc\ub294 \ubaa9\uc801\uc774\uace0, OUT\uc740 \ud568\uc218 \uc885\ub8cc\uc2dc \uc774 \uc778\uc790\uc5d0 \uac12\uc744 \uc804\ub2ec\ud574 \uc678\ubd80\uc5d0 \ubc18\ud658\ud574\uc8fc\ub294 \ubaa9\uc801\ub3c4 \uac16\uc2b5\ub2c8\ub2e4. INOUT\uc740 IN\uacfc OUT\uc5d0 \ub300\ud55c \ubaa9\uc801\uc744 \ubaa8\ub450 \uac16\uc2b5\ub2c8\ub2e4. <\/p>\n
\ud568\uc218\uc758 \uc778\uc790\ub97c OUT\uc73c\ub85c \uc9c0\uc815\ud558\ub294 \uc608\ub97c \uc0b4\ud3b4 \ubcf4\uaca0\uc2b5\ub2c8\ub2e4. \uc544\ub798\uc758 \ucf54\ub4dc\ub97c \uc608\ub85c \ub4e4\uc5b4 OUT \ud615\uc2dd\uc758 \ud568\uc218 \uc778\uc790\ub97c \uc0b4\ud3b4 \ubcf4\uaca0\uc2b5\ub2c8\ub2e4.<\/p>\n
\r\nCREATE OR REPLACE FUNCTION getmaxmin(\r\n    v1 NUMERIC, \r\n    v2 NUMERIC,\r\n    OUT min_value NUMERIC,\r\n    OUT max_value NUMERIC)\r\nAS \r\n$$ BEGIN\r\n    min_value := GREATEST(v1, v2);\r\n    max_value := LEAST(v1, v2);\r\nEND; $$\r\nLANGUAGE PLPGSQL;<\/pre>\n
\uc704\uc758 getmaxmin\uc774\ub77c\ub294 \ud568\uc218\ub294 4\uac1c\uc758 \uc778\uc790\ub97c \ubc1b\uc2b5\ub2c8\ub2e4. \uccab\ubc88\uc9f8\uc640 \ub450\ubc88\uc9f8 \uc778\uc790\ub294 \ubcc4\ub3c4\uc758 \uc9c0\uc815\uc774 \uc5c6\uc73c\ubbc0\ub85c IN \ud615\uc2dd\uc774\uace0 min_value\uc640 max_value\ub294 OUT \ud615\uc2dd\uc73c\ub85c \uc9c0\uc815\ud558\uc600\uc2b5\ub2c8\ub2e4. \uc774 \ud568\uc218\ub294 \uccab\ubc88\uc9f8\uc640 \ub450\ubc88\uc9f8\ub85c \uc9c0\uc815\ub41c \uc778\uc790\uac12\uc911 \ucd5c\ub300\uac12\uc744 max_value\uc758 \uc778\uc790\ub85c \ub118\uae30\uace0 \ucd5c\uc18c\uac12\uc740 min_value\uc758 \uc778\uc790\ub85c \ub118\uae41\ub2c8\ub2e4. \uc774 \ud568\uc218\ub97c \ubcf4\uba74 \ubcc4\ub3c4\uc758 \ubc18\ud658\uac12\uc744 \uc9c0\uc815\ud558\uae30 \uc704\ud55c RETURNS \uad6c\ubb38\uc744 \uc0ac\uc6a9\ud558\uc9c0 \uc54a\uace0, \uc774\ucc98\ub7fc OUT \ud615\uc2dd\uc73c\ub85c \uc9c0\uc815\ub41c \uc778\uc790\uc5d0 \uac12\uc744 \ubc18\ud658\ud558\uace0 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n
\uc774 getmaxmin \ud568\uc218\ub294 \uc544\ub798\uc758 \ud654\uba74\ucc98\ub7fc \uc2e4\ud589\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n
\"\"<\/p>\n
\ub2e4\uc74c\uc73c\ub85c INOUT \ud615\uc2dd\uc740 IN\uacfc OUT\uc5d0 \ub300\ud55c \ud2b9\uc131 \ubaa8\ub4dc\ub97c \uac16\uc2b5\ub2c8\ub2e4. \uc989, \uac12\uc740 \ud568\uc218\ub85c \uc804\ub2ec\ud560 \uc218\ub3c4 \uc788\uace0 \ud568\uc218 \ub0b4\ubd80\uc5d0\uc11c \uac12\uc744 \ubcc0\uacbd\ud574 \uc678\ubd80\ub85c \uac12\uc744 \uc804\ub2ec\ub3c4 \ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4. \uc544\ub798\uc758 \ud568\uc218\ub97c \uc608\ub85c \uc0b4\ud3b4\ubcf4\uaca0\uc2b5\ub2c8\ub2e4.<\/p>\n
\r\nCREATE OR REPLACE FUNCTION getmaxmin2(\r\n    INOUT v1 NUMERIC, \r\n    INOUT v2 NUMERIC)\r\nAS \r\n$$ DECLARE\r\n    v3 NUMERIC := v1;\r\nBEGIN\r\n    v1 := GREATEST(v2, v3);\r\n    v2 := LEAST(v2, v3);\r\nEND; $$\r\nLANGUAGE PLPGSQL;<\/pre>\n
\uc774 getmaxmin2 \ud568\uc218\ub294 INOUT \ud615\uc2dd\uc758 \uc778\uc790 2\uac1c\ub97c \ubc1b\uc2b5\ub2c8\ub2e4. \uc774 \ub450\uac1c\uc758 \uc778\uc790\uac12\uc5d0\uc11c \ucd5c\ub300, \ucd5c\uc18c\uac12\uc744 \ub2e4\uc2dc \uc774 \uc778\uc790\uc758 \uccab\ubc88\uc9f8\uc640 \ub450\ubc88\uc9f8\uc5d0 \uac01\uac01 \uae30\ub85d\ud574 \ubc18\ud658\ud569\ub2c8\ub2e4. \uc774\ub97c \uc704\ud574 5~6\ubc88\uc5d0\uc11c v3 \ubcc0\uc218\ub97c \ud558\ub098 \uc815\uc758\ud574 \uc0ac\uc6a9\ud558\uace0 \uc788\uc2b5\ub2c8\ub2e4. \uc774 \ubcc0\uc218\uc5d0 \ub300\ud55c \uc790\uc138\ud55c \ub0b4\uc6a9\uc740 \ucd94\ud6c4 \ub2e4\ub978 \ud29c\ud1a0\ub9ac\uc5bc\uc5d0\uc11c \uc124\uba85\ud558\ub3c4\ub85d \ud558\uaca0\uc2b5\ub2c8\ub2e4. \uc774 getmaxmin2 \ud568\uc218\ub97c \uc2e4\ud589\ud558\uba74 \ub2e4\uc74c\uacfc \uac19\uc740 \uacb0\uacfc\ub97c \uc5bb\uc744 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n
\"\"<\/p>\n
IN, OUT, INOUT \ud615\uc2dd \uc774\uc678\uc5d0\ub3c4 \uc131\uaca9\uc740 \ub2e4\ub974\uc9c0\ub9cc VARIADIC \ud615\uc2dd\uc774 \uc788\uc2b5\ub2c8\ub2e4. \uc774 VARIADIC \ud615\uc2dd\uc740 \uac00\ubcc0\uc778\uc790\ub85c\uc368 \ub3d9\uc77c\ud55c \ub370\uc774\ud130 \ud0c0\uc785\uc758 \uc5ec\ub7ec\uac1c\uc758 \uc778\uc790\ub4e4\uc744 \ud55c\ubc88\uc5d0 \uc9c0\uc815\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4. \ub2e4\uc74c\uc758 \uc608\ub97c \uc0b4\ud3b4\ubcf4\uaca0\uc2b5\ub2c8\ub2e4.<\/p>\n
\r\nCREATE OR REPLACE FUNCTION sum(VARIADIC params numeric[]) \r\nRETURNS numeric AS \r\n$$ DECLARE\r\n    res numeric := 0;\r\nBEGIN\r\n    FOR i IN 1 .. array_length(params, 1) LOOP\r\n        res := res + params[i];\r\n    END LOOP;\r\n\r\n    RETURN res;\r\nEND; $$ \r\nLANGUAGE plpgsql;<\/pre>\n
\uc704\uc758 \ucf54\ub4dc\uc5d0\uc11c params \uc778\uc790\ub294 VARIADIC \ud615\uc2dd\uc774\uace0 \ud0c0\uc785\uc740 NUMERIC[]\uc785\ub2c8\ub2e4. \uc9c0\uc815\ud55c \uc778\uc790\uc758 \uac1c\uc218\ub294 array_length \ub0b4\uc7a5 \ud568\uc218\ub97c \uc0ac\uc6a9\ud574 \uc5bb\uc744 \uc218 \uc788\uc2b5\ub2c8\ub2e4. FOR \ubc18\ubcf5\ub9cc\uc744 \uc0ac\uc6a9\ud558\uace0 \uc788\ub294\ub370, 1\ubd80\ud130 \uc778\uc790\uc758 \uac1c\uc218\ub9cc\ud07c \ubc18\ubcf5\ud558\uace0 \uc788\uc2b5\ub2c8\ub2e4. \ubc18\ubcf5\ubb38\uc5d0 \ub300\ud574\uc11c\ub294 \ucd94\ud6c4 \uc790\uc138\ud788 \uc124\uba85\ud558\uaca0\uc2b5\ub2c8\ub2e4. \uc774 \ud568\uc218\ub294 \uc5ec\ub7ec\uac1c\uc758 \uc22b\uc790\uac12\ub4e4\ub85c \uc778\uc790\ub97c \uc9c0\uc815\ud558\uc5ec \uadf8 \ud569\ud55c \uacb0\uacfc\ub97c \ubc18\ud658\ud558\ub294\ub370\uc694. \uc774 \ud568\uc218\uc758 \uc2e4\ud589 \uacb0\uacfc\uc758 \uc608\ub294 \uc544\ub798\uc640 \uac19\uc2b5\ub2c8\ub2e4.<\/p>\n
\"\"<\/p>\n","protected":false},"excerpt":{"rendered":"
\uc548\ub155\ud558\uc138\uc694, GIS Developer \uae40\ud615\uc900\uc785\ub2c8\ub2e4. 2\ubc88\uc9f8 PostgreSQL\uc758 Stored Procedure\uc758 \ud29c\ud1a0\ub9ac\uc5bc\uc785\ub2c8\ub2e4. \uc774\ubc88\uc5d0\ub294 \uc0ac\uc6a9\uc790 \uc815\uc758 \ud568\uc218\ub97c \uc0dd\uc131\ud558\ub294 CREATE FUNCTION \uad6c\ubb38\uc758 \ud55c\uac00\uc9c0 \uc608\ub85c \uc544\ub798\uc758 \ucf54\ub4dc\ub97c \uc0b4\ud3b4\ubcf4\uaca0\uc2b5\ub2c8\ub2e4. CREATE FUNCTION add(a INTEGER, b INTEGER) RETURNS INTEGER AS $$ BEGIN RETURN a+b; END; $$ LANGUAGE PLPGSQL; \uc704\uc758 \ucf54\ub4dc \uc911 CREATE FUNCTION \ucf54\ub4dc \ub300\uc2e0 CREATE OR REPLACE FUNCTION \ucf54\ub4dc\ub85c \ub300\uccb4\ud558\uba74 \uc544\ub798\uc640 \uac19\uc740\ub370\uc694. CREATE … <\/p>\n
\ub354 \ubcf4\uae30 “PostgreSQL\uc758 PL\/pgSQL \ud29c\ud1a0\ub9ac\uc5bc \u2013 2 : \ud568\uc218 \uc778\uc790”<\/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":[50],"tags":[],"class_list":["post-4560","post","type-post","status-publish","format-standard","hentry","category-gisdev"],"_links":{"self":[{"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=\/wp\/v2\/posts\/4560","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=4560"}],"version-history":[{"count":11,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=\/wp\/v2\/posts\/4560\/revisions"}],"predecessor-version":[{"id":9700,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=\/wp\/v2\/posts\/4560\/revisions\/9700"}],"wp:attachment":[{"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4560"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4560"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4560"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}