如何在Postgres查询窗口中测试带参数的临时SQL？

Question

在 Microsoft SQL Server 中，要在查询窗口中测试类似这样的内容：

select * from Users where LastName = @lastname

我可以在命令前面添加这个：

declare @lastname varchar(16)
set @lastname = 'Troy'

但是在PostgreSQL中，我找不到类似的方法。似乎我唯一能做的就是直接用它的值替换参数名。当临时查询变得复杂，并且同一个参数被使用多次时，这样做会变得很困难。有没有其他方法呢？

- Carol

---

顺便问一下：“Postgres查询窗口”？你是指pgAdmin查询窗口吗？ - Erwin Brandstetter

我也习惯了 MSSQL 的工作流程，将参数化查询复制到查询运行器中，在其上方设置声明，然后点击运行。这样我就不会在修改查询以进行测试的过程中错误地改变查询语义。令人失望的是，在 postgres 中没有办法做到这一点。必须始终更改参数化查询（至少更改变量名称或插入转换），以进行测试并冒着无效的测试结果的风险。 - Kasey Speakman

MySQL中也存在工作流，使用SET @var = 123。https://dev59.com/Mmgt5IYBdhLWcg3w3xPC。我知道我之前在MSSQL中使用过它。 - Kasey Speakman

Answer 1

各种选项。

在CTE中提供参数，以在纯SQL中具有“变量”：

WITH var(lastname) AS (SELECT 'Troy'::varchar(16))
SELECT *
FROM   users<b>, var v</b>
WHERE  lastname = <b>v.lastname</b>;

这适用于任何查询。
由于CTE var仅包含单个行，因此在FROM子句末尾添加CROSS JOIN是安全的 - 实际上，在逗号后追加它的短形式可能是最好的，因为显式连接语法在逗号之前绑定。另一个表别名v是可选的，以进一步缩短语法。

或者如果没有CTE更便宜。顺便问一下，为什么使用varchar(16)？只需使用text即可：

SELECT *
FROM   users
JOIN  (SELECT 'Troy'::text) var(lastname) USING (lastname)
WHERE  lastname = var.lastname;

或者使用一个临时表来在同一会话中为所有查询扮演类似的角色。临时表会随着会话结束而消失。

CREATE TEMP TABLE var AS
SELECT text 'Troy' AS lastname;

ANALYZE var;  -- temp tables are not covered by autovacuum

SELECT * FROM users JOIN var USING (lastname);

• 关于临时表和autovacuum

或者您可以像@Houari提供的那样使用DO语句，或者像这里演示的那样：

• PostgreSQL函数外的循环。是否可能？

请注意，您不能从DO语句中返回值。（不过您可以使用RAISE ...）。而且在plpgsql - DO语句中的默认过程语言中，不能使用没有目标的SELECT。将SELECT替换为PERFORM以丢弃结果。

或者您可以使用自定义选项，您可以在postgresql.conf中进行设置，以在全局中可见。

或者在会话中设置以在会话期间对同一会话可见：

SET my.lastname = 'Troy';

变量名称必须包括一个点。这种方式限制了数据类型为text，但所有数据类型都可以表示为text…您可以将current_setting('my.lastname')用作值表达式。如果需要，进行转换。例如：current_setting('my.json_var')::json…或者使用SET LOCAL使效果仅在当前事务中持续。请参见：Passing user id to PostgreSQL triggers 或者您可以使用微小的IMMUTABLE函数作为只有特权用户才能操作的全局持久变量。请参见：Is there a way to define a named constant in a PostgreSQL query? 或者在使用psql客户端时，使用\set或\gset元命令和变量替换。

- Erwin Brandstetter

---

临时表选项给我带来了很多麻烦。它是一个隐式连接，需要在更新子句中使用FROM，这使得运行时间长达几分钟。 - Noumenon

@Noumenon：请注意关于临时表和自动清理的添加部分。 - Erwin Brandstetter

Answer 2

也许可以使用DO指令来模拟函数。这样，您就可以声明变量并使用它们来执行查询！

http://www.postgresql.org/docs/9.4/static/sql-do.html

- Houari

---

我尝试了，但总是出现SQL错误：ERROR: query has no destination for result data SQL state: 42601 提示：如果您想丢弃SELECT的结果，请改用PERFORM。 上下文：PL/pgSQL function inline_code_block line 5 at SQL statement DO $$ DECLARE startDate Timestamp; BEGIN startDate := '2014-4-1'; select pid from grouping_horizontal where dt = startDate; END $$; - Carol

@Carol：提示已经说明了一切：在DO语句中使用关键字PERFORM而不是SELECT。你不能在DO语句中没有目标地进行SELECT，也不能返回行... - Erwin Brandstetter

Answer 3

我配置了Postgres以记录所有命令，并从日志文件中复制命令，因此所有参数已经替换为值，并在查询窗口中测试该命令。

这可能不是最佳方法，但对我来说很简单且有效。

- Carol