主页 / dba / 问题 / 266540
Accepted
s.k
Asked: 2020-05-06 07:04:30 +0800 CST

如果列名中存在“word”，则在触发函数中设置 var="NEW.this_word_column"

  • 772

语境

我正在使用PostgreSQL 10.12。

我希望能够在某些具有地理数据的特定表上触发触发器功能。

此触发函数旨在将PotGIS点创建到geom来自lat和lon值的列中。
每次插入新行时，我想在多个表上应用相同的触发器函数。
保存纬度和经度值的列的命名在表之间并不一致，但它遵循一种模式......

我 100% 肯定的是：
1. 总是有两列包含纬度和经度值，
2. 这些列的名称在不同的表中并不总是相同（否则它会太简单），但是“纬度”一词和“经度”一词 始终出现在它们的名称中，而从不出现在其他列名中。

例如在表 A 中：

_loc_longitude_和_loc_latitude_

在表 B 中：

_building_longitude_center和_building_latitude_center

例如（+许多其他）。

触发函数如下：

CREATE OR REPLACE FUNCTION make_point_with_latlon()
  RETURNS trigger AS
  $$
  DECLARE
      varlon := NULL;
      varlat := NULL;
  BEGIN
      IF to_jsonb(NEW) SIMILAR TO '.*longitude.*' THEN
          varlon := NEW.the_column_whith_longitude_in_its_name;
      END IF;
      IF to_jsonb(NEW) SIMILAR TO '.*latitude.*' THEN
          varlat := NEW.the_column_whith_latitude_in_its_name;
      END IF;
      NEW.geom = ST_MakePoint(varlon, varlat);
  END
  $$
LANGUAGE 'plpgsql';

使用以下触发器：

CREATE TRIGGER make_point_with_latlon_but
    BEFORE INSERT OR UPDATE ON schema.table1
    FOR EACH ROW
    EXECUTE PROCEDURE schema.make_point_with_latlon();

CREATE TRIGGER make_point_with_latlon_but
    BEFORE INSERT OR UPDATE ON schema.table2
    FOR EACH ROW
    EXECUTE PROCEDURE schema.make_point_with_latlon();

CREATE TRIGGER make_point_with_latlon_but
    BEFORE INSERT OR UPDATE ON schema.table3
    FOR EACH ROW
    EXECUTE PROCEDURE schema.make_point_with_latlon();

-- (...and many more other tables that need the same trigger).

我不知道如何.*latitude.*在传递给函数的列名称中搜索模式，以及如何将它们的值转换为我可以用来构建点的两个变量。
而且我搜索这类东西的谷歌搜索技巧远非完美，因此返回嘈杂的结果。

问题

有没有可能这样做？

postgresql trigger

1 个回答

  1. Best Answer

    您可以使用 INFORMATION_SCHEMA 视图和特殊变量：TG_TABLE_SCHEMA、TG_TABLE_NAME

    引用自文档：

    当 PL/pgSQL 函数作为触发器调用时，会在顶层块中自动创建几个特殊变量。他们是：

    TG_TABLE_NAME：数据类型名称；导致触发器调用的表的名称。

    TG_TABLE_SCHEMA：数据类型名称；导致触发器调用的表的模式的名称。

    给出下一个例子：

    create table a (id int primary key, latitude int, longitude int);
create table b (id int primary key, _loc_latitude int, _loc_longitude int);
create table c (result text);

    create function trg_tables()
returns trigger as
$$
declare
  varlat text;
  varlng text;
  cmd text;
  vallat int;
  vallng int;
begin  
  varlat := (select column_name
            from   information_schema.columns
            where  table_schema = TG_TABLE_SCHEMA
                   and table_name = TG_TABLE_NAME
                   and column_name like '%latitude%');

  varlng := (select column_name
            from   information_schema.columns
            where  table_schema = TG_TABLE_SCHEMA
                   and table_name = TG_TABLE_NAME
                   and column_name like '%longitude%');

  execute 'select $1.' || varlat
  using NEW
  into vallat;

  execute 'select $1.' || varlng
  using NEW
  into vallng;

  execute format('insert into c values (''lat: '' || ''%s'' || ''  lng '' || ''%s'' )',
                 vallat::text, vallng::text);

  return NEW;
end;
$$
language plpgsql;

    create trigger trg after insert on a
  for each row execute procedure trg_tables();
create trigger trg after insert on b
  for each row execute procedure trg_tables(); 

    insert into a values (1,11,1);
insert into b values (1,20,2);
select * from c;

| result         |
| :------------- |
| lat: 11  lng 1 |
| lat: 20  lng 2 |

    db<>在这里摆弄

    这只是一个关于如何在触发器中使用 INFORMATION_SCHEMA 和特殊变量的示例，但也许 Postgres 专业用户可以为您提供更好的解决方案。

    • 2

相关问题