How do you use script variables in psql?

Variable declaration & usage

This is how you setup your psql genetic modifiers (variables):

\set user_id 101
\set threshold_limit 50


Now let's release our mutated SQL query:

SELECT * FROM users WHERE id = :'user_id' AND threshold > :'threshold_limit';


The little :'variable_name' creatures we've created are now running wild and getting the data we need!

Delving deeper into variable usage

Handling spaces in variable definitions

For variables that contain spaces, use single quotes:

\set user_name 'John Doe'
SELECT * FROM users WHERE name = :'user_name';


This guards our precious user_name variable and correctly conveys its multi-word value in the query.

Variables from the command line

Passage of invocation can be achieved using -v:

psql -v user_id=99 -f your_script.sql


Essentially, this lets you pre-configure your script with custom values before it even takes its first breath.

Variable use in quotes

psql 9.1+ unleashes a new breed of variables that can expand within quotes:

\set table_name 'users'
SELECT * FROM :'table_name' WHERE name = :'user_name';


In this query, our table_name variable has successfully burrowed inside the quotes.

WITH clause as a VAR tutorial

The WITH clause can substitute as a variable declaration hall-of-famer in SQL:

WITH user_pref(id, max_level) AS (VALUES (:user_id, :max_level))
SELECT * FROM userdata WHERE userid = user_pref.id AND level <= user_pref.max_level;


This provides a runway for inline variable declaration and takes us into SQL-standard aesthetics.

Watching out for sneaky semicolons

Watch out for those pesky, uninvited semicolons when setting variables. They don't play nice with variable declarations:

\set user_id 10;
-- Looks harmless, but it's a public enemy number 1 for variable setting.


Understanding PostgreSQL's variable behavior

Variables as macros: A twist in the tale

PostgreSQL uses variables more like macros for text expansion, adding a dash of scripting dynamite into your queries.

Session-specific: The lone wolf

Variables are tightly bound to sessions. They cherish their solitude and do not travel across different sessions:

\set session_var 'session-bound'
-- Like a hermit crab, this variable won't come out to play with others in the next session.


PostgreSQL's variable vs Others: The celebrity match

Remember, PostgreSQL variables put on a different game face than others like MS SQL Server's @somevariable. They have their own rules and don't receive fan mail from other clients like PgAdmin-III.

Include safety measures and flexibility

Fending off SQL injection

Using variables when passing parameters in your query is just like wearing armor against the sneaky attacks of SQL injection. They separate the knights (code) from the horses (data) to ensure a victorious battle.