How to \set and use variables within a block structure of an included script

Question

I am using psql tool on Postgres Version 11 and I am trying to use a variable defined in a „main“ script in another script that I am including. Including the 01_doesWork.sql script works fine, but I cannot get script 02_doesNotWork.sql running.

How do I need to define my variables in 00_main.sql and how to need to refer to them in the 02 script?

00_main.sql:


\set roleName 'my_role'

\set dataBase 'my_db'

 

\i 01_doesWork.sql

\i 02_doesNotWork.sql

01_doesWork.sql:


GRANT CONNECT ON DATABASE :dataBase TO :roleName;

02_doesNotWork.sql:


DO 

$$BEGIN

IF EXISTS (SELECT FROM pg_roles WHERE rolname = :roleName) THEN

    EXECUTE format('REVOKE CONNECT ON DATABASE %s; FROM %s', :dataBase, :roleName);

END IF;

END$$;

I have tried various ways to set the variables such as


\set roleName = '''my_role'''

\set roleName = '\'my_role\''

and also on the 02 script without format or using a DECLARE, e.g.


DO 

$$BEGIN

IF EXISTS (SELECT FROM pg_roles WHERE rolname = :'roleName') THEN

    EXECUTE 'REVOKE CONNECT ON DATABASE :dataBase FROM :roleName';

END IF;

END$$;

Not going to answer your question, but `\set roleName = 'my_role'` yields `\echo :roleName =my_role`, which I'm guessing is not what you want the variable set to. You need to do `\set roleName 'my_role'` which yields `\echo :roleName my_role` ` — Adrian Klaver, Feb 21 '22 at 20:51

score 0 · Answer 1 · answered Feb 21 '22 at 16:23

0

A DO statement is a string literal (in your case dollar quoted), and no variable substitution is performed in string literals. The best thing to do might be a PL/pgSQL function.

answered Feb 21 '22 at 16:23

Laurenz Albe

209,280
17
206
263

Thank you for your explanation. Do you have a link to an example to have a starting point for using PL/pgSQL? – Michael Heil Feb 21 '22 at 16:48
You are using it just fine. I can recommend the PostgreSQL documentation. – Laurenz Albe Feb 21 '22 at 20:08

Adrian Klaver · Answer 2 · 2022-02-21T23:24:25.233

If you really want to do this with DO then a proof of concept:

create temp table variable_vals (role_name varchar, db varchar);
insert into variable_vals values ('myRole', 'test');
create role "myRole";

DO
$$
DECLARE
    _role  varchar;
    _db    varchar;
BEGIN
    SELECT INTO _role, _db role_name, db from variable_vals;
    RAISE NOTICE '%, %', _role, _db;
    IF EXISTS (SELECT FROM pg_roles WHERE rolname = _role) THEN
        EXECUTE format('REVOKE CONNECT ON DATABASE %I FROM %I', _db, _role);
    END IF;
END;
$$
;

NOTICE:  myRole, test
DO

UPDATE Rough draft of minimal working example using DO and further example below.

If you wanted to do as full(not-anonymous) function then:

CREATE OR REPLACE FUNCTION public.revoke_db_connect(role_name character varying, db_name character varying)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
BEGIN
    RAISE NOTICE '%, %', role_name, db_name;
    IF EXISTS (SELECT FROM pg_roles WHERE rolname = role_name) THEN
        EXECUTE format('REVOKE CONNECT ON DATABASE %I FROM %I', db_name, role_name);
    END IF;
END;
$function$;

select revoke_db_connect('myRole', 'test');
NOTICE:  myRole, test
NOTICE:  role myRole
 revoke_db_connect 
-------------------

score 0 · Answer 3 · answered Feb 21 '22 at 23:17

This is a duplicate of 28997527.

You could use Alexander Martin's solution and set a session variable:

SET vars.roleName TO :roleName;

DO $$
BEGIN
IF EXISTS (SELECT FROM pg_roles WHERE rolname = current_setting('vars.roleName') THEN
    EXECUTE 'REVOKE CONNECT ON DATABASE :dataBase FROM :roleName';
END IF;
END;
$$;

How to \set and use variables within a block structure of an included script

3 Answers3