如何在 PostgreSQL 查询中声明变量 [英] How to declare a variable in a PostgreSQL query

问题描述

如何声明在 PostgreSQL 8.3 查询中使用的变量?

How do I declare a variable for use in a PostgreSQL 8.3 query?

在 MS SQL Server 中，我可以这样做:

In MS SQL Server I can do this:

DECLARE @myvar INT
SET @myvar = 5

SELECT *
FROM somewhere
WHERE something = @myvar

我如何在 PostgreSQL 中做同样的事情?根据文档，变量被简单地声明为名称类型；"，但这给了我一个语法错误:

How do I do the same in PostgreSQL? According to the documentation variables are declared simply as "name type;", but this gives me a syntax error:

myvar INTEGER;

有人能给我一个正确语法的例子吗?

Could someone give me an example of the correct syntax?

推荐答案

PostgreSQL 中没有这样的功能.您只能在 pl/PgSQL(或其他 pl/*)中执行此操作，而不能在普通 SQL 中执行此操作.

There is no such feature in PostgreSQL. You can do it only in pl/PgSQL (or other pl/*), but not in plain SQL.

一个例外是 WITH () 查询，它可以用作变量，甚至是变量的 tuple.它允许您返回一个临时值表.

An exception is WITH () query which can work as a variable, or even tuple of variables. It allows you to return a table of temporary values.

WITH master_user AS (
    SELECT
      login,
      registration_date
    FROM users
    WHERE ...
)

SELECT *
FROM users
WHERE master_login = (SELECT login
                      FROM master_user)
      AND (SELECT registration_date
           FROM master_user) > ...;

这篇关于如何在 PostgreSQL 查询中声明变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！