postgresql - plpgsql 函数中的变量替换

Question

为了能够在一个数据库中创建应用程序的不同实例，例如测试阶段，我想使用变量作为模式名称。创建表或序列时一切正常，但我想不出一种在函数内部使用变量的优雅方法。我已经阅读了一些文章关于美元引用的字符串常量，但我仍然希望找到解决方案。

目前正在运行 PostgreSQL 11。

代码如下:

按预期工作，将 schema_name 设置为 sc 并在 DDL 期间使用该变量:

\set schema_name sc

CREATE SEQUENCE :schema_name.S_VT_KEY INCREMENT BY 1 START WITH 1 CACHE 1 NO CYCLE OWNED BY NONE;

create table :schema_name.vartest (
  VT_KEY   BIGINT,
  DESCRIPTION TEXT);

现在函数:

create or replace function :schema_name.fvartest(
  p_desc  TEXT)
  RETURNS bigint
  language 'plpgsql'
  COST 100.0
  VOLATILE NOT LEAKPROOF
AS $function$
DECLARE
  p_key bigint;
BEGIN

  select nextval('sc.S_VT_KEY') into p_key; -- (1)

  insert into sc.vartest (VT_KEY, DESCRIPTION) -- (2)
      values (p_key, p_desc);

  return p_key;

END $function$;

案例(1)我想使用类似的东西:

  select nextval(:schema_name || '.S_VT_KEY') into p_key;

案例(2)

  insert into :schema_name.vartest (VT_KEY, DESCRIPTION) -- (2)

到目前为止，任何变体都会呈现错误消息:

直接使用:

select nextval(:schema_name || '.S_VT_KEY') into p_key; 

呈现语法错误

psql:test/testcase001.sql:29: ERROR:  syntax error at or near ":"
LINE 12:  select nextval(:schema_name || '.S_VT_KEY') into p_key; -- ...

转义，编译但不运行:

select nextval($e$:schema_name$e$ || '.S_VT_KEY') into p_key; -- (1)

CONTEXT:  SQL statement "select nextval($e$:schema_name$e$ || '.S_VT_KEY')"

几乎就在那里，使用相同的定界符，替换变量但确实呈现语法错误:

select nextval($function$ :'schema_name' $function$ || '.S_VT_KEY') into p_key; -- (1)

LINE 12:  select nextval($function$ 'sc' $function$ || '.S_VT_KEY')

也许 PostgreSQL 12 有办法？

Answer 1

我想出了一个目前可行的解决方案，但目前还不够优雅。我正在使用 bash 变量替换，它似乎适用于上述两种情况:

#!/bin/bash

export schema_name="sc"

psql postgresql://<user>:<pwd>@<host>/<db>  << EOF


create or replace function ${schema_name}.fvartest(
  p_desc  TEXT)
  RETURNS bigint
  language 'plpgsql'
  COST 100.0
  VOLATILE NOT LEAKPROOF
AS \$function\$
DECLARE
    p_key bigint;
BEGIN

    select nextval('${schema_name}.S_VT_KEY') into p_key; -- (1)

    insert into ${schema_name}.vartest (VT_KEY, DESCRIPTION) -- (2)
        values (p_key, p_desc);

    return p_key;

END \$function\$;

    select ${schema_name}.fvartest('bla bla');
    select * from ${schema_name}.vartest;

EOF

但现在我必须将代码放入 bash 脚本中并转义 $function$ Postges 风格的 $$ 符号。也许还有更好的方法，很想听听您的意见。