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:

1. 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.

2. Passing Variables as Arguments to psql:

   psql -v variable_name=value database_name
   

   • Pass the -v (or --variable or --set) flag followed by variable_name=value.
   • Specify the target database name after the variable definition.

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, and id_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 with EXECUTE.

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.