0

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?

nlarralde
  • 27
  • 6

1 Answers1

0

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
Huzaifa
  • 484
  • 4
  • 8
  • Thanks @Huzaifa but... Is there a simplest way to concat variables and strings? Before making a plblock I used "\set" to concat prefix and sufix in a single variable => I'm trying to avoid "\set" and concat directly a prefix with a string at execution time. – nlarralde Aug 12 '23 at 19:43