Using psql variables

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

Getting ready

As an example, we 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 respectively to the table name, column name and column value. Then we have replaced the mentions of those...