{"id":1852,"date":"2020-11-12T11:02:23","date_gmt":"2020-11-12T03:02:23","guid":{"rendered":"http:\/\/www.puzi7.com\/?p=1852"},"modified":"2020-11-12T11:02:23","modified_gmt":"2020-11-12T03:02:23","slug":"postgre-%e6%95%b0%e6%8d%ae%e5%ba%93%e4%b9%8b%e5%ad%98%e5%82%a8%e8%bf%87%e7%a8%8b%e7%a4%ba%e4%be%8b","status":"publish","type":"post","link":"https:\/\/www.puzi7.net\/?p=1852","title":{"rendered":"postgre \u6570\u636e\u5e93\u4e4b\u5b58\u50a8\u8fc7\u7a0b\u793a\u4f8b"},"content":{"rendered":"

Postgre\u6570\u636e\u5e93\uff0c\u4e00\u822c\u7684\u6570\u636e\u5e93\u64cd\u4f5c\u8ddf\u5176\u4ed6mysql, oracle \u7684sql\u8bed\u53e5\u7684\u5199\u6cd5\u7c7b\u4f3c\uff0c\u53ea\u662f\u4e2a\u522b\u7684\u51fd\u6570\u548c\u5173\u952e\u5b57\u5199\u6cd5\u4e0d\u540c\u800c\u5df2\uff0c\u8fd9\u91cc\u662f\u6700\u8fd1\u505a\u7684\u5173\u4e8e\u5b58\u50a8\u8fc7\u7a0b\u64cd\u4f5c\u7684\u4e00\u4e2a\u7b80\u5355\u793a\u4f8b\uff0c\u7528\u5230\u4e00\u4e9b\u5e38\u7528\u7684\u6570\u636e\u5e93\u7f16\u7a0b\u77e5\u8bc6\uff0c\u6b64\u6587\u505a\u4e2a\u7b80\u5355\u7684\u4ecb\u7ecd\u3002<\/p>\n

    \n
  1. \u51fd\u6570\u58f0\u660e\n
      \n
    1. function \u505a\u51fd\u6570\u58f0\u660e<\/li>\n
    2. select update_info()\u505a\u51fd\u6570\u8c03\u7528<\/li>\n
    3. drop function update_info() \u5220\u9664\u51fd\u6570<\/li>\n<\/ol>\n<\/li>\n
    4. \u53d8\u91cf\u8d4b\u503c\n
        \n
      1. select xxx into var \/\/\u628a\u67e5\u8be2\u7ed3\u679c\u8d4b\u503c\u7ed9\u53d8\u91cfvar<\/li>\n
      2. declare \u4e2d\u58f0\u660e\u53d8\u91cf<\/li>\n<\/ol>\n<\/li>\n
      3. \u8fd4\u56de\u503c\n
          \n
        1. returns {\u8fd4\u56de\u503c\u7c7b\u578b}<\/li>\n<\/ol>\n<\/li>\n
        2. \u903b\u8f91\u5224\u65ad\n
            \n
          1. if … then … else … end if, \u505a\u903b\u8f91\u5224\u65ad<\/li>\n
          2. \u5224\u65ad\u6761\u4ef6\u8ddf\u4e00\u822c\u7684sql\u5224\u65ad\u4e00\u6837<\/li>\n<\/ol>\n<\/li>\n
          3. \u5f02\u5e38\u5904\u7406\n
              \n
            1. exception \u505a\u5f02\u5e38\u5904\u7406<\/li>\n
            2. raise \u505a\u5f02\u5e38\u629b\u51fa<\/li>\n<\/ol>\n<\/li>\n
            3. \u6570\u636e\u7684\u5904\u7406\n
                \n
              1. \u5728\u903b\u8f91\u5feb\u91cc\u8fb9\u505a\u6570\u636e\u7684CRUD<\/li>\n
              2. uuid \u51fd\u6570\uff0c \u9700\u5b89\u88c5\u6269\u5c55\u00a0 create extension \"uuid-ossp\"<\/code>\n

                select<\/b><\/span> uuid_generate_v4();<\/span><\/p>\n<\/li>\n

              3. \u5f53\u524d\u65f6\u95f4\uff1a\n

                select<\/b> now<\/b><\/span>()::<\/span>timestamp<\/b><\/span>(<\/span>0<\/span>)<\/span>without<\/b> time<\/b><\/span> zone<\/b><\/p>\n<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n

                \u793a\u4f8b\u9700\u6c42<\/strong>\uff1a\u67e5\u8be2info\u8868\u6ee1\u8db3\u6761\u4ef6\u7684\u8bb0\u5f55\uff0c\u904d\u5386\u8fd9\u4e9b\u8bb0\u5f55\uff0c\u5e76\u63d2\u5165area_item\u8bb0\u5f55\uff0c\u628ainfo\u7684\u5b57\u6bb5\u5185\u5bb9\u63d2\u5165\u5230area_item\u91cc\u8fb9\uff0c\u7136\u540e\u628aarea_item.id\u5b58\u5165info\u8868\u4e2d\u3002<\/span><\/p>\n

                \u5b58\u50a8\u8fc7\u7a0b\u793a\u4f8b\u5982\u4e0b<\/strong>\uff1a<\/p>\n

                CREATE<\/b><\/span> OR<\/b><\/span> REPLACE<\/b><\/span> FUNCTION<\/b><\/span> update_info()<\/p>\n

                RETURNS<\/b> varchar<\/b><\/span> as<\/b> $$<\/b><\/span><\/p>\n

                declare<\/b><\/p>\n

                \u00a0 <\/span>V_RECORD info;<\/span><\/p>\n

                \u00a0 <\/span>V_SUCCESS_COUNT int<\/b><\/span>;<\/span><\/p>\n

                \u00a0 <\/span>area_id varchar<\/b><\/span>;<\/span><\/p>\n

                \u00a0 <\/span>result_code varchar<\/b><\/span>;<\/span><\/p>\n

                \u00a0 <\/span>nowDate timestamp<\/b><\/span> without<\/b><\/span> time<\/b><\/span> zone<\/b><\/span>;<\/span><\/p>\n

                begin<\/b><\/p>\n

                V_SUCCESS_COUNT := 0<\/span>;<\/span><\/p>\n

                for<\/b><\/span> V_RECORD in<\/b><\/span> (select<\/b><\/span> * from<\/b><\/span> info where<\/b><\/span> area_item like<\/b><\/span> ‘POLYGON%’<\/span>) loop<\/b><\/span><\/p>\n

                begin<\/b><\/p>\n

                select<\/b><\/span> uuid_generate_v4() into<\/b><\/span> area_id;<\/span><\/p>\n

                V_SUCCESS_COUNT = V_SUCCESS_COUNT + 1<\/span>;<\/span><\/p>\n

                select<\/b><\/span> now<\/b><\/span>()::timestamp<\/b><\/span>(0<\/span>)without<\/b><\/span> time<\/b><\/span> zone<\/b><\/span> into<\/b><\/span> nowDate;<\/span><\/p>\n

                if<\/b><\/span> area_id is<\/b><\/span> not<\/b><\/span> null<\/b><\/span> then<\/b><\/span><\/p>\n

                insert<\/b><\/span> into<\/b><\/span> area_item(id, area<\/b><\/span>, area_type, area_name, description, last_update_date_time, last_updater)<\/p>\n

                values<\/b><\/span>(area_id, V_RECORD.area_location, ‘device’<\/span>, V_RECORD.id, V_RECORD.item_info, nowDate, ‘tester’<\/span>);<\/span><\/p>\n

                update<\/b><\/span> info set<\/b><\/span> area_location = area_id where<\/b><\/span> id = V_RECORD.id;<\/span><\/p>\n

                result_code = area_id;<\/span><\/p>\n

                raise<\/b><\/span> notice<\/b><\/span> ‘update info %’<\/span>, V_RECORD.id;<\/span><\/p>\n

                end<\/b> if<\/b>;<\/span><\/p>\n

                \u00a0\u00a0 <\/span><\/span>EXCEPTION<\/b><\/p>\n

                \u00a0 \u00a0 <\/span><\/span>WHEN<\/b> others<\/b> THEN<\/b> \u00a0 \u00a0<\/span><\/span><\/p>\n

                \u00a0 \u00a0 <\/span>RAISE<\/b><\/span> EXCEPTION<\/b><\/span> ‘(%)’<\/span>, V_SUCCESS_COUNT;<\/span><\/p>\n

                end<\/b>;<\/span><\/p>\n

                \u00a0\u00a0 <\/span><\/span>end<\/b> loop<\/b>;<\/span><\/p>\n

                \u00a0\u00a0 <\/span>return<\/b><\/span> result_code;<\/span><\/p>\n

                end<\/b>;<\/span><\/p>\n

                $$<\/b><\/span> LANGUAGE<\/b><\/span> plpgsql;<\/span><\/p>\n

                <\/code><\/p>\n","protected":false},"excerpt":{"rendered":"

                Postgre\u6570\u636e\u5e93\uff0c\u4e00\u822c\u7684\u6570\u636e\u5e93\u64cd\u4f5c\u8ddf\u5176\u4ed6mysql, oracle \u7684sql\u8bed\u53e5\u7684\u5199\u6cd5…<\/span><\/p>\n

                Read More<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9,11,12],"tags":[104,136],"class_list":["post-1852","post","type-post","status-publish","format-standard","hentry","category-9","category-11","category-blog","tag-postgre","tag-136","zoom-theme-has-thumb"],"_links":{"self":[{"href":"https:\/\/www.puzi7.net\/index.php?rest_route=\/wp\/v2\/posts\/1852","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.puzi7.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.puzi7.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.puzi7.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.puzi7.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1852"}],"version-history":[{"count":0,"href":"https:\/\/www.puzi7.net\/index.php?rest_route=\/wp\/v2\/posts\/1852\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.puzi7.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1852"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.puzi7.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1852"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.puzi7.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1852"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}