我想为数据库中的许多表生成代码,并在我准备编写第三个“获取表X的代码”的第三个实现时停止重构我的解决方案.
我的代码是这样的：
-- 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);
如何分配NEW.tenant_code,NEW.user_code或NEW.table_whatever_code？
运行一些测试会产生正确的“语句”,但我似乎无法正确分配：
INSERT INTO tenants(subdomain) VALUES ('abc')
CREATE TABLE
ERROR: Syntax error at or near "NEW"
LINE 1: NEW.tenant_code := rpad(substr(subdomain,'-') || ...
^
QUERY: NEW.tenant_code := rpad(substr(subdomain,'-') || lpad(nextval('tenant_codes')::text,'4','0'::text)
CONTEXT: PL/pgsql function "generate_table_code" line 20 at EXECUTE statement
我会非常热衷于被证明是错误的(我偶尔也需要这个),但最好我知道,使用变量引用列名是你真正需要使用PL / C触发器而不是PL /的情况之一Pgsql触发器.您可以在contrib / spi和PGXN上找到此类触发器的示例.
或者,一致地命名您的列,以便能够直接引用它们,例如NEW.tenant_code.
就个人而言,我通常最终会编写一个创建触发器的函数：
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;
