Check if database matches and create user using PG/PLSQL-CodePudding

The below script runs but does not create the user when database name matches? i just cannot spot what i am doing wrong here.


DO
$do$
DECLARE
lc_s_db_name CONSTANT VARCHAR(30) := 'dev';
lv_db_name  VARCHAR(100);
BEGIN
    select datname into lv_db_name from pg_catalog.pg_database;
     
    IF lv_db_name in (lc_s_db_name )
    THEN
        create role monitor LOGIN PASSWORD 'monitor';
    END IF;
 END
 $do$;

CodePudding user response：

Your select query picks one database name of the list of all databases. That's not necessarily the one you are connected to.

To get the name of the database you are connected to, use the function current_database()

DO
$do$
DECLARE
  lc_s_db_names CONSTANT text[] := array['dev', 'dev2'];
BEGIN
  IF current_database() = any(lc_s_db_names)
  THEN
    create role monitor LOGIN PASSWORD 'monitor';
  END IF;
END
$do$;