数据库微观案例第49期

本期内容如下：

• 系统表间的外键关联
• 连接符和IS NOT NULL的优先级
• 函数出参规范命名
• MogDB在B模式下使用自定义变量
• SQL挑战赛第二期PG 16解法

1.如何查询系统表之间的关联信息

普通表的外键关联信息可以通过系统表pg_constraint进行查询，典型查询语句如下：

SELECT conname, conrelid::pg_catalog.regclass AS ontable,
       pg_catalog.pg_get_constraintdef(oid, true) AS condef
  FROM pg_catalog.pg_constraint c
 WHERE confrelid = 'a'::pg_catalog.regclass
       AND contype = 'f'
ORDER BY conname;
复制

但系统表之间的关联并没有使用显式的主外键关联，需要使用系统函数pg_get_catalog_foreign_keys()进行获取：

SELECT * 
 FROM pg_get_catalog_foreign_keys()
WHERE pktable = 'pg_authid'::regclass;
复制

例如，PG内部使用比较频繁的pg_authid系统表，它与哪些系统表之间有关联关系呢？

postgres=# SELECT * 
 FROM pg_get_catalog_foreign_keys()
WHERE pktable = 'pg_authid'::regclass;
┌─────────────────────────┬──────────────┬───────────┬────────┬──────────┬────────┐
│         fktable         │    fkcols    │  pktable  │ pkcols │ is_array │ is_opt │
├─────────────────────────┼──────────────┼───────────┼────────┼──────────┼────────┤
│ pg_proc                 │ {proowner}   │ pg_authid │ {oid}  │ f        │ f      │
│ pg_type                 │ {typowner}   │ pg_authid │ {oid}  │ f        │ f      │
│ pg_class                │ {relowner}   │ pg_authid │ {oid}  │ f        │ f      │
│ pg_operator             │ {oprowner}   │ pg_authid │ {oid}  │ f        │ f      │
│ pg_opfamily             │ {opfowner}   │ pg_authid │ {oid}  │ f        │ f      │
│ pg_opclass              │ {opcowner}   │ pg_authid │ {oid}  │ f        │ f      │
│ pg_language             │ {lanowner}   │ pg_authid │ {oid}  │ f        │ f      │
│ pg_largeobject_metadata │ {lomowner}   │ pg_authid │ {oid}  │ f        │ f      │
│ pg_statistic_ext        │ {stxowner}   │ pg_authid │ {oid}  │ f        │ f      │
│ pg_event_trigger        │ {evtowner}   │ pg_authid │ {oid}  │ f        │ f      │
│ pg_namespace            │ {nspowner}   │ pg_authid │ {oid}  │ f        │ f      │
│ pg_conversion           │ {conowner}   │ pg_authid │ {oid}  │ f        │ f      │
│ pg_database             │ {datdba}     │ pg_authid │ {oid}  │ f        │ f      │
│ pg_db_role_setting      │ {setrole}    │ pg_authid │ {oid}  │ f        │ t      │
│ pg_tablespace           │ {spcowner}   │ pg_authid │ {oid}  │ f        │ f      │
│ pg_auth_members         │ {roleid}     │ pg_authid │ {oid}  │ f        │ f      │
│ pg_auth_members         │ {member}     │ pg_authid │ {oid}  │ f        │ f      │
│ pg_auth_members         │ {grantor}    │ pg_authid │ {oid}  │ f        │ f      │
│ pg_ts_config            │ {cfgowner}   │ pg_authid │ {oid}  │ f        │ f      │
│ pg_ts_dict              │ {dictowner}  │ pg_authid │ {oid}  │ f        │ f      │
│ pg_extension            │ {extowner}   │ pg_authid │ {oid}  │ f        │ f      │
│ pg_foreign_data_wrapper │ {fdwowner}   │ pg_authid │ {oid}  │ f        │ f      │
│ pg_foreign_server       │ {srvowner}   │ pg_authid │ {oid}  │ f        │ f      │
│ pg_user_mapping         │ {umuser}     │ pg_authid │ {oid}  │ f        │ t      │
│ pg_policy               │ {polroles}   │ pg_authid │ {oid}  │ t        │ t      │
│ pg_default_acl          │ {defaclrole} │ pg_authid │ {oid}  │ f        │ f      │
│ pg_collation            │ {collowner}  │ pg_authid │ {oid}  │ f        │ f      │
│ pg_publication          │ {pubowner}   │ pg_authid │ {oid}  │ f        │ f      │
│ pg_subscription         │ {subowner}   │ pg_authid │ {oid}  │ f        │ f      │
└─────────────────────────┴──────────────┴───────────┴────────┴──────────┴────────┘
(29 rows)
复制

2.连接符||和IS NOT NULL的优先级

查询语句如下：

SELECT case when t.a||'' is not null then 1 else 2 end res 
 FROM (SELECT 'a'::varchar AS a ) t;
复制

• PostgreSQL 9.5之前，语句执行的逻辑是t.a||(’’ is not null)
• PostgreSQL 9.5开始，语句执行的逻辑是(t.a||’’) is not null

PostgreSQL 9.5以上的版本，上面的语句可以正常执行，9.5之前会报错如下：

ERROR:  argument of CASE/WHEN must be type boolean, not type text
LINE 1: SELECT case when t.a||'' is not null then 1 else 2 end res 
                         ^
复制

3.函数出参规范命名问题

相关表结构如下：

drop table if exists t1,t2;
create table t1(id int,type varchar);
create table t2(id int,flag varchar);
insert into t1 values(1,'pg');
insert into t2 values(1,'abc');
复制

下面的查询语句字段的引用比较清晰，type和flag不存在重名需要加别名的因素。

select a.id,type,flag
 from t1 a inner join t2 b using(id);
复制

但如果查询语句通过函数返回时，函数的出参名称可能会影响字段是否需要加别名。

例如下面函数的出参名称直接使用查询语句返回的字段名：

create or replace function f_get_list()
returns table(id int,type varchar,flag varchar)
as $$
begin
  return query select a.id,type,flag
                 from t1 a 
              inner join t2 b using(id);
end;
$$ LANGUAGE plpgsql;
复制

此时查询函数会报错

select * from f_get_list();
复制

错误如下：

ERROR:  column reference "type" is ambiguous
LINE 1: select a.id,type,flag
复制

该问题有两种解法：

• 查询语句返回的所有字段都显式带别名

  return query select a.id,a.type,b.flag
                 from t1 a 
              inner join t2 b using(id);
复制

• 函数return参数使用新名称，例如统一加前缀

returns table(v_id int,v_type varchar,v_flag varchar)
复制

无论哪种处理习惯都可避免这个错误。

4.MogDB在MySQL兼容性模式下如何使用自定义用户变量

MySQL兼容性下，使用虚拟行号，类似Oracle的rownum，示例如下

SET @rownum := 0;
SELECT id, @rownum := @rownum + 1 AS rn FROM (values('a'),('b'),('c')) t(id) ;
复制

使用@设置自定义变量，需要先开启enable_set_variable_b_format参数为on

SET enable_set_variable_b_format TO on;
复制

5.SQL挑战赛第二期(墨天轮)

参考链接：https://www.modb.pro/db/1826549073320697856

尝试使用PG 16的any_value特性

WITH cte_group_flag(seqno,amount,group_flag) AS 
(
  SELECT seqno,amount,count(amount) over (order by seqno) group_flag 
    FROM test_gen
),cte_group_amount(group_flag,amount_all) AS
(
  SELECT group_flag,any_value(amount) amount_all 
    FROM cte_group_flag 
  GROUP BY group_flag 
)
SELECT seqno,amount,amount_all
  FROM cte_group_flag
LEFT JOIN cte_group_amount USING (group_flag)
;
复制

本文结束～

往期回顾

• 数据库微观案例第48期
• 数据库微观案例第47期
• 数据库微观案例第46期
• 数据库微观案例第45期
• 数据库微观案例第44期
• 数据库微观案例第43期
• 数据库微观案例第42期
• 数据库微观案例第41期 ｜NULL值案例
• 数据库微观案例第40期
• PostgreSQL智慧碎片｜微观案例 ｜宏观收获
• PostgreSQL小案例集｜4月刊

与我联系

• 微信公众号：象楚之行
• 墨天轮：https://www.modb.pro/u/15675
• 微信：skypkmoon

勤耕细作，用心积微；静待花开，量变质成。