如果未在命令行中指定，则仅在 psql-script 中设置变量 [英] only set variable in psql-script if not specified on the command-line

问题描述

我想给 option 以在命令行上为我的 psql 脚本指定一些变量.

I want to give the the option to specify some variables on the command-line for my psql-scripts with.

 psql -v myVar=myValue

但是我发现无法在 sql-script 本身中为这些变量提供默认值.语法:

However I found no way to give those variables default values within the sql-script itself. The syntax:

`\set MyVar defaultValue` 

无条件覆盖在 psql 命令行上指定的值 myValue.

overwrites the value myValue specified on the psql command-line unconditionally.

有没有办法检查psql中是否设置了变量?

Is there any way to check if a variable is set within psql?

推荐答案

psql 不支持为变量设置默认值，但您可以使用变通方法来完成.

Setting default values for variables is not supported in psql, but you can do it using a workaround.

这个 psql 命令:

This psql command:

\set myVar :myVar

如果变量已经通过 psql 设置(这意味着 myVar 再次设置为他的值)，则什么都不做，否则变量被设置为字符串 :myVar.

does nothing if the variable was already set via psql (this means, myVar is set again to his value), otherwise the variable is set literally to the string :myVar.

使用这种情况和其他 psql 命令 \gset，您实际上可以为 myVar 设置默认值.把它放在你的 sql 脚本的顶部:

Using this circumstance and the other psql command \gset, you can actually set a default value for myVar. Put this on the top of you sql script:

\set myVar :myVar
-- now myVar is set to the string ':myVar' if was not already set.
-- Checking it using a CASE statement:
SELECT CASE 
  WHEN :'myVar'= ':myVar'
  THEN 'default_value' 
  ELSE :'myVar' 
END AS "myVar"  \gset -- < \gset call at end of the query

它似乎只适用于文本变量，但如果你需要数字变量，你可以转换为数字:

It seems to work only with text variables, but you can cast to numeric if you need numeric variables:

SELECT CASE 
  WHEN :'myVar'= ':myVar'
  THEN '10' 
  ELSE :'myVar' 
END::numeric AS "myVar"  \gset

\gset 如何工作:

\gset 允许您根据选择查询的结果设置变量.结果变量的命名与列名一样，这就是为什么您需要在查询末尾使用子句 AS "myVar" (如果要使用变量名，请不要忘记双引号)大写字母).

How does \gset work:

\gset allows you to set a variable from the result of a select query. The result variable is named like the column name, this is why you need the clause AS "myVar" at the end of the query (don't forget the double quotes if you want to use variables names with uppercase letters).

例如命令:

SELECT 'hello' AS var1 \gset

设置变量var1为hello，同

\set var1 hello

请参阅此处了解更多信息:http://www.postgresql.org/docs/9.4/static/app-psql.html

See here for more infos: http://www.postgresql.org/docs/9.4/static/app-psql.html

这篇关于如果未在命令行中指定，则仅在 psql-script 中设置变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！