postgresql – 基于计数限制插入的通用触发器

发布时间：2020-12-13 15:52:15 所属栏目：百科来源：网络整理

导读：背景在PostgreSQL 9.0数据库中,有各种表具有多对多关系.必须限制这些关系的数量.几个示例表包括： CREATE TABLE authentication ( id bigserial NOT NULL,-- Primary key cookie character varying(64) NOT NULL,-- Authenticates the user with a cookie i

背景

在PostgreSQL 9.0数据库中,有各种表具有多对多关系.必须限制这些关系的数量.几个示例表包括：

CREATE TABLE authentication (
  id bigserial NOT NULL,-- Primary key
  cookie character varying(64) NOT NULL,-- Authenticates the user with a cookie
  ip_address character varying(40) NOT NULL -- Device IP address (IPv6-friendly)
)

CREATE TABLE tag_comment (
  id bigserial NOT NULL,-- Primary key
  comment_id bigint,-- Foreign key to the comment table
  tag_name_id bigint -- Foreign key to the tag name table
)

然而,不同的关系具有不同的限制.例如,在认证表中,给定的ip_address允许1024个cookie值;而在tag_comment表中,每个comment_id可以有10个关联的tag_name_ids.

问题

目前,许多功能都对这些限制进行了硬编码;在整个数据库中分散限制,并防止它们动态更改.

题

您将如何以通用方式对表施加最大的多对多关系限制？

理念

创建一个表来跟踪限制：

CREATE TABLE imposed_maximums (
  id serial NOT NULL,table_name  character varying(128) NOT NULL,column_group character varying(128) NOT NULL,column_count character varying(128) NOT NULL,max_size INTEGER
)

建立限制：

INSERT INTO imposed_maximums
  (table_name,column_group,column_count,max_size) VALUES
  ('authentication','ip_address','cookie',1024);
INSERT INTO imposed_maximums
  (table_name,max_size) VALUES
  ('tag_comment','comment_id','tag_id',10);

创建触发器功能：

CREATE OR REPLACE FUNCTION impose_maximum()
  RETURNS trigger AS
$BODY$
BEGIN
  -- Join this up with imposed_maximums somehow?
  select
    count(1)
  from
    -- the table name
  where
    -- the group column = NEW value to INSERT;

  RETURN NEW;
END;

将触发器附加到每个表：

CREATE TRIGGER trigger_authentication_impose_maximum
  BEFORE INSERT
  ON authentication
  FOR EACH ROW
  EXECUTE PROCEDURE impose_maximum();

显然它不会像写的那样工作……有没有办法使它工作,或以其他方式强制执行限制,使得它们是：

>在一个地方;和
>没有硬编码？

谢谢！

解决方法

我一直在做类似的通用触发器.
最棘手的部分是根据列名称在新记录中获取值条目.

我是按照以下方式做的：

>将新数据转换为数组;
>找到列的attnum并将其用作数组的索引.

只要数据中没有逗号,这种方法就可以工作:(我不知道如何将NEW或OLD变量转换为值数组的其他方法.

以下功能可能有所帮助：

CREATE OR REPLACE FUNCTION impose_maximum() RETURNS trigger AS $impose_maximum$
DECLARE
  _sql  text;
  _cnt  int8;
  _vals text[];
  _anum int4;
  _im   record;

BEGIN
 _vals := string_to_array(translate(trim(NEW::text),'()',''),',');

 FOR _im IN SELECT * FROM imposed_maximums WHERE table_name = TG_TABLE_NAME LOOP
  SELECT attnum INTO _anum FROM pg_catalog.pg_attribute a
    JOIN pg_catalog.pg_class t ON t.oid = a.attrelid
   WHERE t.relkind = 'r' AND t.relname = TG_TABLE_NAME
     AND NOT a.attisdropped AND a.attname = _im.column_group;

  _sql := 'SELECT count('||quote_ident(_im.column_count)||')'||
          ' FROM '||quote_ident(_im.table_name)||
          ' WHERE '||quote_ident(_im.column_group)||' = $1';

  EXECUTE _sql INTO _cnt USING _vals[_anum];

  IF _cnt > CAST(_im.max_size AS int8) THEN
    RAISE EXCEPTION 'Maximum of % hit for column % in table %(%=%)',_im.max_size,_im.column_count,_im.table_name,_im.column_group,_vals[_anum];
  END IF;
 END LOOP;

 RETURN NEW;
END; $impose_maximum$LANGUAGE plpgsql;

此函数将检查为给定表定义的所有条件.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!