Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

Variable Substitution

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.

Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire