Using psql variables

In the previous recipe, we have seen how to use the ON_ERROR_STOP variable. Here, we will show you how to work with any variable, including user-defined ones.

Getting ready

As an example, we will create a script that does some work on a given table. We will keep it simple, because we just want to show how variables work.

For instance, we might want to add a text column to a table, and then set it to a given value. So, we write the following lines into a file called vartest.sql:

ALTER TABLE mytable ADD COLUMN mycol text;
UPDATE mytable SET mycol = 'myval';

The script can be run as follows:

psql -f vartest.sql

How to do it…

We change vartest.sql as follows:

\set tabname mytable
\set colname mycol
\set colval 'myval'
ALTER TABLE :tabname ADD COLUMN :colname text;
UPDATE :tabname SET :colname = :'colval';

How it works…

What do these changes mean? We have defined three variables, setting them to the table name, column name, and column value, respectively. Then, we have replaced the...