{"id":5196,"date":"2018-04-27T20:11:40","date_gmt":"2018-04-27T11:11:40","guid":{"rendered":"http:\/\/www.gisdeveloper.co.kr\/?p=5196"},"modified":"2020-05-28T13:54:44","modified_gmt":"2020-05-28T04:54:44","slug":"postgresql%ec%9d%98-%ed%94%84%eb%a1%9c%ec%8b%9c%ec%a0%b8pl-pgsql-%ec%98%88%ec%a0%9c","status":"publish","type":"post","link":"http:\/\/www.gisdeveloper.co.kr\/?p=5196","title":{"rendered":"PostgreSQL\uc758 \ud504\ub85c\uc2dc\uc838(PL\/pgSQL) \uc608\uc81c"},"content":{"rendered":"<p>\ucd94\ud6c4 PostgreSQL\uc5d0\uc11c \ud504\ub85c\uc2dc\uc838\ub97c \uc791\uc131\ud560 \ub54c \ucc38\uc870\ud558\uae30 \uc704\ud574 \uae00\uc744 \ub0a8\uae41\ub2c8\ub2e4.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">\r\nCREATE OR REPLACE FUNCTION \r\n    get_two_rows(id1 TEXT, lyr1 TEXT, id2 TEXT, lyr2 TEXT) \r\nRETURNS \r\nTABLE (geom GEOMETRY)\r\nAS $$\r\nBEGIN\r\n\tRETURN QUERY EXECUTE\r\n\t\t'SELECT the_geom FROM ' || lyr1 || ' WHERE networkid::text = ''' || id1 || '''' || \r\n\t\t' UNION ALL ' || \r\n\t\t'SELECT the_geom FROM ' || lyr2 || ' WHERE networkid::text = ''' || id2 || '''';\r\n\r\nEND; $$\r\nLANGUAGE PLPGSQL;\r\n<\/pre>\n<p>SQL\ubb38\uc744 \ubb38\uc790\uc5f4 \ud615\ud0dc\ub85c \uc870\ud569\ud574\uc11c \uc2e4\ud589\ud558\uae30 \uc704\ud574 &#8216;QUERY EXECUTE&#8217;\ub97c \uc0ac\uc6a9\ud574\uc57c \ud558\uba70, \uc678\ub530\uc634\ud45c(&#8216;)\ub97c \ubb38\uc790\uc5f4\uc5d0 \ub123\uae30 \uc704\ud574 \uc5f0\uc18d\ub41c \uc678\ub530\uc634\ud45c 2\uac1c\uac00 \ud544\uc694\ud558\ub2e4\ub294 \uac83\uc5d0 \uc8fc\uc758\ud574\uc57c \ud569\ub2c8\ub2e4.<\/p>\n<p>\uc0ac\uc2e4 \uc704\uc758 \ud504\ub85c\uc2dc\uc838\ub294 \ub2e8\uc21c\ud574\uc11c \uadf8\ub0e5 SQL\ubb38\uc744 \ud1b5\ud574 Java\uc758 PrepareStatement\ub97c \uc0ac\uc6a9\ud574 \uc2e4\ud589\ud574\ub3c4 \ub418\ub294\ub370.. PrepareStatement\ub97c \ud1b5\ud55c SQL \ubb38 \uc2e4\ud589\uc2dc SQL\uc758 \ubb38\uc790\uc5f4 \uac12 \uc911 SELECT\uc758 FROM\uc808\uc5d0 \ub300\ud55c \ud14c\uc774\ube14\uba85\uc740 \ubc14\uc778\ub529\ud560 \uc218 \uc5c6\ub2e4(?\uc5d0 \uc758\ud55c..)\ub294 \uc81c\uc57d\ub54c\ubb38\uc5d0 \ud504\ub85c\uc2dc\uc838\ub97c \uc0ac\uc6a9\ud558\uac8c \ub418\uc5c8\uc2b5\ub2c8\ub2e4. \uc18c\uc2a4\ucf54\ub4dc\uc5d0 \ub300\ud55c \uc815\uc801\uac80\uc0ac\uc5d0\uc11c SQL\ubb38\uc5d0 \ub300\ud55c SQL Injection \ubcf4\uc548\uc131 \uc624\ub958\ub85c \uc9c0\uc801\ubc1b\uc544.. \uc704\uc640 \uac19\uc774 \uc0c1\ub2f9\ud788 \ubd88\ud544\uc694\ud55c \ud504\ub85c\uc2dc\uc838\uae4c\uc9c0 \uc0ac\uc6a9\ud558\uac8c \ub418\uc5c8\uc2b5\ub2c8\ub2e4.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\ucd94\ud6c4 PostgreSQL\uc5d0\uc11c \ud504\ub85c\uc2dc\uc838\ub97c \uc791\uc131\ud560 \ub54c \ucc38\uc870\ud558\uae30 \uc704\ud574 \uae00\uc744 \ub0a8\uae41\ub2c8\ub2e4. CREATE OR REPLACE FUNCTION get_two_rows(id1 TEXT, lyr1 TEXT, id2 TEXT, lyr2 TEXT) RETURNS TABLE (geom GEOMETRY) AS $$ BEGIN RETURN QUERY EXECUTE &#8216;SELECT the_geom FROM &#8216; || lyr1 || &#8216; WHERE networkid::text = &#8221;&#8217; || id1 || &#8221;&#8221; || &#8216; UNION ALL &#8216; || &#8216;SELECT the_geom &hellip; <\/p>\n<p class=\"link-more\"><a href=\"http:\/\/www.gisdeveloper.co.kr\/?p=5196\" class=\"more-link\">\ub354 \ubcf4\uae30<span class=\"screen-reader-text\"> &#8220;PostgreSQL\uc758 \ud504\ub85c\uc2dc\uc838(PL\/pgSQL) \uc608\uc81c&#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":[1],"tags":[],"class_list":["post-5196","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\/5196","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=5196"}],"version-history":[{"count":7,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=\/wp\/v2\/posts\/5196\/revisions"}],"predecessor-version":[{"id":9609,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=\/wp\/v2\/posts\/5196\/revisions\/9609"}],"wp:attachment":[{"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=5196"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=5196"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=5196"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}