SQL 如何在 PostgreSQL 查询中声明变量

How to declare a variable in a PostgreSQL query

提问by EMP

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

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

In MS SQL Server I can do this:

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

DECLARE @myvar INT
SET @myvar = 5

SELECT *
FROM somewhere
WHERE something = @myvar

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:

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

myvar INTEGER;

Could someone give me an example of the correct syntax?

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

采纳答案by fei0x

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

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

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

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

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) > ...;

回答by fei0x

I accomplished the same goal by using a WITHclause, it's nowhere near as elegant but can do the same thing. Though for this example it's really overkill. I also don't particularly recommend this.

我通过使用WITH子句实现了相同的目标，它远不及优雅，但可以做同样的事情。虽然对于这个例子，它真的有点矫枉过正。我也不是特别推荐这个。

WITH myconstants (var1, var2) as (
   values (5, 'foo')
)
SELECT *
FROM somewhere, myconstants
WHERE something = var1
   OR something_else = var2;

回答by Dario Barrionuevo

You could also try this in PLPGSQL:

你也可以在 PLPGSQL 中试试这个：

DO $$
DECLARE myvar integer;
BEGIN
    SELECT 5 INTO myvar;

    DROP TABLE IF EXISTS tmp_table;
    CREATE TABLE tmp_table AS
    SELECT * FROM yourtable WHERE   id = myvar;
END $$;

SELECT * FROM tmp_table;

The above requires Postgres 9.0 or later.

以上需要 Postgres 9.0 或更高版本。

回答by a_horse_with_no_name

Dynamic Config Settings

动态配置设置

you can "abuse" dynamic config settings for this:

您可以为此“滥用”动态配置设置：

-- choose some prefix that is unlikely to be used by postgres
set session my.vars.id = '1';

select *
from person 
where id = current_setting('my.vars.id')::int;

Config settings are always varchar values, so you need to cast them to the correct data type when using them. This works with any SQL client whereas \setonly works in psql

配置设置始终是 varchar 值，因此在使用它们时需要将它们转换为正确的数据类型。这适用于任何 SQL 客户端，而\set仅适用于psql

The above requires Postgres 9.2 or later.

以上需要 Postgres 9.2 或更高版本。

For previous versions, the variable had to be declared in postgresql.confprior to being used, so it limited its usability somewhat. Actually not the variable completely, but the config "class" which is essentially the prefix. But once the prefix was defined, any variable could be used without changing postgresql.conf

对于以前的版本，变量必须在使用postgresql.conf之前声明，因此在某种程度上限制了它的可用性。实际上完全不是变量，而是配置“类”，它本质上是前缀。但是一旦定义了前缀，任何变量都可以使用而无需更改postgresql.conf

回答by Shahriar Aghajani

It depends on your client.

这取决于您的客户。

However, if you're using the psqlclient, then you can use the following:

但是，如果您使用的是psql客户端，则可以使用以下内容：

my_db=> \set myvar 5
my_db=> SELECT :myvar  + 1 AS my_var_plus_1;
 my_var_plus_1 
---------------
             6

If you are using text variables you need to quote.

如果您使用文本变量，则需要引用。

\set myvar 'sometextvalue'
select * from sometable where name = :'myvar';

回答by Shahriar Aghajani

Using a Temp Table outside of pl/PgSQL

在 pl/PgSQL 之外使用临时表

Outside of using pl/pgsql or other pl/* language as suggested, this is the only other possibility I could think of.

除了按照建议使用 pl/pgsql 或其他 pl/* 语言之外，这是我能想到的唯一其他可能性。

begin;
select 5::int as var into temp table myvar;
select *
  from somewhere s, myvar v
 where s.something = v.var;
commit;

回答by bluish

I want to propose an improvement to @DarioBarrionuevo's answer, to make it simpler leveraging temporary tables.

我想对@DarioBarrionuevo 的回答提出改进意见，使其更容易利用临时表。

DO $$
    DECLARE myvar integer = 5;
BEGIN
    CREATE TEMP TABLE tmp_table ON COMMIT DROP AS
        -- put here your query with variables:
        SELECT * 
        FROM yourtable
        WHERE id = myvar;
END $$;

SELECT * FROM tmp_table;

回答by Jorge Luis

This solution is based on the one proposed by fei0xbut it has the advantages that there is no need to join the value list of constants in the query and constants can be easily listed at the start of the query. It also works in recursive queries.

这个方案是基于fei0x提出的方案，但是它的优点是不需要在查询中加入常量的值列表，并且可以很容易地在查询开始时列出常量。它也适用于递归查询。

Basically, every constant is a single-value table declaredin a WITH clause which can then be called anywhere in the remaining part of the query.

基本上，每个常量都是在 WITH 子句中声明的单值表，然后可以在查询的其余部分中的任何地方调用。

• Basic example with two constants:

• 具有两个常量的基本示例：

WITH
    constant_1_str AS (VALUES ('Hello World')),
    constant_2_int AS (VALUES (100))
SELECT *
FROM some_table
WHERE table_column = (table constant_1_str)
LIMIT (table constant_2_int)

Alternatively you can use SELECT * FROM constant_nameinstead of TABLE constant_namewhich might not be valid for other query languages different to postgresql.

或者，您可以使用whichSELECT * FROM constant_name代替TABLE constant_namewhich 对于不同于 postgresql 的其他查询语言可能无效。

回答by Martin Zinovsky

Here is an example using PREPARE statements. You still can't use ?, but you can use $nnotation:

这是一个使用PREPARE 语句的示例。你仍然不能使用?，但你可以使用$n符号：

PREPARE foo(integer) AS
    SELECT  *
    FROM    somewhere
    WHERE   something = ;
EXECUTE foo(5);
DEALLOCATE foo;

回答by Michael.Medvedskiy

True, there is no vivid and unambiguous way to declare a single-value variable, what you can do is

确实，没有生动明确的方式来声明单值变量，您可以做的是

with myVar as (select "any value really")

then, to get access to the value stored in this construction, you do

然后，要访问存储在此构造中的值，您可以

(select * from myVar)

for example

例如

with var as (select 123)    
... where id = (select * from var)