GaussDB(DWS)迁移-upsert支持

GaussDB(DWS)从8.1.1版本开始支持UPSERT功能，8.1.1版本之前建议使用如下的UDF实现UPSERT

-- upStr为拼接的update语句
-- inStr为拼接的insert语句
-- 适合update记录数>insert记录数的场景
CREATE OR REPLACE FUNCTION public.do_upsert(upStr text, inStr text)
 RETURNS void
 LANGUAGE plpgsql
 VOLATILE NOT FENCED NOT SHIPPABLE
AS $function$
DECLARE
    val int;
BEGIN
    -- do update first
    EXECUTE IMMEDIATE upStr;
    GET DIAGNOSTICS val = row_count;
    IF val = 0 THEN
        -- if update nothing，just do insert 
        EXECUTE IMMEDIATE inStr;
    END IF;
    -- if other session have inserted an conflicting tuples, ust do update
    EXCEPTION WHEN UNIQUE_VIOLATION THEN
        EXECUTE IMMEDIATE upStr;
END$function$
;

-- 适合insert记录数>update记录数的场景
CREATE OR REPLACE FUNCTION public.do_upsert(upStr text, inStr text)
 RETURNS void
 LANGUAGE plpgsql
 VOLATILE NOT FENCED NOT SHIPPABLE
AS $function$
DECLARE
    val int;
BEGIN
    EXECUTE IMMEDIATE inStr;
    EXCEPTION WHEN UNIQUE_VIOLATION THEN
        EXECUTE IMMEDIATE upStr;
END$function$
;

函数do_upsert_update_prevails测试

postgres=# CREATE OR REPLACE FUNCTION public.do_upsert_update_prevails(upStr text, inStr text)
postgres-#  RETURNS void
postgres-#  LANGUAGE plpgsql
postgres-#  VOLATILE NOT FENCED NOT SHIPPABLE
postgres-# AS $function$
postgres$# DECLARE
postgres$#     val int;
postgres$# BEGIN
postgres$#     -- do update first
postgres$#     EXECUTE IMMEDIATE upStr;
postgres$#     GET DIAGNOSTICS val = row_count;
postgres$#     IF val = 0 THEN
postgres$#         -- if update nothing，just do insert
postgres$#         EXECUTE IMMEDIATE inStr;
postgres$#     END IF;
postgres$#     -- if other session have inserted an conflicting tuples, ust do update
postgres$#     EXCEPTION WHEN UNIQUE_VIOLATION THEN
postgres$#         EXECUTE IMMEDIATE upStr;
postgres$# END$function$
postgres-# ;
CREATE FUNCTION
Time: 17.373 ms
postgres=# DROP TABLE txx;
DROP TABLE
Time: 14.356 ms
postgres=# CREATE TABLE txx(a int, b int, c int)DISTRIBUTE BY HASH(a);
CREATE TABLE
Time: 9.475 ms
postgres=# CREATE UNIQUE INDEX idx ON txx(a, b);
CREATE INDEX
Time: 10.532 ms
postgres=# CALL public.do_upsert_update_prevails('update txx set c = c+1 where a = 1 and b = 2', 'insert into txx values(1, 2, 3)');
 do_upsert_update_prevails
---------------------------

(1 row)

Time: 66.729 ms
postgres=# SELECT * FROM txx;
 a | b | c
---+---+---
 1 | 2 | 3
(1 row)

Time: 9.156 ms
postgres=# CALL public.do_upsert_update_prevails('update txx set c = c+1 where a = 1 and b = 2', 'insert into txx values(1, 2, 3)');
 do_upsert_update_prevails
---------------------------

(1 row)

Time: 18.699 ms
postgres=# SELECT * FROM txx;
 a | b | c
---+---+---
 1 | 2 | 4
(1 row)

Time: 15.189 ms
postgres=#

函数do_upsert_insert_prevails测试

postgres=# CREATE OR REPLACE FUNCTION public.do_upsert_insert_prevails(upStr text, inStr text)
postgres-#  RETURNS void
postgres-#  LANGUAGE plpgsql
postgres-#  VOLATILE NOT FENCED NOT SHIPPABLE
postgres-# AS $function$
postgres$# DECLARE
postgres$#     val int;
postgres$# BEGIN
postgres$#     EXECUTE IMMEDIATE inStr;
postgres$#     EXCEPTION WHEN UNIQUE_VIOLATION THEN
postgres$#         EXECUTE IMMEDIATE upStr;
postgres$# END$function$
postgres-# ;
CREATE FUNCTION
Time: 7.840 ms
postgres=# DROP TABLE txx;
DROP TABLE
Time: 16.716 ms
postgres=# CREATE TABLE txx(a int, b int, c int)DISTRIBUTE BY HASH(a);
CREATE TABLE
Time: 9.756 ms
postgres=# CREATE UNIQUE INDEX idx ON txx(a, b);
CREATE INDEX
Time: 14.154 ms
postgres=# CALL public.do_upsert_insert_prevails('update txx set c = c+1 where a = 1 and b = 2', 'insert into txx values(1, 2, 3)');
 do_upsert_insert_prevails
---------------------------

(1 row)

Time: 5.652 ms
postgres=# SELECT * FROM txx;
 a | b | c
---+---+---
 1 | 2 | 3
(1 row)

Time: 2.726 ms
postgres=# CALL public.do_upsert_insert_prevails('update txx set c = c+1 where a = 1 and b = 2', 'insert into txx values(1, 2, 3)');
 do_upsert_insert_prevails
---------------------------

(1 row)

Time: 9.574 ms
postgres=# SELECT * FROM txx;
 a | b | c
---+---+---
 1 | 2 | 4
(1 row)

Time: 2.269 ms

想了解GuassDB(DWS)更多信息，欢迎微信搜索“GaussDB DWS”关注微信公众号，和您分享最新最全的PB级数仓黑科技，后台还可获取众多学习资料哦~