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

问题描述

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

我的代码是这样的：

 -租户收到的代码是由子域的一部分和一个唯一的数字。 
-此数字来自此序列。 
 CREATE SEQUENCE tenant_codes_seq MAXVALUE 9999 NO CYCLE; 
 
创建表租户（
子域varchar（36）非空唯一
，tenant_code char（8）非空唯一
，主键（tenant_code）
）; 
 
-该函数需要四个参数：
-1.接收生成的代码的列（RECEIVING_COLUMN_NAME）
-2.用于给代码加盐的列（SALT_COLUMN_NAME）
-3.盐列中要使用的字符数（SALT_LENGTH）
-4.序列名称，但默认为RECEIVING_COLUMN_NAME || 
的创建或替换功能generate_table_code（）返回触发条件为$$ 
 DECCLARE 
 receive_column_name文本； 
 salt_column_name文本； 
 salt_length文本； 
 sequence_name文本； 
 BEGIN 
 receive_column_name：= TG_ARGV [0]; 
 salt_column_name：= TG_ARGV [1]; 
 salt_length：= TG_ARGV [2]; 
 
情况
 TG_NARGS = 3然后
 sequence_name：= receive_column_name ||的； 
当TG_NARGS = 4时
 sequence_name：= TG_ARGV [3]; 
 ELSE 
上升例外‘3或4个参数，收到％’，TG_NARGS； 
结束案例； 
 
-目的是当salt_column包含'ABC'时返回ABC-0001 
 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'）|| 
'）'
进入严格的新规则； 
返回新； 
 END 
 $$语言plpgsql; 
 
创建触发器generate_tenant_code_trig 
在为每行插入租户之前
执行过程generate_table_code（'tenant_code'，'subdomain'，4）; 
  

如何分配给NEW.tenant_code，NEW.user_code或NEW.table_whatever_code？

运行一些测试会产生正确的声明，但我似乎无法正确分配：

 插入租户（子域）值（'abc'）
 
 
创建表
错误： NEW或附近的语法错误
 LINE 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）
语境：EXECUTE语句
  




解决方案

我会很热情地被证明是错误的（我有时也需要这个）据我所知，使用变量引用列名是实际需要使用PL / C触发器而不是PL / PgSQL触发器的情况之一。您可以在contrib / spi和PGXN上找到此类触发器的示例。

或者，始终命名您的列，以便能够直接引用它们，例如 NEW.tenant_code 。

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

 创建函数create_tg_stuff（_table regclass，_args [] text []）
返回void，因为$$ 
开始
-稍微探索一下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屋！