DO
$do$
BEGIN
   IF EXISTS (
      SELECT FROM pg_catalog.pg_roles
      WHERE  rolname = '%%QBIT42USER%%') THEN

      RAISE NOTICE 'Role "%%QBIT42USER%%" already exists. Skipping.';
   ELSE
      BEGIN   -- nested block
         CREATE ROLE %%QBIT42USER%% LOGIN PASSWORD '%%QBIT42PW%%';
      EXCEPTION
         WHEN duplicate_object THEN
            RAISE NOTICE 'Role "%%QBIT42USER%%" was just created by a concurrent transaction. Skipping.';
      END;
   END IF;
END
$do$;

DO
$do$
BEGIN
   IF EXISTS (
      SELECT FROM pg_catalog.pg_roles
      WHERE  rolname = '%%BROADCASTUSER%%') THEN

      RAISE NOTICE 'Role "%%BROADCASTUSER%%" already exists. Skipping.';
   ELSE
      BEGIN   -- nested block
         CREATE ROLE %%BROADCASTUSER%% LOGIN PASSWORD '%%BROADCASTPW%%';
      EXCEPTION
         WHEN duplicate_object THEN
            RAISE NOTICE 'Role "%%BROADCASTUSER%%" was just created by a concurrent transaction. Skipping.';
      END;
   END IF;
END
$do$;


SELECT 'CREATE DATABASE broadcast'
WHERE NOT EXISTS (SELECT from pg_database WHERE datname = 'broadcast')\gexec

GRANT ALL PRIVILEGES ON DATABASE broadcast TO %%BROADCASTUSER%%;

SELECT 'CREATE DATABASE qbit42'
WHERE NOT EXISTS (SELECT from pg_database WHERE datname = 'qbit42')\gexec

GRANT ALL PRIVILEGES ON DATABASE qbit42 TO %%QBIT42USER%%;

\c qbit42;

CREATE EXTENSION IF NOT EXISTS vector;