Блог

Главная - Вопросы по программированию - Повторное использование переменных в запросах PGSQL

Повторное использование переменных в запросах PGSQL

#php #postgresql #pdo

#php #postgresql #pdo

Вопрос:

У меня есть запрос, который я немного упростил, чтобы:

 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 и повторно использовать ее?

Ответ №1:

 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

Комментарии:

1. В PDO вы можете использовать параметры только один раз, поэтому мне приходится передавать 1337 несколько раз. Я не пробовал перекрестное соединение, это может быть достойным методом

Ответ №2:

К сожалению, нет хорошего способа. Клодальдо показал единственный способ, который действительно жизнеспособен в обычном SQL.

Другой способ, и то, что я обычно делаю, — это обернуть его в тривиальную функцию SQL.

 CREATE OR REPLACE FUNCTION do_whatever(gid integer) RETURNS SETOF integer AS $
WITH users AS (
    SELECT member_id FROM group_members AS gm 
        JOIN groups AS g on gm.group_id = g.id 
        WHERE g.id = $1 OR g.parents @> ARRAY[$1]
    )
UPDATE access SET revoked = TRUE 
    WHERE user_id IN (SELECT member_id FROM users)
RETURNING user_id;
$ LANGUAGE sql;

SELECT * FROM do_whatever(1337);

К сожалению, PostgreSQL не имеет TEMPORARY функций, и DO блоки не могут принимать параметры или возвращать набор строк. Так что это не идеально, но это работает.

Метки: