Greenplum对CTE表达式的支持及如何启用

CTE表达式大家应该都不陌生,很多从Oracle迁移到Greenplum的小伙伴查询如何实现递归的时候,都会查到这个语法。CTE功能在Greenplum 5.x版本被引入,默认在5.x版本中禁用了该功能,这里对版本的配置及如何启用做一下简单记录,方便大家使用。

1.Greenplum不同版本对CTE的支持

  • Greenplum 5.x版本,默认禁用该功能;需要开启参数gp_recursive_cte_prototype才能使用;
  • Greenplum 6.x版本,默认启用该功能,无需额外配置。

2.如何开启CTE支持

在Greenplum 5.x版本中,我们可以通过在psql命令行执行命令查看是否开启对CTE的支持,如下:

postgres=# show gp_recursive_cte_prototype;
 gp_recursive_cte_prototype
----------------------------
 off
(1 row)

off表示为开启,on表示开启。

开启方式分为两种,一种是在session级别临时开启,一种是在数据库级别全局开启。

临时开启方法

例如:

set gp_recursive_cte_prototype to true; -- 部分低版本greenplum必须加
insert into neworg
(
    id, name, pid, path_id, path_name, leve, is_leaf
)
with recursive result_ as -- 递归主体开始
(
    select id -- 首先是顶层节点
    , name
    , pid
    , cast(id as varchar(100)) as path_id -- 保证格式与目标表相同
    , cast(name as varchar(500)) as path_name
    , 1 as leve
    from org 
    where id = '1' -- 指定顶层节点位置
    union all -- 下面是下层节点
    select org.id
    , org.name
    , org.pid
    , cast(r.id || '/' || org.id as varchar(100)) as path_id -- 拼接时加上斜杠
    , cast(r.name || '/' || org.name as varchar(500)) as path_name -- 拼接时加上斜杠
    , r.leve + 1 as leve -- 每递归一次 + 1
    , 0 as is_leaf
    from result_ r -- 注意这里是 result_
    join org on org.pid = r.id -- 指定父子关系,这里注意其实是inner join 
    where 1 = 1 -- 有其他条件可加在这里
)
-- 然后这里可以查询result_了,同时加工is_leaf字段
select t.id, t.name, t.pid, org.name as pname
, '/' || t.path_id as path_id -- 格式化避免顶层缺少斜杠
, '/' || t.path_name as path_name
, t.leve
, case when trim(t.id) in (select distinct a1.pid from org a1 ) then '0' else '1' and as id_leaf -- 判断是否叶子节点,写在此处当表数据量较大时效率较低,可以考虑额外跟新。
from result_ t
left join org on t.pid = org.id -- 再关联一下父级信息

全局开启方法

1)查看是否开启(默认未开启)

$ gpconfig -s gp_recursive_cte_prototype
Values on all segments are consistent
GUC          : gp_recursive_cte_prototype
Master  value: off
Segment value: off

2)执行命令修改配置

$ gpconfig -c gp_recursive_cte_prototype -v on --skipvalidation
20211109:23:22:08:027035 gpconfig:allinone:gpdb-[INFO]:-completed successfully with parameters '-c gp_recursive_cte_prototype -v on --skipvalidation'

3)重载配置并查看是否开启(已经开启)

$ gpstop -u
$ gpconfig -s gp_recursive_cte_prototype
Values on all segments are consistent
GUC          : gp_recursive_cte_prototype
Master  value: on
Segment value: on

如果您觉得有用,麻烦点赞收藏文章,谢谢~

本文从CSDN(点击查看原文)转载而来。不代表烟海拾贝立场,如若转载,请注明出处:https://somirror.com/3646.html

(0)
上一篇 2023-01-13 10:21
下一篇 2023-01-13 16:38

相关推荐

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注