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

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

CodePudding user response：

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.

CodePudding user response：

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

CodePudding user response：

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