How Can I Declare and Use Variables in PostgreSQL Queries?

Declare and use variables in PostgreSQL queries

This article describes how to declare variables in PostgreSQL 8.3 and above queries. In MS SQL Server, the variable declaration syntax is simple:

<code class="language-sql">DECLARE @myvar INT;
SET @myvar = 5;

SELECT * FROM somewhere WHERE something = @myvar;</code>

However, this approach will throw an error in PostgreSQL, although the documentation recommends using a simple "name type;" declaration. The following code will throw a syntax error:

<code class="language-sql">myvar INTEGER;</code>

Solution using WITH clause

An alternative to declaring variables in PostgreSQL is to use the WITH clause. Although not as concise as the method in MS SQL Server, it achieves the same functionality. Note that for simple scenarios, using the WITH clause may appear too redundant:

<code class="language-sql">WITH myconstants (var1, var2) as (
   values (5, 'foo')
)
SELECT *
FROM somewhere, myconstants
WHERE something = var1
   OR something_else = var2;</code>

The above is the detailed content of How Can I Declare and Use Variables in PostgreSQL Queries?. For more information, please follow other related articles on the PHP Chinese website!