等待PostgreSQL 14 – SEARCH和CYCLE子句

2021-02-06 19:58:00

SEARCH和CYCLE子句这增加了SQL标准功能,该功能将SEARCH和CYCLE子句添加到递归查询中,从而能够产生广度优先或深度优先的搜索顺序并检测周期。这些子句可以使用现有语法重写为查询,这就是thispatch在重写器中所做的。审阅人:Vik Fearing< [email protected]>审阅人:Pavel Stehule< [email protected]>讨论:https://www.postgresql.org/message-id/flat/db80ceee- [email protected]

这意味着要测试它,我们需要一些数据来进行递归查询。这次,我获取了/ etc的目录列表,并将其加载到db中:

$ CREATE TABLE dirs(id INT NOT NULL,parent_id INT参考IDs(id),目录名文本,PRIMARY KEY(id));从' /tmp/dirs.data' COPY 434创建表

$ WITH RECURSIVE c AS(SELECT id,dirname AS来自dirs的路径,where parent_id是NULL UNION ALL SELECT dirs .id,c .path ||' /' || dirs .dirname AS来自dirs的联接路径c ON dirs .parent_id = c .id)选择id,从c ORDER BY路径限制15开始的路径; id |路径---- + ----------------------------- 1 |等42 |等等/ acpi 43 |等等/ acpi / events 44 |等/ alsa 45 |等等/ alsa / conf .d 46 |等/替代47 |等等/ apache2 48 |等/ apache2 / conf-可用49 |等/ apm 50 |等等/ apm / resume .d 51 |等等/ apm / scripts .d 52 |等等/ apm / suspend .d 53 |等等/ apparmor 54 |等等/ apparmor .d 55 |等/ apparmor .d / abstractions(15 ROWS)

$ WITH RECURSIVE c AS(SELECT id,dirname AS来自dirs的路径,where parent_id是NULL UNION ALL SELECT dirs .id,c .path ||' /' || dirs .dirname AS来自dirs的联接路径c ON dirs .parent_id = c .id)通过ID首先搜索深度设置ordercol SELECT id,从c ORDER BY ordercol LIMIT 15开始的路径; id |路径----- + ------------------------------------------- -1 |等2等等/.java 434 |等等/.java /.systemPrefs 3 |等等/ GNUstep 4 |等等/ ImageMagick-6 5 |等等/ Muttrc .d 6 |等等/ NetworkManager 7 |等等/ NetworkManager / conf .d 8 |等等/ NetworkManager / dispatcher .d 9 |等等/ NetworkManager / dispatcher .d / no-wait .d 10 |等等/ NetworkManager / dispatcher .d / pre-down .d 11 |等等/ NetworkManager / dispatcher .d / pre-up .d 12 |等等/ NetworkManager / dnsmasq .d 13 |等等/ NetworkManager / dnsmasq -shared .d 14 |等等/ NetworkManager / system -connections(15 ROWS)

让我们尝试使用这种新方法来获取先前的输出(按字母顺序排列),而且,让我们看看ordercol实际上是什么:

$ WITH RECURSIVE c AS(SELECT id,dirname AS来自dirs的路径,where parent_id是NULL UNION ALL SELECT dirs .id,c .path ||' /' || dirs .dirname AS来自dirs的联接路径c ON dirs .parent_id = c .id)首先按路径设置搜索深度SET ordercol SELECT id,path,ordercol FROM c ORDER BY ordercol LIMIT 15; id |路径ordercol ---- + -------------------------------------------- -+ ----------------------------------------------- -------------------------------------------- 1 |等| {(etc)} 42 |等/ acpi | {(etc),(etc / acpi)} 43 |等/ acpi /事件| {(etc),(etc / acpi),(etc / acpi / events)} 44 |等/ alsa | {(etc),(etc / alsa)} 45 |等等/ alsa / conf .d | {(etc),(etc / alsa),(etc / alsa / conf .d)} 46 |等/替代品| {(etc),(etc / alternatives)} 47 |等/ apache2 | {(etc),(etc / apache2)} 48 |等/ apache2 / conf-可用| {(etc),(etc / apache2),(etc / apache2 / conf -available)} 49 |等/ apm | {(etc),(etc / apm)} 50 |等/ apm / resume .d | {(等),(等/ apm),(等/apm/resume.d)} 51 |等等/ apm / scripts .d | {(等),(等/ apm),(等/ apm / scripts .d)} 52 |等/ apm / suspend .d | {(etc),(etc / apm),(etc / apm / suspend .d)} 53 |等等/ apparmor | {(etc),(etc / apparmor)} 67 |等/ apparmor / init | {(etc),(etc / apparmor),(etc / apparmor / init)} 68 |等/ apparmor / init / network -interface -security | {(etc),(etc / apparmor),(etc / apparmor / init),(etc / apparmor / init / network -interface -security)}(15行)

因此,我现在可以看到,在这种情况下-etc / apparmor / X实际上在/etc/apparmor.d/X之前。哪一种有意义。让我们自己比较两个订单:

$ WITH RECURSIVE c AS(SELECT id,dirname AS path from dirs WHERE id IN(53,54)UNION ALL SELECT dirs .id,c .path ||' /' || dirs .dirname AS路径FROM dirs JOIN c ON dirs .parent_id = c .id)SELECT id,路径FROM c ORDER BY路径; id |路径---- + --------------------------------------------- 53 |装甲54 | apparmor .d 55 | apparmor .d / abstractions 56 | apparmor .d / abstractions / apparmor_api 57 | apparmor .d / abstractions / ubuntu-browsers .d 58 | apparmor .d / disable 59 | apparmor .d / force -complain 60 | apparmor .d / libvirt 61 | apparmor .d / LOCAL 62 | apparmor .d / LOCAL / abstractions 63 | apparmor .d / tunables 64 | apparmor .d / tunables / home .d 65 | apparmor .d / tunables / multiarch .d 66 | apparmor .d / tunables / xdg -user-dirs .d 67 | apparmor / init 68 | apparmor / init / network -interface -security(16 ROWS)

$ WITH RECURSIVE c AS(SELECT id,dirname AS path from dirs WHERE id IN(53,54)UNION ALL SELECT dirs .id,c .path ||' /' || dirs .dirname AS路径FROM dirs JOIN c ON dirs .parent_id = c .id)通过路径设置ordercol SELECT ID首先搜索深度,从c ORDER BY ordercol来查找路径; id |路径---- + --------------------------------------------- 53 |装甲67 | apparmor / init 68 | apparmor / init / network -interface -security 54 | apparmor .d 55 | apparmor .d / abstractions 56 | apparmor .d / abstractions / apparmor_api 57 | apparmor .d / abstractions / ubuntu-browsers .d 58 | apparmor .d / disable 59 | apparmor .d / force -complain 60 | apparmor .d / libvirt 61 | apparmor .d / LOCAL 62 | apparmor .d / LOCAL / abstractions 63 | apparmor .d / tunables 64 | apparmor .d / tunables / home .d 65 | apparmor .d / tunables / multiarch .d 66 | apparmor .d / tunables / xdg -user-dirs .d(16 ROWS)

请注意,现在apparmor / init和apparmor / init / network-interface-security的位置要好得多-在apparmor /之后,而不是apparmor.d之后。真好!

$ WITH RECURSIVE c AS(SELECT id,dirname AS来自dirs的路径,where parent_id是NULL UNION ALL SELECT dirs .id,c .path ||' /' || dirs .dirname AS来自dirs的联接路径c ON dirs .parent_id = c .id)通过ID优先搜索宽度SET ordercol SELECT id,从c ORDER BY ordercol LIMIT 15开始的路径; id |路径---- + --------------------- 1 |等2等等/.java 3 |等等/ GNUstep 4 |等/ ImageMagick-6 5 |等等/ Muttrc .d 6 |等等/ NetworkManager 15 |等/ ODBCDataSources 16 |等等/ OpenCL 18 |等/ PackageKit 19 |等/ UPower 20 |等等/ X11 42 |等等/ acpi 44 |等/ alsa 46 |等/替代47 |等等/ apache2(15 ROWS)

是的-/ etc中有许多目录,因此要更深入地了解目录,我必须抵消很多:

$ WITH RECURSIVE c AS(SELECT id,dirname AS来自dirs的路径,where parent_id是NULL UNION ALL SELECT dirs .id,c .path ||' /' || dirs .dirname AS来自dirs的联接路径c ON dirs .parent_id = c .id)通过ID优先搜索宽度SET ordercol SELECT id,从c ORDER BY ordercol LIMIT 15 offset 170开始的路径; id |路径----- + --------------------------------------- 420 |等/ vulkan 424 |等等/ wpa_supplicant 425 |等等/ xdg 433 |等/ xml 7 |等等/ NetworkManager / conf .d 8 |等等/ NetworkManager / dispatcher .d 12 |等等/ NetworkManager / dnsmasq .d 13 |等/ NetworkManager / dnsmasq -shared .d 14 |等/ NetworkManager /系统-连接17 |等/ OpenCL /供应商21 |等等/ X11 / Xreset .d 22 |等等/ X11 / Xresources 23 |等等/ X11 / Xsession .d 24 |等/ X11 / app-默认值25 |等/ X11 /光标(15行)

Warning: Can only detect less than 5000 characters