Início / dba / Perguntas / 266540
Accepted
s.k
Asked: 2020-05-06 07:04:30 +0800 CST

Defina var="NEW.this_word_column" em uma função de gatilho se "word" estiver presente em um nome de coluna

  • 772

Contexto

Estou usando o PostgreSQL 10.12.

Eu quero ser capaz de disparar uma função de gatilho em algumas tabelas específicas com dados geográficos.

Esta função de gatilho é projetada para criar um ponto PotGIS em uma coluna geomde late lonvalores.
Eu quero aplicar essa mesma função de gatilho em várias tabelas cada vez que uma nova linha é inserida.
A nomenclatura das colunas que contêm valores de latitude e longitude não é consistente nas tabelas, mas segue um padrão...

O que tenho 100% de certeza é que:
1. sempre há duas colunas contendo valores de latitude e longitude,
2. o nome dessas colunas nem sempre é o mesmo nas diferentes tabelas (caso contrário, seria muito simples), mas o palavra 'latitude' e a palavra 'longitude' sempre aparecem em seu nome e nunca aparecem em outros nomes de coluna.

Por exemplo, na tabela A:

_loc_longitude_e_loc_latitude_

e na tabela B:

_building_longitude_centere_building_latitude_center

por exemplo (+ muitos outros).

A função de disparo é a seguinte:

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';

Com os seguintes acionadores:

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).

Não sei como procurar o padrão .*latitude.*nos nomes das colunas passados ​​para a função e como obter seu valor em duas variáveis ​​que posso usar para construir o ponto.
E minhas habilidades de pesquisa no Google para pesquisar esse tipo de coisa estão longe de ser perfeitas, portanto, retornando resultados barulhentos.

Pergunta

Existe alguma possibilidade de fazer isso?

postgresql trigger

1 respostas

  1. Best Answer

    Você pode usar visualizações INFORMATION_SCHEMA e variáveis ​​especiais: TG_TABLE_SCHEMA, TG_TABLE_NAME

    Citado de documentos:

    Quando uma função PL/pgSQL é chamada como trigger, várias variáveis ​​especiais são criadas automaticamente no bloco de nível superior. Eles são:

    TG_TABLE_NAME: Nome do tipo de dados; o nome da tabela que causou a invocação do gatilho.

    TG_TABLE_SCHEMA: Nome do tipo de dados; o nome do esquema da tabela que causou a chamada do gatilho.

    Dado o próximo exemplo:

    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<>fique aqui

    É apenas um exemplo de como usar INFORMATION_SCHEMA e variáveis ​​especiais dentro de uma trigger, mas talvez um pro-user do Postgres possa lhe dar uma solução melhor.

    • 2

relate perguntas