我如何在PostgreSQL 8.3查询中声明一个变量?
在MS SQL Server我可以这样做:
DECLARE @myvar INT
SET @myvar = 5
SELECT *
FROM somewhere
WHERE something = @myvar
我如何在PostgreSQL做同样的事情?根据文档,变量被简单地声明为“name type;”,但这给了我一个语法错误:
myvar INTEGER;
谁能给我一个正确语法的例子吗?
我如何在PostgreSQL 8.3查询中声明一个变量?
在MS SQL Server我可以这样做:
DECLARE @myvar INT
SET @myvar = 5
SELECT *
FROM somewhere
WHERE something = @myvar
我如何在PostgreSQL做同样的事情?根据文档,变量被简单地声明为“name type;”,但这给了我一个语法错误:
myvar INTEGER;
谁能给我一个正确语法的例子吗?
当前回答
这取决于你的客户。
然而,如果你正在使用psql客户端,那么你可以使用以下方法:
my_db=> \set myvar 5
my_db=> SELECT :myvar + 1 AS my_var_plus_1;
my_var_plus_1
---------------
6
如果你使用文本变量,你需要引用。
\set myvar 'sometextvalue'
select * from sometable where name = :'myvar';
其他回答
此解决方案基于fei0x提出的解决方案,但它的优点是不需要在查询中加入常量的值列表,并且可以在查询开始时轻松列出常量。它也适用于递归查询。
基本上,每个常量都是在WITH子句中声明的单值表,然后可以在查询的其余部分的任何地方调用它。
包含两个常量的基本示例:
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)
或者,你可以使用SELECT * FROM constant_name代替TABLE constant_name,这对于与postgresql不同的其他查询语言可能无效。
PostgreSQL中没有这样的特性。您只能在pl/PgSQL(或其他pl/*)中这样做,但不能在纯SQL中这样做。
一个例外是WITH()查询,它可以作为一个变量,甚至是变量的元组。它允许您返回临时值的表。
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) > ...;
下面是一个使用PREPARE语句的例子。你仍然不能使用?,但你可以使用$n符号:
PREPARE foo(integer) AS
SELECT *
FROM somewhere
WHERE something = $1;
EXECUTE foo(5);
DEALLOCATE foo;
你也可以在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;
以上要求Postgres 9.0或更高版本。
在psql中,你可以使用这些“变量”作为宏。请注意,它们在每次使用时都被“计算”,而不是在“设置”时。
简单的例子:
\set my_random '(SELECT random())'
select :my_random; -- gives 0.23330629315990592
select :my_random; -- gives 0.67458399344433542
这样每次都会给出两个不同的答案。
但是,您仍然可以使用这些有价值的简写来避免重复大量的子选择。
\set the_id '(SELECT id FROM table_1 WHERE name = ''xxx'' LIMIT 1)'
然后在以后的查询中使用它
:the_id
e.g.
INSERT INTO table2 (table1_id,x,y,z) VALUES (:the_id, 1,2,3)
注意,你必须对变量中的字符串加双引号,因为整件事都是字符串插值(即宏展开)到你的查询中。