Using psql variables
In the previous recipe, you learned 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 takes a table name as a parameter. 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 must write the following lines in 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...