在 plpgsql 声明块中使用 \set 变量

本文介绍了在 plpgsql 声明块中使用 \set 变量的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我的 SQL 脚本包含以下内容:

\set test 'some value'做 $$DECLAREv_test text:= :'test';开始RAISE NOTICE 'test var is %',v_test;结束$$;

在尝试评估 test 的值时出现语法错误:

错误::"处或附近的语法错误

理想情况下，我希望有一个匿名 plpqsql 块存在于一个文件中，然后使用一组环境变量从 shell 脚本中调用该块

解决方案

解释是，根据手册:

不会在引用的 SQL 文字和标识符内执行变量插值.

DO 语句的主体是一个用美元引用的字符串.所以字符串内部没有插值.

由于它必须是文字字符串，因此您也不能即时连接字符串.手册:

这必须指定为字符串文字，就像在 CREATE FUNCTION 中一样.

但是你可以连接字符串然后执行它.

\set [ name [ value [ ... ] ] ]

将 psql 变量名称设置为 value，或者 如果有多个值给定，将它们串联起来.

大胆强调我的.你只需要正确引用:

test=# \set test 'some value'test=# \set code 'DECLARE v_test text := ' :'test' ';BEGIN RAISE NOTICE ''test var is: %'', v_test;结尾'测试=#做:'代码'；注意:测试变量是:一些值做测试=#

但我宁愿创建一个(临时)函数并将值作为参数传递(在 psql 插值工作的地方).dba.SE 上相关答案中的详细信息:

• 使用表达式创建序列参数的 psql 变量

My SQL script contains the following:

\set test 'some value' DO $$DECLARE v_test text:= :'test'; BEGIN RAISE NOTICE 'test var is %',v_test; END$$;

I get a syntax error when trying to evaluate the value of test:

ERROR: syntax error at or near ":"

Ideally I'd like to have an anonymous plpqsql block living in a file which will then get called from a shell script using a set of environment variables

解决方案

The explanation is, according to the manual:

Variable interpolation will not be performed within quoted SQL literals and identifiers.

The body of the DO statement is a dollar-quoted string. So no interpolation inside the string.

Since it must be a literal string, you can also not concatenate strings on the fly. The manual:

This must be specified as a string literal, just as in CREATE FUNCTION.

But you can concatenate the string and then execute it.

\set [ name [ value [ ... ] ] ]

Sets the psql variable name to value, or if more than one value is given, to the concatenation of all of them.

Bold emphasis mine. You just have to get the quoting right:

test=# \set test 'some value' test=# \set code 'DECLARE v_test text := ' :'test' '; BEGIN RAISE NOTICE ''test var is: %'', v_test; END' test=# DO :'code'; NOTICE: test var is: some value DO test=#

But I would rather create a (temporary) function and pass the value as parameter (where psql interpolation works). Details in this related answer on dba.SE:

• CREATE SEQUENCE using expressions with psql variables for parameters