Keywords: PostgreSQL | psql | script variables | \set command | SQL development
Abstract: This article provides an in-depth exploration of using script variables in the PostgreSQL client psql. It covers the creation of variables with the \set command, their referencing in SQL statements, and syntax variations across different psql versions. Through detailed code examples, the article demonstrates variable applications in table name references, conditional queries, and string handling, with comparisons to MS SQL Server variable declarations. Advanced topics include passing variables from the command line and database-level settings, offering practical guidance for database administration and script development.
Introduction
In database script development, the use of variables is crucial for enhancing code maintainability and flexibility. Unlike MS SQL Server, which employs DECLARE statements for variable declaration, PostgreSQL's official client psql offers a unique variable management mechanism. This article systematically explains how to effectively use script variables in psql, covering basic syntax, advanced techniques, and practical application scenarios.
Basic Operations of psql Variables
In psql, variables are created and managed using the \set command. The basic syntax is \set variable_name value. For example, to create a variable named myvariable with the value somevalue, execute \set myvariable somevalue. Once created, variables can be referenced in SQL statements using a colon prefix, such as SELECT * FROM :myvariable.
Unlike MS SQL Server's DECLARE @somevariable int, psql variables are session-scoped and defined at the top of scripts for easy maintenance. For instance, to mimic the MS SQL Server example: after \set somevariable -1, executing INSERT INTO foo VALUES (:somevariable) achieves similar functionality.
Referencing Variables in SQL Statements
The method of variable reference depends on the context. For identifiers like table or column names, use :variable_name directly, e.g., SELECT * FROM :myvariable.table1. In conditional expressions where variables act as values, quote handling is essential. In psql 9.1 and later, the syntax :\'variable_name\' automatically handles string quotes, for example: SELECT * FROM table1 WHERE column1 = :\'myvariable\'.
For older psql versions, variable values must include quotes manually. For instance, set a variable with quotes: \set myvariable \'value\', then use it in queries like SELECT * FROM table1 WHERE column1 = :myvariable. To generate a quoted version from an existing variable, use \set quoted_myvariable \'\\\'\' :myvariable \'\\\'\'\', creating both quoted and unquoted forms.
Advanced Applications and Version Differences
Variables play a key role in complex queries. For example, combining table names and conditions: INSERT INTO :myvariable.table1 SELECT * FROM table2 WHERE column1 = :quoted_myvariable. This approach allows dynamic specification of table names and query conditions, enhancing script adaptability.
Passing variables from the command line is another practical feature. Use the -v option, e.g., psql -v filepath=/path/to/file.data, and reference it in the psql session: SELECT :\'filepath\'. Note that this method does not work with -c commands or graphical tools like PgAdmin-III, and variable substitution occurs at input time, preventing dynamic changes in function definitions.
Supplementary Methods and Comparisons
Beyond \set, PostgreSQL supports using WITH clauses to simulate variables. For example: WITH vars AS (SELECT 42 AS answer, 3.14 AS appr_pi) SELECT t.*, vars.answer, t.radius*vars.appr_pi FROM table AS t, vars. This method is effective in pure SQL environments but does not rely on psql-specific features.
The reference article mentions database-level settings, such as ALTER DATABASE mydb SET app.settings.bla = 1::text, which enable persistent variables across sessions. Functions like pg_db_setting can retrieve these settings, contrasting with current_setting to show session override mechanisms. For instance, role-specific settings: ALTER ROLE myrole IN DATABASE mydb SET app.settings.bla = 2::text, queried via SELECT pg_db_role_setting(\'app.settings.bla\', current_user).
Conclusion
psql variables, managed via the \set command, offer flexible script customization. Key points include: correct reference syntax for different contexts, handling quote requirements across versions, and integration with command-line and database-level settings. Compared to MS SQL Server, psql variables are lighter and session-bound, while WITH clauses and database settings extend application scenarios. Mastering these techniques significantly improves the readability and maintainability of PostgreSQL scripts.