PostgreSQL的merge范例

PostgreSQL的merge范例

-- PostgreSQL的merge范例

-- 创建用户
create user jdbc_etl_in with password '1';
-- 授权schema tzq的使用权限
grant usage on schema tzq to jdbc_etl_in;

-- 建表
/*=================================================*/
/* 表名(Table) : tzq_bas_bank_account_pay_t        */
/* 效能(Efficacy) : 接收上游数据，TI表数据merge到T表 */
/* 释义(Definition) : 银行账号支付配置             */
/* 归档人(From) : tangzhiqiang                     */
/* 归档时间(Archived Time) : 20230717-2311         */
/*=================================================*/
create table tzq.tzq_bas_bank_account_pay_t (
	bank_account_pay_id	INT8	NOT NULL,
	bank_account_id	INT8,
	tzq_bank_account	VARCHAR(200),
	bank_account_pay_status	INT8,
	bank_short_name_id	INT8,
	delete_flag	INT8,
	comments	VARCHAR(4000),
	description	VARCHAR(1000),
	created_by	INT8	NOT NULL,
	creation_date	TIMESTAMP	NOT NULL,
	last_updated_by	INT8	NOT NULL,
	last_update_date	TIMESTAMP	NOT NULL,
	CONSTRAINT pk_tzq_bas_bank_account_pay_t PRIMARY KEY(bank_account_pay_id)
);
CREATE UNIQUE INDEX uk_tzq_bas_bank_account_pay_t_1 ON tzq_bas_bank_account_pay_t((CASE delete_flag WHEN 0 THEN tzq_bank_account ELSE NULL END));
CREATE INDEX       idx_tzq_bas_bank_account_pay_t_1 on tzq_bas_bank_account_pay_t(delete_flag);
CREATE INDEX       idx_tzq_bas_bank_account_pay_t_2 on tzq_bas_bank_account_pay_t(tzq_bank_account);
CREATE INDEX       idx_tzq_bas_bank_account_pay_t_3 on tzq_bas_bank_account_pay_t(tzq_bank_account,delete_flag);
COMMENT ON TABLE   tzq.tzq_bas_bank_account_pay_t IS '银行账号支付配置。';
COMMENT ON COLUMN  tzq.tzq_bas_bank_account_pay_t.bank_account_pay_id IS 'IT主键，序列号。';
COMMENT ON COLUMN  tzq.tzq_bas_bank_account_pay_t.bank_account_id IS '银行账号ID';
COMMENT ON COLUMN  tzq.tzq_bas_bank_account_pay_t.tzq_bank_account IS '在tzq系统使用支付的账号';
COMMENT ON COLUMN  tzq.tzq_bas_bank_account_pay_t.bank_account_pay_status IS '是否有效状态 0：有效；1：无效';
COMMENT ON COLUMN  tzq.tzq_bas_bank_account_pay_t.bank_short_name_id IS 'tzq_bas_bank_short_name_t来源于账户对应的bank short name';
COMMENT ON COLUMN  tzq.tzq_bas_bank_account_pay_t.delete_flag IS '删除标识 0：有效；1：无效';
COMMENT ON COLUMN  tzq.tzq_bas_bank_account_pay_t.comments IS '其他信息说明';
COMMENT ON COLUMN  tzq.tzq_bas_bank_account_pay_t.description IS '对本条记录的说明';
COMMENT ON COLUMN  tzq.tzq_bas_bank_account_pay_t.created_by IS '创建人';
COMMENT ON COLUMN  tzq.tzq_bas_bank_account_pay_t.creation_date IS '创建时间';
COMMENT ON COLUMN  tzq.tzq_bas_bank_account_pay_t.last_updated_by IS '最后修改人';
COMMENT ON COLUMN  tzq.tzq_bas_bank_account_pay_t.last_update_date IS '最后修改时间';
CREATE  SEQUENCE   seq_tzq_bas_bank_account_pay_t;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE tzq_bas_bank_account_pay_t TO jdbc_etl_in;

/*=================================================*/
/* 表名(Table) : tzq_bas_bank_account_pay_t        */
/* 效能(Efficacy) : 接收上游数据，TI表数据merge到T表 */
/* 释义(Definition) : TI表。银行账号支付配置       */
/* 归档人(From) : tangzhiqiang                     */
/* 归档时间(Archived Time) : 20230717-2311         */
/*=================================================*/
create table tzq.tzq_bas_bank_account_pay_ti (
	bank_account_pay_id	INT8	NOT NULL,
	bank_account_id	INT8,
	tzq_bank_account	VARCHAR(200),
	bank_account_pay_status	INT8,
	bank_short_name_id	INT8,
	delete_flag	INT8,
	comments	VARCHAR(4000),
	description	VARCHAR(1000),
	created_by	INT8	NOT NULL,
	creation_date	TIMESTAMP	NOT NULL,
	last_updated_by	INT8	NOT NULL,
	last_update_date	TIMESTAMP	NOT NULL,
	process_date	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP,
	process_status	INT8	DEFAULT 2,
	process_error_message	VARCHAR(500)
)PARTITION BY list(process_status);
CREATE TABLE tzq_bas_bank_account_pay_ti_p2 PARTITION OF tzq_bas_bank_account_pay_ti FOR VALUES IN (2);
CREATE TABLE tzq_bas_bank_account_pay_ti_p4 PARTITION OF tzq_bas_bank_account_pay_ti FOR VALUES IN (4);
CREATE TABLE tzq_bas_bank_account_pay_ti_p5 PARTITION OF tzq_bas_bank_account_pay_ti FOR VALUES IN (5);
GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON TABLE tzq_bas_bank_account_pay_ti TO jdbc_etl_in;

-- PostgreSQL的merge脚本，可设置成定时任务来执行，比如15分钟执行一次
DO $$
DECLARE
  cur_interface record;
  v_isok BOOLEAN;
BEGIN
  FOR cur_interface IN (
    SELECT ctid AS row_id
          ,bank_account_pay_id
          ,bank_account_id
          ,tzq_bank_account
          ,bank_account_pay_status
          ,bank_short_name_id
          ,delete_flag
          ,comments
          ,description
          ,created_by
          ,creation_date
          ,last_updated_by
          ,last_update_date
      FROM tzq.tzq_bas_bank_account_pay_ti
     WHERE process_status = 2
     ORDER BY last_update_date ASC
  ) LOOP
    BEGIN
      v_isok := TRUE;
      INSERT INTO tzq.tzq_bas_bank_account_pay_t
            (bank_account_pay_id
            ,bank_account_id
            ,tzq_bank_account
            ,bank_account_pay_status
            ,bank_short_name_id
            ,delete_flag
            ,comments
            ,description
            ,created_by
            ,creation_date
            ,last_updated_by
            ,last_update_date)
      SELECT cur_interface.bank_account_pay_id
            ,cur_interface.bank_account_id
            ,cur_interface.tzq_bank_account
            ,cur_interface.bank_account_pay_status
            ,cur_interface.bank_short_name_id
            ,cur_interface.delete_flag
            ,cur_interface.comments
            ,cur_interface.description
            ,cur_interface.created_by
            ,cur_interface.creation_date
            ,cur_interface.last_updated_by
            ,cur_interface.last_update_date
      ON CONFLICT ON CONSTRAINT pk_tzq_bas_bank_account_pay_t DO UPDATE
      SET bank_account_id         = EXCLUDED.bank_account_id
         ,tzq_bank_account        = EXCLUDED.tzq_bank_account
         ,bank_account_pay_status = EXCLUDED.bank_account_pay_status
         ,bank_short_name_id      = EXCLUDED.bank_short_name_id
         ,delete_flag             = EXCLUDED.delete_flag
         ,comments                = EXCLUDED.comments
         ,description             = EXCLUDED.description
         ,created_by              = EXCLUDED.created_by
         ,creation_date           = EXCLUDED.creation_date
         ,last_updated_by         = EXCLUDED.last_updated_by
         ,last_update_date        = EXCLUDED.last_update_date;
    /* 
    EXCEPTION
      WHEN OTHERS THEN
        v_isok := FALSE;
        UPDATE tzq.tzq_bas_bank_account_pay_ti ti
           SET process_status        = 4
              ,process_error_message = SUBSTRING(SQLERRM, 1, 500)
         WHERE ti.ctid = cur_interface.row_id;
        COMMIT; 
     */
    END;
    IF (v_isok) THEN
      UPDATE tzq.tzq_bas_bank_account_pay_ti ti
         SET process_status   = 4
       WHERE ti.ctid = cur_interface.row_id;
      COMMIT;
    END IF;
  END LOOP;
END $$;