PostgreSQL函数中是否存在psql变量的转义语法？

问题描述

我编写PSQL脚本并使用变量（用于 psql --variable key = value 命令行语法）。

I write PSQL script and using variables (for psql --variable key=value commandline syntax).

此功能非常适用于top级别范围，例如 select * from：key ，但我使用脚本创建函数，并且需要在其中包含变量值。

This works perfectly for top level scope like select * from :key, but I create functions with the script and need variable value inside them.

因此，语法类似于

create function foo() returns void as
$$
declare
begin
    grant select on my_table to group :user;
end;
$$
language plpgsql;

在：user 处失败。

据我了解，psql变量是普通的宏替换功能，但它不处理函数体。
这种情况​​下是否有任何转义语法？在：user 周围加上 $$ 可以进行替换，但是psql在 $$ 处失败。

As far as I understand psql variables is a plain macro substitution feature, but it doesn't process function bodies.Are there any escaping syntax for such cases? Surrounding :user with $$ works regarding substitution, but psql fails at $$.

除了独立的宏处理（sed，awk等）之外，还有其他方法吗？

Are there any other way to do this besides standalone macro processing (sed, awk, etc)?

推荐答案

PSQL SET 变量不会在用美元括起来的字符串内插值。我不确定这一点，但我认为在其中打开 SET 变量插值方法没有逃脱或其他窍门。

PSQL SET variables aren't interpolated inside dollar-quoted strings. I don't know this for certain, but I think there's no escape or other trickery to turn on SET variable interpolation in there.

一个人可能认为您可以在两个用美元引用的PL / pgSQL片段之间插入一个未引用的：user 以获得所需的效果。但这似乎不起作用...我认为语法只需要一个字符串，而不是一个将字符串连接在一起的表达式。

One might think you could wedge an unquoted :user between two dollar-quoted stretches of PL/pgSQL to get the desired effect. But this doesn't seem to work... I think the syntax requires a single string and not an expression concatenating strings. Might be mistaken on that.

无论如何，没关系。还有另一种方法（如Pasco所述）：编写存储过程以接受PL / pgSQL参数。

Anyway, that doesn't matter. There's another approach (as Pasco noted): write the stored procedure to accept a PL/pgSQL argument. Here's what that would look like.

CREATE OR REPLACE FUNCTION foo("user" TEXT) RETURNS void AS
$$
BEGIN
        EXECUTE 'GRANT SELECT ON my_table TO GROUP ' || quote_ident(user);
END;    
$$ LANGUAGE plpgsql;

此功能的注意事项：

1. EXECUTE 使用我们的过程参数在每次调用时生成一个适当的 GRANT 。 PG手册部分称为 详细说明了执行。


2. 过程参数<$ c $的声明c> user 必须用双引号引起来。

1. EXECUTE generates an appropriate GRANT on each invocation using on our procedure argument. The PG manual section called "Executing Dynamic Commands" explains EXECUTE in detail.
2. The declaration of procedure argument user must be double quoted. Double quotes force it to be interpreted as an identifier.

一旦定义了这样的函数，就可以使用内插的PSQL变量来调用它。这是一个大纲。

Once you define the function like this, you can call it using interpolated PSQL variables. Here's an outline.

1. 运行 psql --variable user ='whoever' --file = myscript。 sql 。


2. 在myscript.sql中，定义上述函数。


3. 在myscript.sql中，将选择foo（：user）; 。在这里，我们依靠我们在 user 的值中加上单引号的方式。

1. Run psql --variable user="'whoever'" --file=myscript.sql. Single quotes are required around the username!
2. In myscript.sql, define function like above.
3. In myscript.sql, put select foo(:user);. This is where we rely on those single quotes we put in the value of user.

尽管这似乎行得通，但它使我感到不安。我认为 SET 变量用于运行时配置。在 SET 中传送数据似乎很奇怪。

Although this seems to work, it strikes me as rather squirrely. I thought SET variables were intended for runtime configuration. Carrying data around in SET seems odd.

编辑：这是一个具体的原因不使用 SET 变量。在联机帮助页中：这些分配是在启动的非常早期阶段完成的，因此保留给内部使用的变量可能会在以后被覆盖。如果Postgres决定使用名为 user 的变量（或您选择的任何变量），它可能会用您原本不希望的内容覆盖脚本参数。实际上，psql本身已经具有 USER 了-这只能起作用，因为 SET 区分大小写。从一开始这几乎破坏了一切！

Edit: here's a concrete reason to not use SET variables. From the manpage: "These assignments are done during a very early stage of startup, so variables reserved for internal purposes might get overwritten later." If Postgres decided to use a variable named user (or whatever you pick), it could overwrite your script argument with something you never intended. In fact, psql already takes USER for itself -- this only works because SET is case sensitive. This very nearly broke things from the start!

这篇关于PostgreSQL函数中是否存在psql变量的转义语法？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！