问题描述
如何声明要在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查询中声明变量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!