Postgres/psql sql script: how to pass arguments as partial name

Question

Postgres/psql 13

Hi all!

I have to create several roles with the same prefix:

create role XXX_admin...
create role XXX_owner...
create role XXX_user...

then I want to create a sql script that create those users by using a var rname=XXX:

cat myscrpt.sql
create role :<rname>_admin...;
create role :<rname>_owner...;
create role :<rname>_user...;

to run it for multiple rnames (XXX, YYY, ZZZ, etc).

for i in XXX YYY ZZZ
do
   psql -p5460 -d mydb -f myscrpt.sql -v rname=$i;
done

How I can delimit the varname from the suffixes?

Answer 1

You can go with the custom sql script/function. Just update your current script.

Something like this might work. (pseudocode):

//Your function
BEGIN
    FOR name IN ('XXX', 'YYY', 'ZZZ')
    LOOP
        Construct the SQL statement to create _admin role using name
        Execute the constructed SQL statement

        // Similarly for others (owner,user)

    END LOOP;
END;

Now, just exuecte the script.

psql -p 5460 -d mydb -f myscript.sql