如何在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屋！