CREATE FUNCTION create_user_if_not_exists(_name text, _pass text)
RETURNS void AS
$func$
DECLARE
_dbname TEXT := concat(_name, '_db');
BEGIN
IF EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = _name) THEN
RAISE NOTICE 'Role already exists. Skipping.';
ELSE
CREATE USER _name WITH encrypted password '_pass';
GRANT ALL PRIVILEGES ON DATABASE _dbname TO _name;
END IF;
END
$func$ LANGUAGE plpgsql;
When I run:
psql --username "$POSTGRES_USER" -c "SELECT create_user_if_not_exists('Foo', 'BAR');"
I get:
ERROR: database "_dbname" does not exist
If comment the GRANT ALL part, I end up with a user named _name in pg_catalog.pg_roles. I suspected _ to be responsible for it but same with various syntax. Adding _name and _pass to DECLARE does not change a thing.
Why are _name, _pass and _dbname interpreted as values instead of proper variables in this context? Is it a matter of function definition, some restrictions on GRANT and CREATE or because of the way I call this function with psql?
After reading this and this, I fail to grasp the difference explaining what is wrong.
Environment: psql (PostgreSQL) 17.4 (Debian 17.4-1.pgdg120+2)
PL/pgSQL variables are handled as parameters in SQL statements. But neither CREATE USER nor GRANT support parameters — only SELECT, INSERT, UPDATE, DELETE and VALUES do. So you will have to construct a query string using format() and execute it with EXECUTE:
EXECUTE format(
'CREATE USER %I WITH encrypted password %L',
_name,
_pass
);