CODEWITHSUNDEEP
postgresqlplpgsql
riqitang
riqitang

Reputation: 3371

Postgres: how to declare user type variable?

I want to write a small script to grant permissions. The script works if I type in the user directly into each query but it's more efficient to use a variable but I cannot find what type to declare it as.

DO $$

DECLARE
usr ??? := myuser;

BEGIN

GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA public 
TO usr;

GRANT ALL PRIVILEGES ON SCHEMA public to usr;

END $$

Upvotes: 0

Views: 301

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246413

You need dynamic SQL for that:

DO
$$DECLARE
   usr text := 'myuser' ;
BEGIN
   EXECUTE format('GRANT SELECT, INSERT, UPDATE, DELETE '
                  'ON ALL TABLES IN SCHEMA public ' 
                  'TO %I',
                  usr);
END;$$;

The second statement works similarly.

Upvotes: 1

Related Questions