在PGSQL查询中重用变量

MrGlass 发表于 Dev

格拉斯先生

我有一个查询，将其简化为：

WITH users AS (
    SELECT member_id FROM group_members AS gm 
        JOIN groups AS g on gm.group_id = g.id 
        WHERE g.id = 1337 OR g.parents @> ARRAY[1337]
    )
UPDATE access SET revoked = TRUE 
    WHERE user_id IN (SELECT member_id FROM users)

RETURNING user_id;

这可行，但是我必须多次输入该id值（1337）。在我的抽象示例中，这并不是很糟糕，但是对于更复杂的现实世界查询而言，这确实很丑陋，而且由于我使用的是PHP / PDO，因此必须多次传递相同的变量。

我要寻找的是一些技巧，一次声明我的变量，然后重新使用它，例如：

DECLARE gid = 1337
WITH users AS (
    SELECT member_id FROM group_members AS gm 
        JOIN groups AS g on gm.group_id = g.id 
        WHERE g.id = gid OR g.parents @> ARRAY[gid]
    )
UPDATE access SET revoked = TRUE 
    WHERE user_id IN (SELECT member_id FROM users)

RETURNING user_id;

但是显然那是行不通的。

有没有一种方法可以在pgsql查询中声明一次变量并重用它？

克洛多尔多·内托

with gid as (
    select 1337 as gid
), users as (
    select member_id
    from
        group_members as gm 
        join
        groups as g on gm.group_id = g.id 
    where
        g.id = (select gid from gid) or
        g.parents @> array[(select gid from gid)]
)
update access
set revoked = true 
where user_id in (select member_id from users)

或如果子查询太丑则进行交叉联接

with gid as (
    select 1337 as gid
), users as (
    select member_id
    from
        group_members as gm 
        join
        groups as g on gm.group_id = g.id
        cross join
        gid
    where g.id = gid or g.parents @> array[gid]
)
update access
set revoked = true 
where user_id in (select member_id from users)

但是，如果您要从PHP传递参数，那么我看不到仅将参数持有人放在 1337

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。