如何创建PostgreSQL存储过程对比两条SQL行数并返回指定结果

前置准备（跨库访问配置） #

因为两条SQL分别位于两个不同的PostgreSQL实例，需要先在1号数据库中配置postgres_fdw扩展，映射2号库的payment表到1号库，方便存储过程直接查询：

• 安装扩展

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

• 创建2号库的服务器连接

CREATE SERVER IF NOT EXISTS db2_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '2号库服务器地址', port '5432', dbname '2号库库名');

• 创建用户映射

CREATE USER MAPPING IF NOT EXISTS FOR 当前1号库操作用户名
SERVER db2_server
OPTIONS (user '2号库登录用户名', password '2号库登录密码');

• 映射2号库payment表到1号库

CREATE FOREIGN TABLE IF NOT EXISTS payment_db2 (
  created_dt timestamp,
  amount numeric,
  status_id varchar,
  service_id int,
  agent_id int,
  external_id varchar
)
SERVER db2_server
OPTIONS (schema_name 'public', table_name 'payment');

存储过程编写 #

存储过程会返回SQL1的所有字段，额外增加1个校验结果标记字段，true代表两行数一致可发报表，false代表行数不一致不可发：

CREATE OR REPLACE FUNCTION check_payment_report()
RETURNS TABLE (
  id int,
  "Date" varchar,
  "Time" time,
  "Initiator" varchar,
  "Service" varchar,
  "Amount" numeric,
  "Status" varchar,
  "Props" varchar,
  "Identifier" varchar,
  "External status" varchar,
  "校验结果" boolean
) AS $$
DECLARE
  count_sql1 int;
  count_sql2 int;
  match_flag boolean;
BEGIN
  -- 统计1号库SQL1返回行数
  SELECT COUNT(*) INTO count_sql1
  FROM payments AS pp
  INNER JOIN auth_user AS au ON au.id = pp.creator_id
  INNER JOIN services AS ss ON ss.id = pp.service_id
  WHERE pp.created_dt::date = (CURRENT_DATE - INTERVAL '1' day)::date
    AND ss.name = 'SomeName' AND pp.status = 'SUCCESS';

  -- 统计2号库SQL2返回行数（查询映射好的外部表）
  SELECT COUNT(*) INTO count_sql2
  FROM payment_db2 AS pp
  WHERE pp.service_id = 1
    AND pp.created_dt::date = (CURRENT_DATE - INTERVAL '1' day)::date
    AND pp.status_id = 'SUCCESS';

  -- 对比行数设置校验标记
  match_flag := (count_sql1 = count_sql2);

  -- 返回SQL1全量结果+校验标记
  RETURN QUERY
  SELECT 
    pp.id, 
    TO_CHAR(pp.created_dt::date, 'dd.mm.yyyy') AS "Date", 
    CAST(pp.created_dt AS time(0)) AS "Time", 
    au.username AS "Initiator", 
    ss.name AS "Service", 
    pp.amount AS "Amount", 
    REPLACE(pp.status, 'SUCCESS', 'Success') AS "Status", 
    pp.account AS "Props", 
    pp.external_id AS "Identifier", 
    COALESCE(pp.external_status, 'Indefined') AS "External status",
    match_flag AS "校验结果"
  FROM payments AS pp
  INNER JOIN auth_user AS au ON au.id = pp.creator_id
  INNER JOIN services AS ss ON ss.id = pp.service_id
  WHERE pp.created_dt::date = (CURRENT_DATE - INTERVAL '1' day)::date
    AND ss.name = 'SomeName' AND pp.status = 'SUCCESS';
END;
$$ LANGUAGE plpgsql STABLE;

使用方法 #

直接调用函数即可获取带校验标记的报表数据：

SELECT * FROM check_payment_report();

• 所有行的校验结果字段为true时，说明两条SQL返回行数一致，可正常发送报表
• 所有行的校验结果字段为false时，说明两条SQL返回行数不一致，禁止发送报表

内容的提问来源于stack exchange，提问作者Alexander