blocks|key|1442946|text|PostgreSQL中没有这样的功能。您只能在pl/PgSQL+(或其他pl/*)中执行此操作，但不能在纯SQL中执行此操作。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1442947|一个例外是WITH+()查询，它可以作为变量，甚至是变量的tuple。它允许您返回临时值的表。|offset|length|style|CODE|1442948|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)+>+...;|code-block|syntax|javascript|1442949|entityMap^0|0|5|7|T|5|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@$D|Q|E|R|F|G]|$D|S|E|T|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|U|8|@]|9|@]|A|$K|L]]|$1|M|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|N|$]]

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

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

<pre><code>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) &gt; ...;
</code></pre>

blocks|key|1228175|text|我通过使用WITH+clause实现了同样的目标，虽然它不是很优雅，但可以做同样的事情。不过，对于这个例子来说，这实在是言过其实了。我也不特别推荐这样做。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1228176|WITH+myconstants+(var1,+var2)+as+(
+++values+(5,+'foo')
)
SELECT+*
FROM+somewhere,+myconstants
WHERE+something+=+var1
+++OR+something_else+=+var2;|code-block|syntax|javascript|1228177|entityMap|0|LINK|mutability|MUTABLE|url|https://www.postgresql.org/docs/current/static/queries-with.html^0|5|4|5|B|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@$9|V|A|W|1|X]]|E|$]]|$1|F|3|G|5|H|7|Y|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Z|8|@]|D|@]|E|$]]]|L|$M|$5|N|O|P|E|$Q|R]]]]

I accomplished the same goal by using a <a href="https://www.postgresql.org/docs/current/static/queries-with.html" rel="noreferrer"><code>WITH</code> clause</a>, 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.

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

blocks|key|1228099|text|在pl/PgSQL之外使用临时表|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1228100|除了使用pl/pgsql或建议的其他pl/*语言之外，这是我能想到的唯一其他可能性。|1228101|begin;
select+5::int+as+var+into+temp+table+myvar;
select+*
++from+somewhere+s,+myvar+v
+where+s.something+=+v.var;
commit;|code-block|syntax|javascript|1228102|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

<h1>Using a Temp Table outside of pl/PgSQL</h1>

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

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

blocks|key|1443125|text|我想提出一个对@DarioBarrionuevo's+answer的改进，使它更简单地利用临时表。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1443126|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;|code-block|syntax|javascript|1443127|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/12487075/505893^0|7|Q|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

I want to propose an improvement to <a href="https://stackoverflow.com/a/12487075/505893">@DarioBarrionuevo's answer</a>, to make it simpler leveraging temporary tables.

<pre><code>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;
</code></pre>

blocks|key|1443303|text|确实，没有生动而明确的方法来声明单值变量，您可以做的是|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1443304|with+myVar+as+(select+"any+value+really")|code-block|syntax|javascript|1443305|然后，要访问存储在此构造中的值，您需要|1443306|(select+*+from+myVar)|1443307|例如|1443308|with+var+as+(select+123)++++
...+where+id+=+(select+*+from+var)|1443309|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

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

<pre><code>with myVar as (select "any value really")
</code></pre>

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

<pre><code>(select * from myVar)
</code></pre>

for example

<pre><code>with var as (select 123) 
... where id = (select * from var)
</code></pre>

blocks|key|1443212|text|下面是一个使用PREPARE+statements的示例。您仍然不能使用?，但您可以使用$n表示法：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1443213|PREPARE+foo(integer)+AS
++++SELECT++*
++++FROM++++somewhere
++++WHERE+++something+=+$1;
EXECUTE+foo(5);
DEALLOCATE+foo;|code-block|syntax|javascript|1443214|entityMap|0|LINK|mutability|MUTABLE|url|https://www.postgresql.org/docs/current/static/sql-prepare.html^0|10|1|18|2|7|I|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]|$9|V|A|W|B|C]]|D|@$9|X|A|Y|1|Z]]|E|$]]|$1|F|3|G|5|H|7|10|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|11|8|@]|D|@]|E|$]]]|L|$M|$5|N|O|P|E|$Q|R]]]]

Here is an example using <a href="https://www.postgresql.org/docs/current/static/sql-prepare.html" rel="noreferrer">PREPARE statements</a>. You still can't use <code>?</code>, but you can use <code>$n</code> notation:

<pre><code>PREPARE foo(integer) AS
 SELECT *
 FROM somewhere
 WHERE something = $1;
EXECUTE foo(5);
DEALLOCATE foo;
</code></pre>

blocks|key|1443345|text|你可以求助于工具的特殊功能。就像for+DBeaver自己的专有语法：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1443346|@set+name+=+'me'
SELECT+:name;
SELECT+${name};

DELETE+FROM+book+b
WHERE+b.author_id+IN+(SELECT+a.id+FROM+author+AS+a+WHERE+a.name+=+:name);|code-block|syntax|javascript|1443347|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You may resort to tool special features. Like for DBeaver own proprietary syntax:

<pre><code>@set name = 'me'
SELECT :name;
SELECT ${name};

DELETE FROM book b
WHERE b.author_id IN (SELECT a.id FROM author AS a WHERE a.name = :name);
</code></pre>

blocks|key|1443457|text|在DBeaver中，您可以在查询中使用参数，就像在代码中使用参数一样，所以这是可行的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1443458|SELECT+*
FROM+somewhere
WHERE+something+=+:myvar|code-block|syntax|javascript|1443459|当您运行查询时，DBeaver将要求您提供:myvar的值并运行查询。|1443460|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

In DBeaver you can use parameters in queries just like you can from code, so this will work:

<pre><code>SELECT *
FROM somewhere
WHERE something = :myvar
</code></pre>

When you run the query DBeaver will ask you for the value for :myvar and run the query.

blocks|key|1228564|text|这是一个在postges终端中使用普通变量的代码段。我已经用过几次了。但需要想出一个更好的方法。这里我使用的是字符串变量。使用整型变量，你不需要三重引号。三重引号在查询时变为单引号；否则将出现语法错误。在使用字符串变量时，可能有一种方法可以消除对三重引号的需要。如果您找到改进的方法，请更新。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1228565|\set+strainname+'''B.1.1.7'''

select+*
from+covid19strain
where+name+=+:strainname+;|code-block|syntax|javascript|1228566|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Here is a code segment using plain variable in postges terminal. I have used it a few times. But need to figure a better way. Here I am working with string variable. Working with integer variable, you don't need the triple quote. Triple quote becomes single quote at query time; otherwise you got syntax error. There might be a way to eliminate the need of triple quote when working with string variables. Please update if you find a way to improve.
<pre><code>\set strainname '''B.1.1.7'''

select *
from covid19strain
where name = :strainname ;
</code></pre>

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

In MS SQL Server I can do this:

<pre><code>DECLARE @myvar INT
SET @myvar = 5

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

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:

<pre><code>myvar INTEGER;
</code></pre>

Could someone give me an example of the correct syntax?

How to declare a variable in a PostgreSQL query

如何声明在PostgreSQL 8.3查询中使用的变量？在MS SQL Server中，我可以这样做：DECLARE @myvar INTSET @myvar = 5SELECT *FROM somewhereWHERE something = @myvar如何在PostgreSQL中执行相同的操作？根据文档，变量被简单地声明为"name type;"，但这给了我一个语法错误：myvar INTE

问如何在PostgreSQL查询中声明变量
EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在PostgreSQL查询中声明变量EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在PostgreSQL查询中声明变量
EN