1

PostgeSQL: create role/user if not exists?

says

do
$$
begin
  if not exists (select * from pg_user where usename = 'new_role') then
     create role new_role password '...';
  end if;
end
$$
;

I wonder whether ther is a method inside this SQL to loop this logig through a list of usernames

in pseudo terms it would somethig like ...

for username in user1 user2 user3
   if ${username} exists
      create user ${username}
   else
      echo "user exists already"
done
2
  • Cursor and iteration over it. postgresql.org/docs/15/plpgsql-cursors.html
    – Akina
    Nov 15, 2022 at 12:22
  • thx for the tip. It is, however, way beyond my current level of understanding yet. I'll get back to that sort of complexity later and stick to one by one creation for now.
    – vrms
    Nov 17, 2022 at 11:51

1 Answer 1

0

You could encapsulate your statement into a function:

CREATE OR REPLACE FUNCTION add_user_if_not_exists(username NAME, pw TEXT) RETURNS integer
    AS $$
BEGIN
   IF NOT EXISTS ( SELECT FROM pg_roles  
                   WHERE  rolname = username) THEN

        EXECUTE FORMAT('CREATE ROLE "%I" PASSWORD %L', username, pw);
   END IF;
   RETURN NULL;
END;
$$ language plpgsql;

Then just have these simple statements

SELECT add_user_if_not_exists('user1', 'pw1');
SELECT add_user_if_not_exists('user2', 'pw2');
1
  • that looks slick. I am wondering why such a logic is not builtin. The application of IF NOt EXISTS seems somewhat now executed in all consequence (existis for some cases, does not for others) in postgres
    – vrms
    Sep 16, 2023 at 11:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.