How to Declare Variables in PostgreSQL Queries?

How to declare variables in PostgreSQL queries

Unlike MS SQL Server, PostgreSQL does not support declaring variables using the DECLARE statement. To use variables in PostgreSQL queries, you can use the WITH clause as an alternative.

Grammar:

<code class="language-sql">WITH 变量名 AS (
   SELECT 表达式 AS 名称
)
SELECT *
FROM 表名
WHERE 列名 = 变量名</code>

Example:

Consider the following MS SQL Server query:

<code class="language-sql">DECLARE @myvar INT;
SET @myvar = 5;
SELECT * FROM somewhere WHERE something = @myvar;</code>

To achieve the same functionality in PostgreSQL using the WITH clause:

<code class="language-sql">WITH myvar AS (
   SELECT 5 AS var
)
SELECT *
FROM somewhere
WHERE something = myvar;</code>

Note:

Although the WITH clause provides a way to declare variables in PostgreSQL queries, it is generally not considered the most elegant or efficient method. For complex queries that may require multiple variables, it can lead to cluttered and less readable code. In this case, it is better to create a temporary table or use a stored procedure to encapsulate the logic related to the variables.

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