Postgresql command line variable usage in sql file

Question

I am using psql command to call a file bypassing argument as -v argument=value. In the SQL file, the argument needs to be used as part of a string. For example prefix_:argument_suffix. This is not working. If I use it as prefix_:argument it works, but when it comes to the suffix it doesn't.

This is for building a database name in the create database statement.

Can somebody help with this?

Answer 1

Use another variable like:

nd@postgres=# \set argument 'aaa'
nd@postgres=# \set dbname 'foo_':argument'_bar'
nd@postgres=# select :'dbname';
┌─────────────┐
│ ?column? │
├─────────────┤
│ foo_aaa_bar │
└─────────────┘
nd@postgres=# create database :dbname;
CREATE DATABASE
nd@postgres=# \l
 List of databases
┌─────────────┬──────────┬──────────┬─────────────┬─────────────┬───────────────────────┐
│ Name │ Owner │ Encoding │ Collate │ Ctype │ Access privileges │
├─────────────┼──────────┼──────────┼─────────────┼─────────────┼───────────────────────┤
│ foo_aaa_bar │ nd │ UTF8 │ en_US.UTF-8 │ en_US.UTF-8 │ │
│ postgres │ postgres │ UTF8 │ en_US.UTF-8 │ en_US.UTF-8 │ │
│ template0 │ postgres │ UTF8 │ en_US.UTF-8 │ en_US.UTF-8 │ =c/postgres ↵│
│ │ │ │ │ │ postgres=CTc/postgres │
│ template1 │ postgres │ UTF8 │ en_US.UTF-8 │ en_US.UTF-8 │ =c/postgres ↵│
│ │ │ │ │ │ postgres=CTc/postgres │
└─────────────┴──────────┴──────────┴─────────────┴─────────────┴───────────────────────┘

• JeZ: In Oracle we have prefix_&&argument._suffix, anything similar in Postgres? I need to handle it in the statement itself.
• Abelisto: @JeZ As I know, no. postgresql.org/docs/current/…
• JeZ: In that case, let me set the parameters as you have shown. Thank you
• Abelisto: @JeZ Yet another way is to set prefix/suffix variables and then use it like create database :prefix:argument:suffix. Choose the case most suitable for you. Good luck!
• JeZ: In my case, there are multiple prefixes and suffixes which are constants. So I am going with your first answer. That would suffice. Thank you

Answer 2

The string could be built as

'prefix_' || :'variable' || '_suffix'

To use it in a CREATE DATABASE statement, you will have to use dynamic SQL in a function:

CREATE FUNCTION pg_temp.mkdb(var text) RETURNS void
 LANGUAGE plpgsql STRICT AS
$$BEGIN
 EXECUTE format('CREATE DATABASE %I;', 'prefix_' || var || '_suffix');
END;$$;
SELECT pg_temp.mkdb(:'variable');

Since the function is created in the temporary schema, it will exist only in this database session.

• JeZ: This is for building a database name in the create database statement. This won't work in that case. Could you please help with that case
• Laurenz Albe: You should have said that in the question. See my extended answer.
• JeZ: Is there any possibility to do it without a function? In Oracle we have prefix_&&argument._suffix, anything similar in Postgres?