PSQL: passing variables into a select 'create user ... ' subquery
0
votes
2
answers
61
views
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?
Asked by Paule
(3 rep)
Jun 5, 2025, 02:07 PM
Last activity: Jun 7, 2025, 12:01 PM
Last activity: Jun 7, 2025, 12:01 PM