主页 / dba / 问题 / 302794
Accepted
Jakub Holý
Asked: 2021-11-18 13:16:31 +0800 CST

如何在 plpgsql 过程中参数化 DDL？

  • 772

如何在 plpgsql 过程中参数化 DDL 语句？也就是说，我想使用org_label参数作为角色和模式名称，但下面的代码从字面上理解它，我最终得到了角色“org_label”：

create or replace procedure create_org_schema (org_label varchar(63)) -- max identifier len in pgsql is 63
language plpgsql AS $$ BEGIN
  create role org_label IN GROUP organization;
  GRANT org_label TO ardoq_api_customers; -- so that AUTHORIZATION... below works
  CREATE SCHEMA AUTHORIZATION org_label;
  SET ROLE org_label;
  GRANT USAGE on SCHEMA org_label to ardoq_api_customers; -- so that we can list it
  RESET ROLE;
END $$;

我试图将第一行更改为：

EXECUTE 'create role $1 IN GROUP organization' USING org_label;

但这失败了：

ERROR:  syntax error at or near "$1" at character 13
QUERY:  create role $1 IN GROUP organization
stored-procedures postgresql

1 个回答

  1. Best Answer

    你不能。手册：

    参数符号的另一个限制是它们仅适用于 SELECT、INSERT、UPDATE和DELETE命令。在其他语句类型（通常称为实用程序语句）中，您必须以文本方式插入值，即使它们只是数据值。

    EXECUTE .. USING不适用于此。您必须在执行之前连接查询字符串。通常，format()最方便的是安全地做到这一点：

    CREATE OR REPLACE PROCEDURE create_org_schema (org_label varchar(63)) -- max identifier len in pgsql is 63
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format ('CREATE ROLE %I IN GROUP organization', org_label);
   -- ...
END
$func$;

    请注意使用格式说明符%I来正确引用标识符并防御其他可能的 SQL 注入。使用合法的小写名称让您的 Postgres 生活简单。

    看：

    • 1

相关问题