2

I can't get GRANT to take current_database() as argument. These all fail with the same error:

GRANT ALL PRIVILEGES ON DATABASE current_database() to justintestin;
GRANT ALL PRIVILEGES ON DATABASE (current_database()) to justintestin;
GRANT ALL PRIVILEGES ON DATABASE (Select current_database()) to justintestin;
GRANT ALL PRIVILEGES ON DATABASE format('%I', current_database()) to justintestin;

SQL Error [42601]: ERROR: syntax error at or near "("

Currently, I use EXECUTE:

Do 
$$ 
Begin
    Execute Format('GRANT ALL PRIVILEGES ON DATABASE %I TO %I', current_database(), 'justintestin');
End;
$$;

but the nitpicker me knows there's gotta be a simpler way. Running on the official postgres:16 Docker image:

PostgreSQL 16.2 (Debian 16.2-1.pgdg120+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit"*).

1 Answer 1

3

String interpolation is generally not possible for "utility" commands. You must insert values textually even if they are just data values.

Sorry for your inner nitpicker, but it may still be consolation that dynamic SQL with EXECUTE (like you have it) is already the way to go.

See:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.