Using Script Variables in psql for PostgreSQL Queries
psql, the command-line interface for PostgreSQL, allows you to define variables within your scripts to make your SQL code more flexible and reusable. These variables act as containers for values you can reference throughout your script.
Benefits of Using Variables:
- Readability: Variables improve code clarity by replacing hardcoded values with meaningful names.
- Maintainability: If a value needs to change, you only modify the variable definition, not every instance of the value in your script.
- Reusability: Variables enable you to create generic scripts that can be adapted to different scenarios by changing variable values.
Setting Variables:
There are two main ways to set variables in psql scripts:
Using the
\set
metacommand:\set variable_name value
- Replace
variable_name
with the name you choose for your variable (follow standard identifier naming conventions). - Replace
value
with the actual data you want to store. This value can be a string, number, or even a complex expression.
- Replace
Passing Variables as Arguments to psql:
psql -v variable_name=value database_name
- Pass the
-v
(or--variable
or--set
) flag followed byvariable_name=value
. - Specify the target database name after the variable definition.
- Pass the
Using Variables in SQL Statements:
Once you've defined a variable, you can reference its value within your SQL statements using the single colon (:
) prefix:
SELECT * FROM my_table WHERE id = :variable_name;
- In this example,
:variable_name
will be replaced with the actual value stored in the variable.
Important Notes:
- Variables within single quotes (''') are treated as literal strings, not references to their values. Use double quotes (") if the variable might contain special characters.
- For complex logic or calculations involving variables, consider using PL/pgSQL procedural language within your script.
Example:
\set table_name customers; -- Set variable for table name
SELECT * FROM :table_name WHERE city = 'New York';
-- Update using the same variable
UPDATE :table_name SET email = '[email protected]' WHERE id = 10;
Example Codes for Script Variables in psql
Filtering Data Based on User Input:
This example prompts the user for a minimum order value and then filters the orders
table:
\set min_value -1 -- Initialize with a default value (optional)
-- Prompt user for input and store it in a variable
\prompt Enter minimum order value: \set min_value
SELECT * FROM orders WHERE order_total >= :min_value;
Executing Conditional Statements:
This example uses a variable to control which table to query:
\set target_table products; -- Set the default target
IF EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'customers') THEN
\set target_table customers;
END IF;
SELECT * FROM :target_table;
Looping Through a List (using PL/pgSQL):
This example demonstrates a PL/pgSQL code block that iterates through a list of IDs stored in a variable:
DO $$
DECLARE
id_list text := '{1, 2, 3}'; -- List of IDs as a string
id int;
BEGIN
FOR id IN EXECUTE ($id_list) LOOP
SELECT * FROM users WHERE id = id;
END LOOP;
END;
$$;
Remember:
- Replace placeholders like
min_value
,target_table
, andid_list
with your actual values. - Adapt these examples to your specific needs and database schema.
Here strings provide a way to embed literal text with variable references within single quotes ('''). This can be useful for constructing dynamic SQL statements:
SELECT * FROM my_table
WHERE id = ''' || :variable_name || ''';
- The double pipes (
||
) concatenate strings and variable references. - Note that here strings treat variables literally, so escaping special characters might be necessary.
Parameterized Queries:
Parameterized queries allow you to pre-define the structure of your SQL statement and then pass values separately. This is a secure approach that helps prevent SQL injection vulnerabilities:
-- Prepare the statement template
PREPARE my_query(int) AS
SELECT * FROM my_table WHERE id = $1;
-- Execute with different values
EXECUTE my_query(10);
EXECUTE my_query(25);
- Prepared statements are defined with
PREPARE
. - Values are passed using positional placeholders (
$1
,$2
, etc.) during execution withEXECUTE
.
Command-Line Arguments:
When running psql from the command line, you can pass arguments directly to your script. These arguments can then be accessed within the script using special variables:
psql database_name -f my_script.sql arg1 arg2
- Inside the script, use
$$
to enter a code block and access arguments with$n
(e.g.,$1
for the first argument).
Choosing the Right Method:
The best method depends on your specific needs:
- Readability and simplicity: Script variables are often preferred for their ease of use.
- Security: Parameterized queries are essential when dealing with user-provided data to prevent SQL injection.
- Flexibility: Here strings offer some flexibility in constructing dynamic queries.
- Command-line integration: Command-line arguments are useful for directly passing values during script execution.
sql postgresql variables