本期内容如下：
系统表间的外键关联
连接符和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 condefFROM 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=# SELECTfktable, fkcols, pktableFROM pg_get_catalog_foreign_keys()
WHERE pktable = 'pg_authid'::regclass;
fktable | fkcols | pktable
-------------------------+--------------+-----------
pg_proc | {proowner} | pg_authid
pg_type | {typowner} | pg_authid
pg_class | {relowner} | pg_authid
pg_operator | {oprowner} | pg_authid
pg_opfamily | {opfowner} | pg_authid
pg_opclass | {opcowner} | pg_authid
pg_language | {lanowner} | pg_authid
pg_largeobject_metadata | {lomowner} | pg_authid
pg_statistic_ext | {stxowner} | pg_authid
pg_event_trigger | {evtowner} | pg_authid
pg_namespace | {nspowner} | pg_authid
pg_conversion | {conowner} | pg_authid
pg_database | {datdba} | pg_authid
pg_db_role_setting | {setrole} | pg_authid
pg_tablespace | {spcowner} | pg_authid
pg_auth_members | {roleid} | pg_authid
pg_auth_members | {member} | pg_authid
pg_auth_members | {grantor} | pg_authid
pg_ts_config | {cfgowner} | pg_authid
pg_ts_dict | {dictowner} | pg_authid
pg_extension | {extowner} | pg_authid
pg_foreign_data_wrapper | {fdwowner} | pg_authid
pg_foreign_server | {srvowner} | pg_authid
pg_user_mapping | {umuser} | pg_authid
pg_policy | {polroles} | pg_authid
pg_default_acl | {defaclrole} | pg_authid
pg_collation | {collowner} | pg_authid
pg_publication | {pubowner} | pg_authid
pg_subscription | {subowner} | pg_authid
(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
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
该问题有两种解法：
查询语句返回的所有字段都显式带别名
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参数。
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)
;
本文结束～
最后推荐周六的济南线下交流活动，欢迎大家来面基！
《快速掌握PostgreSQL版本新特性》
主编
《PostgreSQL 17 RC1新特性体验》
九月是个收获的月份，今年的PG 17恰好是第9个在九月发布的大版本。借此活动，正好兑现各位老铁对新书《快速掌握PostgreSQL版本新特性》的殷殷期盼，保持与原书的相同配方，八大主线与大家共同体验这些飘香的新特性。
本次活动只设现场参会。在每个演讲后，有充分的自由交流/互动答疑时段，是不容错过的面基交流机会。
报名二维码：
参会地点：
济南高新区*齐鲁软件园大厦B座4楼415会议室
往期案例回顾
与我联系
微信公众号：象楚之行
墨天轮：https://www.modb.pro/u/15675
微信：skypkmoon
勤耕细作，用心积微；静待花开，量变质成。
原文作者：象楚之行；如有侵权，请联系管理员删除。
