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?

Abelisto · Accepted Answer · 2020-10-20 06:10:58Z

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 │
└─────────────┴──────────┴──────────┴─────────────┴─────────────┴───────────────────────┘

answered Oct 20, 2020 at 6:10

Abelisto

15.8k3 gold badges38 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

JeZ Over a year ago

In Oracle we have prefix_&&argument._suffix, anything similar in Postgres? I need to handle it in the statement itself.

Abelisto Over a year ago

@JeZ As I know, no. postgresql.org/docs/current/…

JeZ Over a year ago

In that case, let me set the parameters as you have shown. Thank you

Abelisto Over a year ago

@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 Over a year ago

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

Laurenz Albe · Accepted Answer · 2020-10-20 06:05:25Z

0

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.

edited Oct 20, 2020 at 6:05

answered Oct 19, 2020 at 19:06

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

3 Comments

JeZ Over a year ago

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 Over a year ago

You should have said that in the question. See my extended answer.

JeZ Over a year ago

Is there any possibility to do it without a function? In Oracle we have prefix_&&argument._suffix, anything similar in Postgres?

Collectives™ on Stack Overflow

Postgresql command line variable usage in sql file

2 Answers 2

5 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related