In Microsoft SQL Server, to test something like this in the query window:
在Microsoft SQL Server中,要在查询窗口中测试类似的内容:
select * from Users where LastName = @lastname
I can add this before the command:
我可以在命令之前添加这个:
declare @lastname varchar(16)
set @lastname = 'Troy'
But in PostgreSQL, I cannot find a similar way to do so. It seems the only thing I can do is to replace the parameter name directly with its value. It gets hard when the ad-hoc query gets complicated and the same parameter gets used several times. Is there a way?
但是在PostgreSQL中,我找不到类似的方法。似乎我唯一能做的就是直接用它的值替换参数名称。当ad-hoc查询变得复杂并且多次使用相同的参数时,它变得很难。有办法吗?
3 个解决方案
#1
Various options.
Provide parameters in a CTE to have "variables" in pure SQL:
在CTE中提供参数以在纯SQL中包含“变量”:
WITH var(lastname) AS (SELECT 'Troy'::varchar(16))
SELECT *
FROM users, var v
WHERE lastname = v.lastname;This works for any query.
Since the CTE var holds a single row it is safe to append it with a CROSS JOIN at the end of the FROM clause - actually the short form with appending it after a comma may be best because explicit join syntax binds before commas. The additional table alias v is optional to further shorten the syntax.
这适用于任何查询。由于CTE var包含单行,因此可以安全地在FROM子句的末尾附加CROSS JOIN - 实际上,在逗号之后附加它的简短形式可能是最好的,因为显式连接语法在逗号之前绑定。附加表别名v是可选的,以进一步缩短语法。
OR cheaper without CTE. BTW, why varchar(16)? Just use text:
或者没有CTE便宜。 BTW,为什么varchar(16)?只需使用文字:
SELECT *
FROM users
JOIN (SELECT 'Troy'::text) var(lastname) USING (lastname);
Or use a temporary table to play a similar role for all queries within the same session. Temp tables die with the end of the session.
或者使用临时表为同一会话中的所有查询播放类似的角色。临时表在会话结束时死亡。
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);
- About temporary tables and
autovacuum
关于临时表和autovacuum
Or you can use DO statements like @Houari supplied or like demonstrated here:
或者你可以使用像@Houari这样的DO语句,或者像这里演示的那样:
- PostgreSQL loops outside functions. Is that possible?
PostgreSQL在函数外部循环。那可能吗?
Note that you cannot return values from DO statements. (You can use RAISE ... though.) And you cannot use SELECT without target in plpgsql - the default procedural language in a DO statement. Replace SELECT with PERFORM to throw away results.
请注意,您不能从DO语句返回值。 (你可以使用RAISE ......但是你不能在plpgsql中使用SELECT而没有target - 这是DO语句中的默认过程语言。用PERFORM替换SELECT以丢弃结果。
Or you can use customized options, which you can set in postgresql.conf to be visible globally.
或者您可以使用自定义选项,您可以在postgresql.conf中设置全局可见。
Or set in your session to be visible for the duration of the session and only in the same session:
或者在会话中设置在会话期间可见,并且仅在同一会话中显示:
SET my.lastname = 'Troy';
The variable name must include a dot. You are limited to text as data type this way, but any data type can be represented as text ...
变量名称必须包含一个点。这种方式限制为文本作为数据类型,但任何数据类型都可以表示为文本...
You can use current_setting('my.lastname') as value expression. Cast if you need. For example: current_setting('my.json_var')::json ...
您可以使用current_setting('my.lastname')作为值表达式。如果需要,可以投射。例如:current_setting('my.json_var'):: json ...
Or use SET LOCAL for the effect to only last for the current transaction.
或者使用SET LOCAL使效果仅持续当前事务。
Related answer by @Craig:
@Craig的相关回答:
- Passing user id to PostgreSQL triggers
将用户标识传递给PostgreSQL触发器
Or you can use tiny IMMUTABLE functions as global persisted variables that only privileged users can manipulate:
或者您可以使用微小的IMMUTABLE函数作为全局持久变量,只有特权用户才能操作:
- Is there a way to define a named constant in a PostgreSQL query?
有没有办法在PostgreSQL查询中定义命名常量?
This related answer also has a similar list of options.
这个相关的答案也有类似的选项列表。
psql
Has \set or \gset meta-commands and provides variable substitution in the client.
具有\ set或\ gset元命令并在客户端中提供变量替换。
pgAdmin
The pgAdmin query window offers the extension pgScript.
pgAdmin查询窗口提供扩展名pgScript。
#2
Perhaps using the DO instruction to simulate a function. In that way you can declare variables and use them to execute your query!
也许使用DO指令来模拟一个函数。通过这种方式,您可以声明变量并使用它们来执行查询!
#3
I config postgres to log all the commands, and copy the command from the log file, so all the parameters are already replaced with the value, and test the command in the query window.
我配置postgres来记录所有命令,并从日志文件中复制命令,因此所有参数都已被值替换,并在查询窗口中测试命令。
May not be the best approach, but it is easy and works for me
可能不是最好的方法,但它很容易,对我有用
#1
Various options.
Provide parameters in a CTE to have "variables" in pure SQL:
在CTE中提供参数以在纯SQL中包含“变量”:
WITH var(lastname) AS (SELECT 'Troy'::varchar(16))
SELECT *
FROM users, var v
WHERE lastname = v.lastname;This works for any query.
Since the CTE var holds a single row it is safe to append it with a CROSS JOIN at the end of the FROM clause - actually the short form with appending it after a comma may be best because explicit join syntax binds before commas. The additional table alias v is optional to further shorten the syntax.
这适用于任何查询。由于CTE var包含单行,因此可以安全地在FROM子句的末尾附加CROSS JOIN - 实际上,在逗号之后附加它的简短形式可能是最好的,因为显式连接语法在逗号之前绑定。附加表别名v是可选的,以进一步缩短语法。
OR cheaper without CTE. BTW, why varchar(16)? Just use text:
或者没有CTE便宜。 BTW,为什么varchar(16)?只需使用文字:
SELECT *
FROM users
JOIN (SELECT 'Troy'::text) var(lastname) USING (lastname);
Or use a temporary table to play a similar role for all queries within the same session. Temp tables die with the end of the session.
或者使用临时表为同一会话中的所有查询播放类似的角色。临时表在会话结束时死亡。
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);
- About temporary tables and
autovacuum
关于临时表和autovacuum
Or you can use DO statements like @Houari supplied or like demonstrated here:
或者你可以使用像@Houari这样的DO语句,或者像这里演示的那样:
- PostgreSQL loops outside functions. Is that possible?
PostgreSQL在函数外部循环。那可能吗?
Note that you cannot return values from DO statements. (You can use RAISE ... though.) And you cannot use SELECT without target in plpgsql - the default procedural language in a DO statement. Replace SELECT with PERFORM to throw away results.
请注意,您不能从DO语句返回值。 (你可以使用RAISE ......但是你不能在plpgsql中使用SELECT而没有target - 这是DO语句中的默认过程语言。用PERFORM替换SELECT以丢弃结果。
Or you can use customized options, which you can set in postgresql.conf to be visible globally.
或者您可以使用自定义选项,您可以在postgresql.conf中设置全局可见。
Or set in your session to be visible for the duration of the session and only in the same session:
或者在会话中设置在会话期间可见,并且仅在同一会话中显示:
SET my.lastname = 'Troy';
The variable name must include a dot. You are limited to text as data type this way, but any data type can be represented as text ...
变量名称必须包含一个点。这种方式限制为文本作为数据类型,但任何数据类型都可以表示为文本...
You can use current_setting('my.lastname') as value expression. Cast if you need. For example: current_setting('my.json_var')::json ...
您可以使用current_setting('my.lastname')作为值表达式。如果需要,可以投射。例如:current_setting('my.json_var'):: json ...
Or use SET LOCAL for the effect to only last for the current transaction.
或者使用SET LOCAL使效果仅持续当前事务。
Related answer by @Craig:
@Craig的相关回答:
- Passing user id to PostgreSQL triggers
将用户标识传递给PostgreSQL触发器
Or you can use tiny IMMUTABLE functions as global persisted variables that only privileged users can manipulate:
或者您可以使用微小的IMMUTABLE函数作为全局持久变量,只有特权用户才能操作:
- Is there a way to define a named constant in a PostgreSQL query?
有没有办法在PostgreSQL查询中定义命名常量?
This related answer also has a similar list of options.
这个相关的答案也有类似的选项列表。
psql
Has \set or \gset meta-commands and provides variable substitution in the client.
具有\ set或\ gset元命令并在客户端中提供变量替换。
pgAdmin
The pgAdmin query window offers the extension pgScript.
pgAdmin查询窗口提供扩展名pgScript。
#2
Perhaps using the DO instruction to simulate a function. In that way you can declare variables and use them to execute your query!
也许使用DO指令来模拟一个函数。通过这种方式,您可以声明变量并使用它们来执行查询!
#3
I config postgres to log all the commands, and copy the command from the log file, so all the parameters are already replaced with the value, and test the command in the query window.
我配置postgres来记录所有命令,并从日志文件中复制命令,因此所有参数都已被值替换,并在查询窗口中测试命令。
May not be the best approach, but it is easy and works for me
可能不是最好的方法,但它很容易,对我有用