The
psql
client allows you to modify and create variables using the
\set slash command, and delete them with the \unset
slash command. Variables within
psql
work much
the same way as variables within UNIX and Linux shell programs, such as bash.
Though the overall implementation of variables within
psql
is fairly
simple, they are still useful, as you may easily insert or substitute the values of variables into slash commands and
SQL commands.
Note: When setting and using variables, be aware that
psql
uses a set of pre-defined internal
variables. Setting these to non-intended values may cause unpredictable and undesirable effects within the program.
For a list of these variables and their uses, see Appendix D.
To set a variable, use the \set command, giving the command the name and the value
of the variable you wish to set, in sequence, separated by space(s). This will either modify a previously existing variable or
create a new variable if there is not one matching the variable name you supplied). As Example 6-2 shows,
the variable name can be any length, and you can use any combination of letters, underscores, or numbers, and the value of the variable
may be set to a string of any length.
Example 6-2. Setting a variable
testdb=#
\set myvariable 'There are many like it, but this one is mine.'
Now, when you type \set without any arguments, the variable will appear in the
list of variables.
Example 6-3. The variable list
testdb=#
\set
VERSION = 'PostgreSQL 7.1.3 on i586-pc-linux-gnu, compiled by GCC 2.96'
DBNAME = 'testdb'
USER = 'postgres'
PORT = '5432'
ENCODING = 'SQL_ASCII'
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '
PROMPT3 = '>> '
HISTSIZE = '500'
myvariable = 'There are many like it, but this one is mine.'
Once you have defined a variable, you can use what is known as
interpolation
to place it within both internal slash commands and SQL commands. This makes
it possible to do things like load files into variables, and then use the loaded contents during an
INSERT or SELECT, as well as more basic substitutions.
To substitute a variable value in this way,
prefix the variable name with a colon (:) when you reference it from within other statements.
For example, Example 6-4 demonstrates
how to use a created variable during an INSERT or SELECT
statement.
Example 6-4. Using interpolation during an INSERT
testdb=#
\set manager_id 150
testdb=#
INSERT INTO employees VALUES (
testdb(#
'Kevin',
testdb(#
'Murphy',
testdb(#
:manager_id
testdb(#
);
testdb=#
SELECT * FROM employees WHERE id = :manager_id;
firstname | lastname | id
-----------+----------+-----
Kevin | Murphy | 150
(1 row)
As mentioned, it is possible to insert files into variables and then use interpolation to
insert their content into other commands. To read files, use backticks (`)
to set a variable to the output of the
cat
command (the UNIX command to display the contents
of a file). Example 6-5 and Example 6-6
illustrate a basic way of doing this. In these examples, the
tabledata
file is located in
the user's home directory (
~/
).
Example 6-5. Reading from a file into a variable
testdb=#
\set data `cat tabledata`
testdb=#
\echo :data
'Mike', 'Nelson', 151
Example 6-6. Using a variable in an INSERT
testdb=#
INSERT INTO employees VALUES (:data);
After Example 6-6, you would have a new row within the
employees table with the values set in the data variable.
