如何将PSQL变量传递给CREATE USER的PASSWORD选项

Question

我在sql脚本中有一个do块(create-user.sql(，如下所示，用于创建用户

do $$
begin
create user :user_name with password ':pass';
exception
when duplicate_object then
raise notice 'user already exists';
end$$

我想将user_name和pwd传递给这个脚本，所以我使用了-v选项

psql --host="myhost" --port=5432 --username=connectionuser --dbname=xyz -v user_name=myuser -v pass=mypass -f create-user.sql

当我执行命令时，它会给我一个错误

在"：">

如何将变量传递到sql脚本？我甚至尝试过：=而不是：，但类似的错误被抛出

Answer 1

这就是我的工作原理。

psql -qf setup-postgres.sql                                            
--set POSTGRES_USERNAME=$POSTGRES_USERNAME                         
--set DATOMIC_USERNAME=$POSTGRES_DATOMIC_USERNAME                  
--set DATOMIC_PASSWORD=$POSTGRES_DATOMIC_PASSWORD                  
postgresql://$POSTGRES_USERNAME:$[e-mail]

setup-postgres.sql:

SET client_min_messages TO WARNING;
CREATE DATABASE datomic WITH
CONNECTION LIMIT = -1
ENCODING = 'UTF8'
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8'
OWNER = :"POSTGRES_USERNAME"
TABLESPACE = pg_default
TEMPLATE template0
;
c datomic
SET DATOMIC.USERNAME TO :"DATOMIC_USERNAME";
SET DATOMIC.PASSWORD TO :'DATOMIC_PASSWORD';
DO $$
BEGIN
EXECUTE format('CREATE ROLE %I LOGIN PASSWORD %L'
, current_setting('DATOMIC.USERNAME')
, current_setting('DATOMIC.PASSWORD')
);
EXCEPTION
WHEN duplicate_object THEN
RAISE NOTICE 'User already exists. Ignoring...';
END$$;
DO $$
BEGIN
CREATE TABLE datomic_kvs (
id text NOT NULL,
rev integer,
map text,
val bytea,
CONSTRAINT pk_id PRIMARY KEY (id)
)
WITH (
OIDS = FALSE
);
EXCEPTION
WHEN duplicate_table THEN
RAISE NOTICE 'Table already exists. Ignoring...';
END$$;
ALTER TABLE datomic_kvs
OWNER TO :"POSTGRES_USERNAME";
GRANT ALL ON TABLE datomic_kvs
TO :"POSTGRES_USERNAME";
GRANT ALL ON TABLE datomic_kvs
TO public;

奇怪的是，SET命令中的变量名，例如DATOMIC.USERNAME，必须包含.