PSQL: passing variables into a select 'create user ... ' subquery

Question

In Postgres I'm trying to automatize the creation of a non-existing db-user (using it in Debian's postinst script) in the following way:

SELECT 'CREATE USER :v1' WHERE NOT EXISTS (SELECT FROM pg_user WHERE usename = ':v1')\gexec

and calling via

 psql -v v1="flop"

This certainly dosen't work, as :v1 is taken as username string itself and is not substituted. The following error is returned:

LINE 1: CREATE USER :v1

Modifying the line to

... 'CREATE USER ':v1' ' WHERE NOT ... 

returns:

Syntax error at >>' '<<

LINE 1: SELECT 'CREATE USER 'flop' ' WHERE NOT EXISTS (SELECT FROM ...

The variable :v1 is substituted, but it seems to me, the whole command, beginning from SELECT, isn't accepted. Changing by adding some single quotes according to some guidelines, to:

SELECT 'CREATE USER ':'v1'' ' WHERE NOT EXISTS ...

returns

Syntax error at 'flop'

LINE 1: CREATE USER 'flop'

Which looks at me as the substring CREATE USER produces the error due to quotation error. After adding some extra space between the single quotes like

SELECT 'CREATE USER ':'v1' ' ' WHERE ...

the query returns the following error:

Syntax error at >>' '<<

LINE 1: SELECT 'CREATE USER ''flop' ' ' WHERE ...

When removing the forst variable :v1 and putting the username explicitly, like

SELECT 'CREATE USER flop' WHERE NOT ...

everything works together with the variable substitution at usename=.

So is seems to me it's "just" a quotation problem. But how cat I remove the single quotes and get the script working?

Answer 1

If you want to write it in shell, you can use a "here document":

psql -v v1='go go' <<-"EOF"
 CREATE PROCEDURE pg_temp.mkuser(text)
 LANGUAGE plpgsql AS
 $$BEGIN
 EXECUTE format('CREATE ROLE %I LOGIN', 1ドル);
 EXCEPTION WHEN duplicate_object THEN
 NULL;
 END;$$;
 CALL pg_temp.mkuser(:'v1');
EOF

The temporary procedure allows you to pass the user name as an argument. You cannot use parameters with CREATE ROLE, so I use EXECUTE to run a dynamically created SQL statement. format() does the necessary escaping for an SQL identifier. I use an exception clause to capture the error if the role already exists.

• Vérace: You reference "a here document". Perhaps you meant something like "a document here"? i.e did you forget a link?
• Laurenz Albe: @Vérace "Here document" is a technical term, see man bash. I put it in quotation marks for clarity.

Answer 2

You can use quote_ident() and quote_literal() to solve the problem。

Variable should be enclosed in single quotes when passed in.

psql -v v1="'flop'" -f /tmp/pg/1.sql

1.sql contents:

SELECT 'CREATE USER ' || quote_ident(:v1) || 
';' WHERE NOT EXISTS (SELECT FROM pg_user
WHERE username = quote_literal(:v1))\gexec