CODEWITHSUNDEEP
phppostgresqlpdo
MrGlass
MrGlass

Reputation: 9252

Reusing variables in PGSQL queries

I have a query, which I have simplified a little to:

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;

This works, but I have to put in that id value (1337) several times. This isn't so bad in my abstract example, but gets really ugly with my more complex real world query & since I'm using PHP/PDO I have to pass in the same variable multiple times.

What I'm looking for is some trick to declare my variable once and then reuse it, like:

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;

But obviously that doesnt work.

Is there a way to declare a variable once in a pgsql query and reuse it?

Upvotes: 2

Views: 2047

Answers (2)

Craig Ringer
Craig Ringer

Reputation: 324375

Unfortunately there is not a good way. Clodaldo has shown the only way that's really viable in plain SQL.

The other way, and what I generally do, is wrap it in a trivial SQL function.

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);

Unfortunately PostgreSQL doesn't have TEMPORARY functions, and DO blocks cannot take parameters or return a rowset. So it's not ideal, but it works.

Upvotes: 3

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125214

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)

or do a cross join if the subquery is too ugly

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)

But if you are passing parameters from PHP then I don't see the problem of just placing the parameters holders in the place of 1337

Upvotes: 5

Related Questions