{"id":4642,"date":"2017-02-27T22:53:26","date_gmt":"2017-02-27T13:53:26","guid":{"rendered":"http:\/\/www.gisdeveloper.co.kr\/?p=4642"},"modified":"2020-05-28T15:48:56","modified_gmt":"2020-05-28T06:48:56","slug":"postgresql%ec%9d%98-plpgsql-%ed%8a%9c%ed%86%a0%eb%a6%ac%ec%96%bc-7-%ec%a7%88%ec%9d%98-%ea%b2%b0%ea%b3%bc%eb%a5%bc-%eb%b0%98%ed%99%98%ed%95%98%eb%8a%94-%ed%95%a8%ec%88%98","status":"publish","type":"post","link":"http:\/\/www.gisdeveloper.co.kr\/?p=4642","title":{"rendered":"PostgreSQL\uc758 PL\/pgSQL \ud29c\ud1a0\ub9ac\uc5bc \u2013 7 : \uc9c8\uc758 \uacb0\uacfc\ub97c \ubc18\ud658\ud558\ub294 \ud568\uc218"},"content":{"rendered":"<p>PL\/pgSQL\ub85c \ub9cc\ub4e0 \ud568\uc218\uac00 \uc9c8\uc758(Query)\uc758 \uacb0\uacfc\ub85c\uc368 \ud14c\uc774\ube14(Table)\uc744 \ubc18\ud658\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4. \ud568\uc218\uac00 \ud14c\uc774\ube14\uc744 \ubc18\ud658\ud55c\ub2e4\ub294 \uc758\ubbf8\uac00 \uc5b4\ub5a4 \uac83\uc778\uc9c0 \uc2e4\uc81c \ucf54\ub4dc \uc608\ub97c \ud1b5\ud574 \ubcf4\uba74 \uc27d\uac8c \uc774\ud574\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4. \uba3c\uc800 \uc608\uc81c \ucf54\ub4dc\uc5d0\uc11c \uc0ac\uc6a9\ud560 person\uc774\ub77c\ub294 \ud14c\uc774\ube14\uc774 \uc544\ub798\uc640 \uac19\ub2e4\uace0 \ud569\uc2dc\ub2e4.<\/p>\n<p><img decoding=\"async\" src=\"http:\/\/www.gisdeveloper.co.kr\/wp-content\/uploads\/2017\/02\/plpgsql_t7_i1.png\" alt=\"\" width=\"700\" class=\"aligncenter size-full wp-image-4643\" \/><\/p>\n<p>person \ud14c\uc774\ube14\uc5d0\uc11c \ub098\uc774\uac12\uc744 \uc9c0\uc815\ud558\uba74 \uc9c0\uc815\ub41c \ub098\uc774 \uc774\uc0c1\uc778 \ub808\ucf54\ub4dc\ub97c \uc9c8\uc758\ud558\uc5ec \uadf8 \uacb0\uacfc\ub97c \ubc18\ud658\ud558\ub294 \ud568\uc218\ub97c \ub9cc\ub4e4\uc5b4 \ubcf4\uaca0\uc2b5\ub2c8\ub2e4.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">\r\nCREATE OR REPLACE FUNCTION get_persons(v INT)\r\nRETURNS TABLE (f_name CHAR(20), f_age INT)\r\nAS $$\r\nBEGIN\r\n    RETURN QUERY SELECT \r\n        name, age\r\n    FROM person\r\n    WHERE age >= v;\r\nEND; $$\r\nLANGUAGE 'plpgsql';\r\n<\/pre>\n<p>\uc704\uc758 \ucf54\ub4dc\ub97c \uc2e4\ud589\ud55c \ud654\uba74\uc774 \uc544\ub798\uc640 \uac19\uc740\ub370\uc694. get_persons(30)\uc744 \ud638\ucd9c\ud588\uc73c\ubbc0\ub85c 30\uc138 \uc774\uc0c1\uc778 \uc0ac\ub78c\ub9cc \uac80\uc0c9\ub418\ub294 \uac83\uc744 \ubcfc \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n<p><img decoding=\"async\" src=\"http:\/\/www.gisdeveloper.co.kr\/wp-content\/uploads\/2017\/02\/plpgsql_t7_i2.png\" alt=\"\" width=\"700\" class=\"aligncenter size-full wp-image-4645\" \/><\/p>\n<p>\ucf54\ub4dc\uc5d0 \ub300\ud574 \uc0b4\ud3b4\ubcf4\uba74, \uc774 \ud568\uc218\ub294 v\ub77c\ub294 \uc778\uc790\ub97c \ubc1b\uace0 \uc788\uc73c\uba70 2\ubc88 \ucf54\ub4dc\uc5d0 \ubc18\ud658 \ud0c0\uc785\uc774 TABLE\uc774\ub77c\uace0 \uba85\uc2dc\ub418\uc5b4 \uc788\uc2b5\ub2c8\ub2e4. TABLE \uad6c\ubb38 \ubc14\ub85c \ub4a4\uc5b4 \ud14c\uc774\ube14\uc758 \uc2a4\ud0a4\ub9c8\ub97c \uc815\uc758\ud558\uace0 \uc788\ub294\ub370\uc694. \ubc18\ud658\ud558\ub294 \ud14c\uc774\ube14\uc758 \uc2a4\ud0a4\ub9c8\uac00 CHAR(20) \ud0c0\uc785\uc758 f_name\uacfc INT \ud0c0\uc785\uc758 f_age \ud544\ub4dc\ub97c \uac16\uc2b5\ub2c8\ub2e4. \uc2e4\uc81c \ucffc\ub9ac\ub294 5\ubc88 \ucf54\ub4dc\uc758 QUERY \uad6c\ubd84 \ubc14\ub85c \ub2e4\uc74c\uc5d0 SELECT \uad6c\ubb38\uc744 \uc9c0\uc815\ud558\uace0 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n<p>\uc0ac\uc2e4, \uc9c8\uc758\ud55c \ucffc\ub9ac \uacb0\uacfc\ub97c \uadf8\ub300\ub85c \ubc18\ud658\ud558\uae30 \ubcf4\ub2e4\ub294 \ucffc\ub9ac \uacb0\uacfc\uc5d0 \ubb34\uc5b8\uac00 \uc5f0\uc0b0\uc744 \uc218\ud589\ud574 \uc7ac\uac00\uacf5\ud55c \uacb0\uacfc\ub97c \ubc18\ud658\ud558\ub294 \uac83\uc774 \uc77c\ubc18\uc801\uc785\ub2c8\ub2e4. <\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">\r\nCREATE OR REPLACE FUNCTION get_persons(v INT)\r\nRETURNS TABLE (f_name CHAR(20), f_age INT)\r\nAS $$\r\nDECLARE\r\n    r RECORD;\r\nBEGIN\r\n    FOR r IN (SELECT name, age \r\n             FROM person\r\n             WHERE age >= v)\r\n     LOOP\r\n         f_name := r.name || '(' || r.age || ')';\r\n         f_age := r.age - 1;\r\n         RETURN NEXT;\r\n     END LOOP;\r\nEND; $$\r\nLANGUAGE 'plpgsql';\r\n<\/pre>\n<p>\uc704\uc758 \ud568\uc218\ub97c \ubcf4\uba74 \uc774\ub984\uacfc \ub098\uc774\ub97c \uc870\ud569\ud55c \ubb38\uc790\uc5f4\uc5d0 \ub300\ud55c \ud544\ub4dc\uc640 \uc2e4\uc81c \ub098\uc774\uc5d0\uc11c \ud55c \uc0b4\uc744 \ube80 \ub098\uc774\uac12 \ud544\ub4dc\uc5d0 \ub300\ud55c \ud14c\uc774\ube14\uc744 \ubc18\ud658\ud558\ub294 \ud568\uc218\uc785\ub2c8\ub2e4. \uc2e4\ud589 \uacb0\uacfc\ub294 \ub2e4\uc74c\uacfc \uac19\uc2b5\ub2c8\ub2e4.<\/p>\n<p><img decoding=\"async\" src=\"http:\/\/www.gisdeveloper.co.kr\/wp-content\/uploads\/2017\/02\/plpgsql_t7_i3.png\" alt=\"\" width=\"700\" class=\"aligncenter size-full wp-image-4647\" \/><\/p>\n","protected":false},"excerpt":{"rendered":"<p>PL\/pgSQL\ub85c \ub9cc\ub4e0 \ud568\uc218\uac00 \uc9c8\uc758(Query)\uc758 \uacb0\uacfc\ub85c\uc368 \ud14c\uc774\ube14(Table)\uc744 \ubc18\ud658\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4. \ud568\uc218\uac00 \ud14c\uc774\ube14\uc744 \ubc18\ud658\ud55c\ub2e4\ub294 \uc758\ubbf8\uac00 \uc5b4\ub5a4 \uac83\uc778\uc9c0 \uc2e4\uc81c \ucf54\ub4dc \uc608\ub97c \ud1b5\ud574 \ubcf4\uba74 \uc27d\uac8c \uc774\ud574\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4. \uba3c\uc800 \uc608\uc81c \ucf54\ub4dc\uc5d0\uc11c \uc0ac\uc6a9\ud560 person\uc774\ub77c\ub294 \ud14c\uc774\ube14\uc774 \uc544\ub798\uc640 \uac19\ub2e4\uace0 \ud569\uc2dc\ub2e4. person \ud14c\uc774\ube14\uc5d0\uc11c \ub098\uc774\uac12\uc744 \uc9c0\uc815\ud558\uba74 \uc9c0\uc815\ub41c \ub098\uc774 \uc774\uc0c1\uc778 \ub808\ucf54\ub4dc\ub97c \uc9c8\uc758\ud558\uc5ec \uadf8 \uacb0\uacfc\ub97c \ubc18\ud658\ud558\ub294 \ud568\uc218\ub97c \ub9cc\ub4e4\uc5b4 \ubcf4\uaca0\uc2b5\ub2c8\ub2e4. CREATE OR REPLACE FUNCTION get_persons(v INT) RETURNS &hellip; <\/p>\n<p class=\"link-more\"><a href=\"http:\/\/www.gisdeveloper.co.kr\/?p=4642\" class=\"more-link\">\ub354 \ubcf4\uae30<span class=\"screen-reader-text\"> &#8220;PostgreSQL\uc758 PL\/pgSQL \ud29c\ud1a0\ub9ac\uc5bc \u2013 7 : \uc9c8\uc758 \uacb0\uacfc\ub97c \ubc18\ud658\ud558\ub294 \ud568\uc218&#8221;<\/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-4642","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\/4642","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=4642"}],"version-history":[{"count":5,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=\/wp\/v2\/posts\/4642\/revisions"}],"predecessor-version":[{"id":9688,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=\/wp\/v2\/posts\/4642\/revisions\/9688"}],"wp:attachment":[{"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4642"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4642"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4642"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}