{"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"],"yoast_head":"\npostgre \u6570\u636e\u5e93\u4e4b\u5b58\u50a8\u8fc7\u7a0b\u793a\u4f8b - \u8c31\u5b50\u68cb - Puzi7<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.puzi7.net\/?p=1852\" \/>\n<meta property=\"og:locale\" content=\"zh_CN\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"postgre \u6570\u636e\u5e93\u4e4b\u5b58\u50a8\u8fc7\u7a0b\u793a\u4f8b - \u8c31\u5b50\u68cb - Puzi7\" \/>\n<meta property=\"og:description\" content=\"Postgre\u6570\u636e\u5e93\uff0c\u4e00\u822c\u7684\u6570\u636e\u5e93\u64cd\u4f5c\u8ddf\u5176\u4ed6mysql, oracle \u7684sql\u8bed\u53e5\u7684\u5199\u6cd5…Read More\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.puzi7.net\/?p=1852\" \/>\n<meta property=\"og:site_name\" content=\"\u8c31\u5b50\u68cb - Puzi7\" \/>\n<meta property=\"article:published_time\" content=\"2020-11-12T03:02:23+00:00\" \/>\n<meta name=\"author\" content=\"\u77f3\u8863\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"\u4f5c\u8005\" \/>\n\t<meta name=\"twitter:data1\" content=\"\u77f3\u8863\" \/>\n\t<meta name=\"twitter:label2\" content=\"\u9884\u8ba1\u9605\u8bfb\u65f6\u95f4\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 \u5206\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.puzi7.net\\\/?p=1852#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.puzi7.net\\\/?p=1852\"},\"author\":{\"name\":\"\u77f3\u8863\",\"@id\":\"https:\\\/\\\/www.puzi7.net\\\/#\\\/schema\\\/person\\\/87dd4941c8ec260c1350b7f41980a190\"},\"headline\":\"postgre \u6570\u636e\u5e93\u4e4b\u5b58\u50a8\u8fc7\u7a0b\u793a\u4f8b\",\"datePublished\":\"2020-11-12T03:02:23+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.puzi7.net\\\/?p=1852\"},\"wordCount\":201,\"commentCount\":0,\"keywords\":[\"postgre\",\"\u5b58\u50a8\u8fc7\u7a0b\"],\"articleSection\":[\"\u6280\u672f\",\"\u6570\u636e\u5e93\",\"\u6587\u7ae0\"],\"inLanguage\":\"zh-Hans\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.puzi7.net\\\/?p=1852#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.puzi7.net\\\/?p=1852\",\"url\":\"https:\\\/\\\/www.puzi7.net\\\/?p=1852\",\"name\":\"postgre \u6570\u636e\u5e93\u4e4b\u5b58\u50a8\u8fc7\u7a0b\u793a\u4f8b - \u8c31\u5b50\u68cb - Puzi7\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.puzi7.net\\\/#website\"},\"datePublished\":\"2020-11-12T03:02:23+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.puzi7.net\\\/#\\\/schema\\\/person\\\/87dd4941c8ec260c1350b7f41980a190\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.puzi7.net\\\/?p=1852#breadcrumb\"},\"inLanguage\":\"zh-Hans\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.puzi7.net\\\/?p=1852\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.puzi7.net\\\/?p=1852#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"\u9996\u9875\",\"item\":\"https:\\\/\\\/www.puzi7.net\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"\u6280\u672f\",\"item\":\"https:\\\/\\\/www.puzi7.net\\\/?cat=9\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"\u6570\u636e\u5e93\",\"item\":\"https:\\\/\\\/www.puzi7.net\\\/?cat=11\"},{\"@type\":\"ListItem\",\"position\":4,\"name\":\"postgre \u6570\u636e\u5e93\u4e4b\u5b58\u50a8\u8fc7\u7a0b\u793a\u4f8b\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.puzi7.net\\\/#website\",\"url\":\"https:\\\/\\\/www.puzi7.net\\\/\",\"name\":\"\u8c31\u5b50\u68cb - Puzi7\",\"description\":\"\u5206\u4eab\uff0c\u6210\u957f\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.puzi7.net\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"zh-Hans\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.puzi7.net\\\/#\\\/schema\\\/person\\\/87dd4941c8ec260c1350b7f41980a190\",\"name\":\"\u77f3\u8863\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"zh-Hans\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/c52c3a64b447af78e58cfa1c73ddd53952049afcf934cd702d71eee91f9907bd?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/c52c3a64b447af78e58cfa1c73ddd53952049afcf934cd702d71eee91f9907bd?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/c52c3a64b447af78e58cfa1c73ddd53952049afcf934cd702d71eee91f9907bd?s=96&d=mm&r=g\",\"caption\":\"\u77f3\u8863\"},\"sameAs\":[\"https:\\\/\\\/www.puzi7.net\"],\"url\":\"https:\\\/\\\/www.puzi7.net\\\/?author=1\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"postgre \u6570\u636e\u5e93\u4e4b\u5b58\u50a8\u8fc7\u7a0b\u793a\u4f8b - \u8c31\u5b50\u68cb - Puzi7","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.puzi7.net\/?p=1852","og_locale":"zh_CN","og_type":"article","og_title":"postgre \u6570\u636e\u5e93\u4e4b\u5b58\u50a8\u8fc7\u7a0b\u793a\u4f8b - \u8c31\u5b50\u68cb - Puzi7","og_description":"Postgre\u6570\u636e\u5e93\uff0c\u4e00\u822c\u7684\u6570\u636e\u5e93\u64cd\u4f5c\u8ddf\u5176\u4ed6mysql, oracle \u7684sql\u8bed\u53e5\u7684\u5199\u6cd5…Read More","og_url":"https:\/\/www.puzi7.net\/?p=1852","og_site_name":"\u8c31\u5b50\u68cb - Puzi7","article_published_time":"2020-11-12T03:02:23+00:00","author":"\u77f3\u8863","twitter_card":"summary_large_image","twitter_misc":{"\u4f5c\u8005":"\u77f3\u8863","\u9884\u8ba1\u9605\u8bfb\u65f6\u95f4":"1 \u5206"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.puzi7.net\/?p=1852#article","isPartOf":{"@id":"https:\/\/www.puzi7.net\/?p=1852"},"author":{"name":"\u77f3\u8863","@id":"https:\/\/www.puzi7.net\/#\/schema\/person\/87dd4941c8ec260c1350b7f41980a190"},"headline":"postgre \u6570\u636e\u5e93\u4e4b\u5b58\u50a8\u8fc7\u7a0b\u793a\u4f8b","datePublished":"2020-11-12T03:02:23+00:00","mainEntityOfPage":{"@id":"https:\/\/www.puzi7.net\/?p=1852"},"wordCount":201,"commentCount":0,"keywords":["postgre","\u5b58\u50a8\u8fc7\u7a0b"],"articleSection":["\u6280\u672f","\u6570\u636e\u5e93","\u6587\u7ae0"],"inLanguage":"zh-Hans","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.puzi7.net\/?p=1852#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.puzi7.net\/?p=1852","url":"https:\/\/www.puzi7.net\/?p=1852","name":"postgre \u6570\u636e\u5e93\u4e4b\u5b58\u50a8\u8fc7\u7a0b\u793a\u4f8b - \u8c31\u5b50\u68cb - Puzi7","isPartOf":{"@id":"https:\/\/www.puzi7.net\/#website"},"datePublished":"2020-11-12T03:02:23+00:00","author":{"@id":"https:\/\/www.puzi7.net\/#\/schema\/person\/87dd4941c8ec260c1350b7f41980a190"},"breadcrumb":{"@id":"https:\/\/www.puzi7.net\/?p=1852#breadcrumb"},"inLanguage":"zh-Hans","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.puzi7.net\/?p=1852"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.puzi7.net\/?p=1852#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"\u9996\u9875","item":"https:\/\/www.puzi7.net\/"},{"@type":"ListItem","position":2,"name":"\u6280\u672f","item":"https:\/\/www.puzi7.net\/?cat=9"},{"@type":"ListItem","position":3,"name":"\u6570\u636e\u5e93","item":"https:\/\/www.puzi7.net\/?cat=11"},{"@type":"ListItem","position":4,"name":"postgre \u6570\u636e\u5e93\u4e4b\u5b58\u50a8\u8fc7\u7a0b\u793a\u4f8b"}]},{"@type":"WebSite","@id":"https:\/\/www.puzi7.net\/#website","url":"https:\/\/www.puzi7.net\/","name":"\u8c31\u5b50\u68cb - Puzi7","description":"\u5206\u4eab\uff0c\u6210\u957f","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.puzi7.net\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"zh-Hans"},{"@type":"Person","@id":"https:\/\/www.puzi7.net\/#\/schema\/person\/87dd4941c8ec260c1350b7f41980a190","name":"\u77f3\u8863","image":{"@type":"ImageObject","inLanguage":"zh-Hans","@id":"https:\/\/secure.gravatar.com\/avatar\/c52c3a64b447af78e58cfa1c73ddd53952049afcf934cd702d71eee91f9907bd?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/c52c3a64b447af78e58cfa1c73ddd53952049afcf934cd702d71eee91f9907bd?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c52c3a64b447af78e58cfa1c73ddd53952049afcf934cd702d71eee91f9907bd?s=96&d=mm&r=g","caption":"\u77f3\u8863"},"sameAs":["https:\/\/www.puzi7.net"],"url":"https:\/\/www.puzi7.net\/?author=1"}]}},"_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}]}}