{"id":4621,"date":"2017-02-27T22:51:33","date_gmt":"2017-02-27T13:51:33","guid":{"rendered":"http:\/\/www.gisdeveloper.co.kr\/?p=4621"},"modified":"2020-05-28T15:50:24","modified_gmt":"2020-05-28T06:50:24","slug":"postgresql%ec%9d%98-plpgsql-%ed%8a%9c%ed%86%a0%eb%a6%ac%ec%96%bc-6-%eb%b0%98%eb%b3%b5%eb%ac%b8","status":"publish","type":"post","link":"http:\/\/www.gisdeveloper.co.kr\/?p=4621","title":{"rendered":"PostgreSQL\uc758 PL\/pgSQL \ud29c\ud1a0\ub9ac\uc5bc \u2013 6 : \ubc18\ubcf5\ubb38"},"content":{"rendered":"

\uc548\ub155\ud558\uc138\uc694, GIS Developer \uae40\ud615\uc900\uc785\ub2c8\ub2e4. \uc774\ubc88 \uae00\uc5d0\uc11c\ub294 PL\/pgSQL\uc5d0\uc11c \ubc18\ubcf5\ubb38\uc5d0 \ub300\ud574 \uc0b4\ud3b4\ubcf4\uaca0\uc2b5\ub2c8\ub2e4. PL\/pgSQL\uc5d0\uc11c \uc81c\uacf5\ud558\ub294 \ubc18\ubcf5\ubb38\uc740 LOOP, WHILE, FOR \ubb38\uc774 \uc788\ub294\ub370\uc694. \ud558\ub098\uc529 \uc0b4\ud3b4\ubcf4\ub3c4\ub85d \ud558\uaca0\uc2b5\ub2c8\ub2e4.<\/p>\n

\uba3c\uc800 \uc608\uc81c \ucf54\ub4dc\ub97c \ud1b5\ud574 LOOP \ubb38\uc744 \uc0b4\ud3b4 \ubcf4\ub3c4\ub85d \ud558\uaca0\uc2b5\ub2c8\ub2e4.<\/p>\n

\r\nCREATE OR REPLACE FUNCTION adder(n INTEGER)\r\nRETURNS INTEGER AS $$\r\nDECLARE\r\n    res INTEGER := 0;\r\n    i INTEGER;\r\nBEGIN\r\n    i := 1;\r\n\r\n    LOOP\r\n        res := res + i;\r\n        \r\n        EXIT WHEN i = n;\r\n        \r\n        SELECT i+1 INTO i;\r\n    END LOOP;\r\n\r\n    RETURN res;\r\nEND;\r\n$$ LANGUAGE plpgsql;\r\n<\/pre>\n
\uc704\uc758 adder \ud568\uc218\ub294 1\ubd80\ud130 \uc778\uc790\ub85c \uc8fc\uc5b4\uc9c4 \uc815\uc218\uac12\uae4c\uc9c0\uc758 \ud569\ud55c \ub204\uc801\uac12\uc744 \ubc18\ud658\ud558\ub294 \ud568\uc218\uc785\ub2c8\ub2e4. 9\ubc88~15\ubc88 \uae4c\uc9c0\uac00 LOOP \ubc18\ubcf5\ubb38\uc778\ub370\uc694. \ud544\uc218 \uc870\uac74\uc740 \uc544\ub2c8\uc9c0\ub9cc \ubc18\ubcf5\ubb38\uc740 \ubc18\ubcf5\uc744 \ub05d\ub0b4\uae30 \uc704\ud55c \uc870\uac74\uc774 \ud544\uc694\ud569\ub2c8\ub2e4. 12\ubc88 \ucf54\ub4dc\uac00 \ubc14\ub85c \ubc18\ubcf5\ubb38\uc744 \uc885\ub8cc\ud558\uae30 \uc704\ud55c \uc870\uac74 \ucf54\ub4dc\ub85c i \uac12\uacfc n \uac12\uc774 \uac19\uc73c\uba74 \ubc18\ubcf5\ubb38\uc744 \uc885\ub8cc\ud558\uac8c \ub429\ub2c8\ub2e4. \uc5ec\uae30\uc11c 14\ubc88 \ucf54\ub4dc\uac00 \uc7ac\ubbf8\uc788\ub294\ub370\uc694. \uc120\ud0dd\ud55c(SELECT) \uac12(i+1)\uc744 \uc6d0\ud558\ub294 \ubcc0\uc218(i) \uc548\uc73c\ub85c(INTO) \ub300\uc785\uc2dc\ucf1c \uc8fc\ub294 \uad6c\ubb38\uc785\ub2c8\ub2e4. \uc774 SELECT i+1 INTO i; \ubb38\uc740 i := i + 1; \uacfc \uac19\uc2b5\ub2c8\ub2e4. <\/p>\n
\uc2e4\ud589 \uacb0\uacfc\ub294 \uc544\ub798\uc640 \uac19\uc2b5\ub2c8\ub2e4.<\/p>\n
\"\"<\/p>\n
\ub2e4\uc74c\uc740 WHILE \ubc18\ubcf5\ubb38\uc744 \ud1b5\ud574 \uc704\uc758 adder \ud568\uc218\ub97c \uc7ac\uc791\uc131\ud574 \ubcf4\ub3c4\ub85d \ud558\uaca0\uc2b5\ub2c8\ub2e4. \uc544\ub798\uc640 \uac19\uc2b5\ub2c8\ub2e4.<\/p>\n
\r\nCREATE OR REPLACE FUNCTION adder(n INTEGER)\r\nRETURNS INTEGER AS $$\r\nDECLARE\r\n    result INTEGER := 0;\r\n    i INTEGER;\r\nBEGIN\r\n    i := 1;\r\n    \r\n    WHILE i <= n LOOP\r\n        SELECT result + i INTO result;\r\n\r\n        SELECT i+1 INTO i;\r\n    END LOOP;\r\n\r\n    RETURN result;\r\nEND;\r\n$$ LANGUAGE plpgsql;\r\n<\/pre>\n
WHILE \ubc18\ubcf5\ubb38\uc5d0 \ud574\ub2f9\ud558\ub294 \ucf54\ub4dc\ub294 9\ubc88 ~ 13\ubc88\uc778\ub370\uc694. WHILE \ubc18\ubcf5\ubb38\uc740 \ubc18\ubcf5\ubb38\uc758 \uc2dc\uc791\uacfc \ud568\uaed8 \ubc18\ubcf5 \uc870\uac74\uc774 \uc9c0\uc815\ub429\ub2c8\ub2e4. \uc774 \ubc18\ubcf5 \uc870\uac74\uc774 \ucc38(true)\uc77c \ub54c \ubc18\ubcf5\ubb38 \uc548\uc758 \ucf54\ub4dc(10\ubc88~12\ubc88)\uc774 \uc2e4\ud589\ub418\ub2e4\uac00, \ubc18\ubcf5 \uc870\uac74\uc774 \uac70\uc9d3(false)\uac00 \ub420\ub54c \ubc18\ubcf5\ubb38\uc744 \ud0c8\ucd9c\ud569\ub2c8\ub2e4.  <\/p>\n
\ub2e4\uc74c\uc740 \ubc18\ubcf5\ubb38 \uc911 \uac00\uc7a5 \uc720\uc5f0\ud55c FOR \ubb38\uc785\ub2c8\ub2e4. FOR \ubc18\ubcf5\ubb38\uc73c\ub85c \uc5ec\uae30 adder \ud568\uc218\ub97c \uc7ac\uc791\uc131\ud574 \ubcf4\ub3c4\ub85d \ud558\uaca0\uc2b5\ub2c8\ub2e4. \uc544\ub798\uc640 \uac19\uc2b5\ub2c8\ub2e4.<\/p>\n
\r\nCREATE OR REPLACE FUNCTION adder(n INTEGER)\r\nRETURNS INTEGER AS $$\r\nDECLARE\r\n    result INTEGER := 0;\r\nBEGIN\r\n    FOR i IN 1..n LOOP\r\n        RAISE NOTICE 'Iterator: %', i;\r\n        result := result + i;\r\n    END LOOP;\r\n\r\n    RETURN result;\r\nEND;\r\n$$ LANGUAGE plpgsql;\r\n<\/pre>\n
\uc704\uc758 \ucf54\ub4dc\uc5d0\uc11c \ubc18\ubcf5\ubb38\uc5d0 \ud574\ub2f9\ud558\ub294 \ucf54\ub4dc\ub294 6\ubc88~9\ubc88\uc785\ub2c8\ub2e4. \ubc18\ubcf5 \uc870\uac74\uc744 FOR \ubb38\uc5d0\uc11c \uc9c0\uc815\ud558\uace0 \uc788\ub294\ub370\uc694. i \ubcc0\uc218\ub97c 1..n\uae4c\uc9c0, \uc989 \ub9cc\uc57d n \uc778\uc790\uac12\uc744 10\uc774\ub77c\uace0 \ud55c\ub2e4\uba74 i \ubcc0\uc218\ub294 \ucd1d 10\ubc88 \ubc18\ubcf5\ub418\uba70 \uac01 \ubc18\ubcf5\uc5d0\uc11c i\uc758 \uac12\uc740 1\uc529 \uc99d\uac00\ub418\uc5b4 \uac01\uac01 1, 2, 3, 4, 5, 6, 7, 8, 9, 10\uc774 \ub429\ub2c8\ub2e4. \uc544\ub798\uc758 \uc2e4\ud589\uc744 \ud1b5\ud574 \uc774\ub7ec\ud55c \ub0b4\uc6a9\uc744 \uc0b4\ud3b4\ubcfc \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n
\"\"<\/p>\n
\ub9cc\uc57d FOR \ubb38\uc758 \ubc18\ubcf5 \uc870\uac74\uc774 \ud070 \uac12\uc5d0\uc11c \uc791\uc740 \uac12\uc73c\ub85c \uc9c4\ud589\ub41c\ub2e4\uba74 \ub2e4\uc74c\ucc98\ub7fc FOR \ubb38\uc5d0 REVERSE\ub97c \ubd99\uc5ec\uc918\uc57c \ud569\ub2c8\ub2e4.<\/p>\n
\r\nCREATE OR REPLACE FUNCTION adder(n INTEGER)\r\nRETURNS INTEGER AS $$\r\nDECLARE\r\n    result INTEGER := 0;\r\nBEGIN\r\n    FOR i IN REVERSE n..1 LOOP\r\n        RAISE NOTICE 'Iterator: %', i;\r\n        result := result + i;\r\n    END LOOP;\r\n\r\n    RETURN result;\r\nEND;\r\n$$ LANGUAGE plpgsql;<\/pre>\n
\uc2e4\ud589\ud574 \ubcf4\uba74 \uc544\ub798\uc640 \uac19\uc740 \uacb0\uacfc\ub97c \ubcfc \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n
\"\"<\/p>\n
\uc704\uc758 \uacb0\uacfc\ub97c \ubcf4\uba74, i \uac12\uc5d0 \ub300\ud55c Iteration\uc774 10\ubd80\ud130 1\uae4c\uc9c0 \uac10\uc18c\ud558\uba74\uc11c \ubc18\ubcf5\ub418\ub294 \uac83\uc744 \ubcfc \uc218 \uc788\uc2b5\ub2c8\ub2e4. <\/p>\n
\uc774\ucc98\ub7fc FOR \ubb38\uc758 \uc99d\uac10\uac12\uc740 1\uc778\ub370\uc694. \uc774 \uac12\uc740 \ubcc0\uacbd\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4. \ub2e4\uc74c \ucf54\ub4dc\ub294 \uc99d\uac00\uac12\uc744 2\ub85c \uc9c0\uc815\ud558\uace0 \uc788\ub294 FOR \ubb38\uc785\ub2c8\ub2e4.<\/p>\n
\r\nCREATE OR REPLACE FUNCTION adder(n INTEGER)\r\nRETURNS INTEGER AS $$\r\nDECLARE\r\n    result INTEGER := 0;\r\nBEGIN\r\n    FOR i IN 1..n BY 2 LOOP\r\n        RAISE NOTICE 'Iterator: %', i;\r\n        result := result + i;\r\n    END LOOP;\r\n\r\n    RETURN result;\r\nEND;\r\n$$ LANGUAGE plpgsql;<\/pre>\n
\uc2e4\ud589\ud574 \ubcf4\uba74, i \uac12\uc758 \ubc18\ubcf5\uc774 1\ubd80\ud130 \uc2dc\uc791\ud574\uc11c 2\uc529 \uc99d\uac00\ud568\uc73c\ub85c\uc368 3, 5, 7, 9\uac00 \ub418\uba70, \uc544\ub798\uc758 \uc2e4\ud589 \uacb0\uacfc\ub97c \ud1b5\ud574 \ud655\uc778\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n
\"\"<\/p>\n
PG\/pgSQL\uc5d0\uc11c \ubc18\ubcf5\ubb38\uc758 \ubc31\ubbf8\ub294 \ub370\uc774\ud0c0\ubca0\uc774\uc2a4\uc758 \ud14c\uc774\ube14\uc5d0 \ub300\ud55c \ucffc\ub9ac\uc5d0 \ub300\ud55c \ubc18\ubcf5\uc785\ub2c8\ub2e4. <\/p>\n
\r\nCREATE OR REPLACE FUNCTION avg_ages()\r\nRETURNS NUMERIC AS $$\r\nDECLARE\r\n    r RECORD;\r\n    total NUMERIC := 0;\r\n    count INTEGER := 0;\r\nBEGIN\r\n    FOR r IN SELECT age FROM person\r\n    LOOP\r\n        total := total + r.age;\r\n        count := count + 1;\r\n    END LOOP;\r\n\r\n    RETURN total \/ count;\r\nEND;\r\n$$ LANGUAGE plpgsql;<\/pre>\n
SELECT \ubb38\uc744 \ud1b5\ud55c \uacb0\uacfc\uc14b\uc758 \ubc18\ubcf5\uc744 \uc704\ud574 8\ubc88 \ucf54\ub4dc\uc758 FOR \ubb38\uc5d0\uc11c RECORD \ud0c0\uc785\uc758 r \ubcc0\uc218\uc640 \ucffc\ub9ac\ubb38\uc774 \ud544\uc694\ud569\ub2c8\ub2e4. \ubc18\ubcf5\ubb38 \uc548\uc5d0\uc11c \ucffc\ub9ac \uacb0\uacfc\uc5d0 \ub300\ud55c Row\uc758 \ud544\ub4dc\uac12 \uc811\uadfc\uc744 \uc704\ud574 {RECORD Type \uac1d\uccb4}.{\ud544\ub4dc\uba85}\uacfc \uac19\uc774 \uc811\uadfc\ud560 \uc218 \uc788\ub294\ub370\uc694. \uc704\uc758 \ucf54\ub4dc\uc5d0\uc11c\ub294 r.age\uc640 \uac19\uc774 \uc811\uadfc\ud558\uace0 \uc788\uc2b5\ub2c8\ub2e4. \uc2e4\ud589 \uacb0\uacfc\ub294 \uc544\ub798\uc640 \uac19\uc2b5\ub2c8\ub2e4.<\/p>\n
\"\"<\/p>\n
SELECT \ubb38\uc744 \ub3d9\uc801 \ucffc\ub9ac\ub85c \uc2e4\ud589\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4. \uc608\ub97c \ub4e4\uc5b4 \uc544\ub798\uc758 \ucf54\ub4dc\ub97c \uc0b4\ud3b4\ubcf4\uba74..<\/p>\n
\r\nCREATE OR REPLACE FUNCTION avg_ages(n INTEGER)\r\nRETURNS NUMERIC AS $$\r\nDECLARE\r\n    r RECORD;\r\n    total NUMERIC := 0;\r\n    query TEXT;\r\nBEGIN\r\n    query := 'SELECT age FROM person LIMIT $1';\r\n    FOR r IN EXECUTE query USING n\r\n    LOOP\r\n        total := total + r.age;\r\n    END LOOP;\r\n\r\n    RETURN total \/ n;\r\nEND;\r\n$$ LANGUAGE plpgsql;<\/pre>\n
\ucffc\ub9ac\ubb38\uc5d0 \ud574\ub2f9\ud558\ub294 \ubb38\uc790\uc5f4\uc5d0 \ub300\ud55c \ubcc0\uc218\uac00 8\ubc88 \ucf54\ub4dc\uc785\ub2c8\ub2e4. \uc989, $1\uc774 \ub3d9\uc801\uc73c\ub85c \ubcc0\ud558\ub294 \ubd80\ubd84\uc778\ub370\uc694. \ucffc\ub9ac\ubb38 \uc2e4\ud589 \ud6c4 Row\uc758 \uac1c\uc218\ub97c \uc81c\ud55c\ud558\ub824\uace0 \ud558\ub294 \uac83\uc785\ub2c8\ub2e4. \uc774\ub7ec\ud55c \ub3d9\uc801 \ucffc\ub9ac\ub97c FOR \ubb38\uc5d0\uc11c \uc2e4\ud589\ud558\ub294 \uac83\uc740 9\ubc88 \ucf54\ub4dc\uc785\ub2c8\ub2e4. %1\uc5d0 \ud574\ub2f9\ud558\ub294 \uac12\uc740 FOR \ubb38\uc5d0\uc11c USING \ubb38\uc744 \uc0ac\uc6a9\ud574 \uc9c0\uc815\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n","protected":false},"excerpt":{"rendered":"
\uc548\ub155\ud558\uc138\uc694, GIS Developer \uae40\ud615\uc900\uc785\ub2c8\ub2e4. \uc774\ubc88 \uae00\uc5d0\uc11c\ub294 PL\/pgSQL\uc5d0\uc11c \ubc18\ubcf5\ubb38\uc5d0 \ub300\ud574 \uc0b4\ud3b4\ubcf4\uaca0\uc2b5\ub2c8\ub2e4. PL\/pgSQL\uc5d0\uc11c \uc81c\uacf5\ud558\ub294 \ubc18\ubcf5\ubb38\uc740 LOOP, WHILE, FOR \ubb38\uc774 \uc788\ub294\ub370\uc694. \ud558\ub098\uc529 \uc0b4\ud3b4\ubcf4\ub3c4\ub85d \ud558\uaca0\uc2b5\ub2c8\ub2e4. \uba3c\uc800 \uc608\uc81c \ucf54\ub4dc\ub97c \ud1b5\ud574 LOOP \ubb38\uc744 \uc0b4\ud3b4 \ubcf4\ub3c4\ub85d \ud558\uaca0\uc2b5\ub2c8\ub2e4. CREATE OR REPLACE FUNCTION adder(n INTEGER) RETURNS INTEGER AS $$ DECLARE res INTEGER := 0; i INTEGER; BEGIN i := 1; LOOP res := res … <\/p>\n
\ub354 \ubcf4\uae30 “PostgreSQL\uc758 PL\/pgSQL \ud29c\ud1a0\ub9ac\uc5bc \u2013 6 : \ubc18\ubcf5\ubb38”<\/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-4621","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\/4621","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=4621"}],"version-history":[{"count":13,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=\/wp\/v2\/posts\/4621\/revisions"}],"predecessor-version":[{"id":9690,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=\/wp\/v2\/posts\/4621\/revisions\/9690"}],"wp:attachment":[{"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4621"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4621"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4621"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}