PostgreSQL触发器动态生成多表代码 [英] PostgreSQL trigger to generate codes for multiple tables dynamically

问题描述

我想为数据库中的许多表生成代码，并在我准备好编写获取表 X 的代码"的第三个实现时停下来重构我的解决方案.

我的代码是这样的:

-- 租户收到一个代码，该代码由其子域的一部分和一个唯一编号组成.-- 这个数字来自这个序列.创建序列tenant_codes_seq MAXVALUE 9999 无循环；创建表租户(子域 varchar(36) NOT NULL UNIQUE,tenant_code char(8) NOT NULL UNIQUE, PRIMARY KEY (tenant_code));-- 这个函数需要四个参数:-- 1. 接收生成代码的列(RECEIVING_COLUMN_NAME)-- 2. 用于为代码加盐的列 (SALT_COLUMN_NAME)-- 3.从salt列中使用的字符数(SALT_LENGTH)-- 4. 序列名称，但默认为RECEIVING_COLUMN_NAME ||'s'CREATE OR REPLACE FUNCTION generate_table_code() RETURNS trigger AS $$宣布接收列名文本；salt_column_name 文本；salt_length 文本；序列名称文本；开始接收列名:= TG_ARGV[0]；salt_column_name := TG_ARGV[1];salt_length := TG_ARGV[2];案件当 TG_NARGS = 3 THEN序列名称:= 接收列名称 ||'s';当 TG_NARGS = 4 时序列名称:= TG_ARGV[3]；别的RAISE EXCEPTION '预期有 3 或 4 个参数，收到 %'，TG_NARGS；结束案例；-- 意图是当 salt_column 包含 'ABC' 时返回 ABC-0001执行 'rpad(substr(' ||quote_ident(salt_column_name) ||', 1, 4), 4, ' ||quote_literal('-') ||') ||lpad(nextval(' ||quote_literal(sequence_name) ||')::文本, ' ||quote_literal(salt_length) ||', ' ||quote_literal('0') ||')'INTO STRICT NEW;退货；结尾$$ 语言 plpgsql;创建触发器 generate_tenant_code_trig在为每一行插入租户之前执行程序 generate_table_code('tenant_code', 'subdomain', 4);

如何分配给 NEW.tenant_code、NEW.user_code 或 NEW.table_whatever_code?

运行一些测试会产生正确的语句"，但我似乎无法正确分配:

INSERT INTO 租户(子域) VALUES ('abc')创建表错误:NEW"处或附近的语法错误第 1 行:NEW.tenant_code := rpad(substr(subdomain, 1, 4), 4, '-') ||...^查询:NEW.tenant_code := rpad(substr(subdomain, 1, 4), 4, '-') ||lpad(nextval('tenant_codes')::text, '4', '0'::text)上下文:PL/pgSQL 函数generate_table_code"在 EXECUTE 语句的第 20 行

解决方案

我很乐意被显示错误(我自己偶尔也需要这个)，但我最清楚的是，使用变量引用列名是其中之一在您实际需要使用 PL/C 触发器而不是 PL/PgSQL 触发器的情况下.您可以在 contrib/spi 和 PGXN 中找到此类触发器的示例.

或者，一致地命名您的列，以便能够直接引用它们，例如NEW.tenant_code.

就我个人而言，我通常最终会编写一个创建触发器的函数:

创建函数create_tg_stuff(_table regclass, _args[] text[])以 $$ 形式返回无效开始-- 稍微探索 pg_catalog执行 $x$创建函数 $x$ ||quote_ident(_table || '_tg_stuff') ||$x$()将触发器作为 $t$ 返回开始-  更多东西返回新的；结尾;$t$ 语言 plpgsql;$x$;结尾;$$ 语言 plpgsql;

I'd like to generate codes for many tables in the database, and stopped to refactor my solution when I was ready to write my third implementation of "get code for table X".

My code is this:

-- Tenants receive a code that's composed of a portion of their subdomain and a unique number.
-- This number comes from this sequence.
CREATE SEQUENCE tenant_codes_seq MAXVALUE 9999 NO CYCLE;

CREATE TABLE tenants (
    subdomain   varchar(36) NOT NULL UNIQUE
  , tenant_code char(8)     NOT NULL UNIQUE
  , PRIMARY KEY (tenant_code)
);

-- This function expects four parameters:
-- 1. The column that's receiving the generated code (RECEIVING_COLUMN_NAME)
-- 2. The column that's used to salt the code (SALT_COLUMN_NAME)
-- 3. The number of characters to use from the salt column (SALT_LENGTH)
-- 4. The sequence name, but defaults to RECEIVING_COLUMN_NAME || 's'
CREATE OR REPLACE FUNCTION generate_table_code() RETURNS trigger AS $$
DECLARE
  receiving_column_name text;
  salt_column_name      text;
  salt_length           text;
  sequence_name         text;
BEGIN
  receiving_column_name := TG_ARGV[0];
  salt_column_name      := TG_ARGV[1];
  salt_length           := TG_ARGV[2];

  CASE
  WHEN TG_NARGS = 3 THEN
    sequence_name := receiving_column_name || 's';
  WHEN TG_NARGS = 4 THEN
    sequence_name := TG_ARGV[3];
  ELSE
    RAISE EXCEPTION '3 or 4 arguments expected, received %', TG_NARGS;
  END CASE;

  -- The intent is to return ABC-0001 when salt_column contains 'ABC'
  EXECUTE 'rpad(substr('                ||
          quote_ident(salt_column_name) ||
          ', 1, 4), 4, '                ||
          quote_literal('-')            ||
          ') || lpad(nextval('          ||
          quote_literal(sequence_name)  ||
          ')::text, '                   ||
          quote_literal(salt_length)    ||
          ', '                          ||
          quote_literal('0')            ||
          ')'
  INTO STRICT NEW;
  RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER generate_tenant_code_trig
  BEFORE INSERT ON tenants FOR EACH ROW
  EXECUTE PROCEDURE generate_table_code('tenant_code', 'subdomain', 4);

How do I assign to NEW.tenant_code, NEW.user_code or NEW.table_whatever_code?

Running some tests yields the correct "statement", but I can't seem to assign correctly:

INSERT INTO tenants(subdomain) VALUES ('abc')


CREATE TABLE
ERROR:  syntax error at or near "NEW"
LINE 1: NEW.tenant_code := rpad(substr(subdomain, 1, 4), 4, '-') || ...
        ^
QUERY:  NEW.tenant_code := rpad(substr(subdomain, 1, 4), 4, '-') || lpad(nextval('tenant_codes')::text, '4', '0'::text)
CONTEXT:  PL/pgSQL function "generate_table_code" line 20 at EXECUTE statement

解决方案

I'd be quite enthusiastic to be shown wrong (I occasionally need this myself too), but best I'm aware, referring column names using variables is one of those cases where you actually need to use PL/C triggers rather than PL/PgSQL triggers. You'll find examples of such triggers in contrib/spi and on PGXN.

Alternatively, name your columns consistently so as to be able to reference them directly, e.g. NEW.tenant_code.

Personally, I generally end up writing a function that creates the trigger:

create function create_tg_stuff(_table regclass, _args[] text[])
  returns void as $$
begin
  -- explore pg_catalog a bit
  execute $x$
  create function $x$ || quote_ident(_table || '_tg_stuff') || $x$()
    returns trigger as $t$
  begin
    -- more stuff
    return new;
  end;
  $t$ language plpgsql;
  $x$;
end;
$$ language plpgsql;

这篇关于PostgreSQL触发器动态生成多表代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！