Declare a variable in a PostgreSQL query

sql

session-variables

plpgsql

temporary-tables

byNikita Barsukov·Aug 20, 2024

In PostgreSQL, the Common Table Expression (CTE) or subquery can work as an inline variable for a single SQL query:

WITH variable_cte AS (SELECT 42 AS my_var)
SELECT * FROM my_table WHERE my_column = (SELECT my_var FROM variable_cte);

You can declare a temporary and local variable in a script in this way:

DO $$
DECLARE
  my_var int := 42;
BEGIN
  -- Replace below with the logic using the variable
  RAISE NOTICE 'Guess what? The secret number my_var is: %', my_var;
END $$;

CTEs/subqueries are ideal for quick needs, while the DO block is suited for extended operations. Note that variables in DO blocks won't persist beyond the current execution.

Grasp the variety, respect the client

Variables for all seasons

Broaden your horizons with session variables in PostgreSQL that provide persistence across your session.

SET session "my.vars.variable_name" = 'value';
SELECT current_setting('my.vars.variable_name')::int;

This method proves useful when handling multi-statement transactions where values are required to persist.

Complex, not complicated

With more complex operations or composite procedures, sticking to pl/PgSQL syntax ensures comprehensive variable manipulation:

CREATE OR REPLACE FUNCTION the_operation()
RETURNS void AS $$
DECLARE
  my_complex_variable complex_type;
BEGIN
  -- Complex logic uses my_complex_variable
END;
$$ LANGUAGE plpgsql;

Charming `psql`

Leverage the simplicity of psql for settings variables:

\set my_number 1337
SELECT :my_number;

For string variables:

\set my_string 'Hello, world!'
SELECT * FROM planet WHERE greeting = :'my_string';

Dodge bullets, ensure survival

Playing safe

Preventing mistakes such as redundant declarations is crucial when creating temporary tables:

DROP TABLE IF EXISTS your_temp_table;
CREATE TEMPORARY TABLE your_temp_table AS
SELECT * FROM earthlings WHERE iq = 'above_average';

Be versatile

Dynamic tables based on variable conditions can up your PostgreSQL game:

CREATE TEMP TABLE IF NOT EXISTS my_table AS
SELECT * FROM aliens WHERE id = current_setting('my.vars.id')::int;

Know your battlefield

Every SQL client handles variables differently. The key is ensuring compatibility whilst making the most of unique client features.

SQL ninja tricks and tips

Prepare for the mission

Whether it's a hand-shaken cocktail or automatic car transmission, pick your right tool for the right job. CTEs for speedy stunts, pl/PgSQL and session variables for the long haul.

Clean your dojo

Optimize, debug (RAISE NOTICE is your buddy there), cast with current_setting() ::type and your variables will be in tip-top condition.

The art of SQL

Write voluptuous variables and intricate interactions within your environment. This separates the wheat from the chaff, a good SQL script from an exceptional one.

explain-codes / Sql / Declare a variable in a PostgreSQL query

Linked

Error SQL70001: This statement is not recognized in this context



How do I use variables in Oracle SQL Developer?



How do you use script variables in psql?



Sql Server SELECT INTO @variable?



Sql Row_Number() function in Where Clause

