Início / dba / Perguntas / 61271
Accepted
François Beausoleil
Asked: 2014-03-20 11:31:35 +0800 CST

Como acessar o campo NOVO ou ANTIGO dado apenas o nome do campo?

  • 772

Estou escrevendo um gatilho de validação. O gatilho deve validar se a soma de uma matriz é igual a outro campo. Como tenho muitas instâncias dessa validação, quero escrever um único procedimento e criar vários gatilhos, cada um com um conjunto diferente de campos a serem verificados.

Por exemplo, tenho o seguinte esquema:

CREATE TABLE daily_reports(
     start_on date
   , show_id uuid
   , primary key(start_on, show_id)

     -- _graph are hourly values, while _count is total for the report
   , impressions_count bigint not null
   , impressions_graph bigint[] not null

   -- interactions_count, interactions_graph
   -- twitter_interactions_count, twitter_interactions_graph
);

A validação deve confirmar isso impressions_count = sum(impressions_graph).

Estou preso porque não sei como acessar dinamicamente um campo NEWde dentro do plpgsql:

CREATE FUNCTION validate_sum_of_array_equals_other() RETURNS TRIGGER AS $$
DECLARE
  total bigint;
  array_sum bigint;
BEGIN
  -- TG_NARGS = 2
  -- TG_ARGV[0] = 'impressions_count'
  -- TG_ARGV[1] = 'impressions_graph'

  -- How to access impressions_count and impressions_graph from NEW?
  RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER validate_daily_reports_impressions
ON daily_reports BEFORE INSERT OR UPDATE
FOR EACH ROW EXECUTE
  validate_sum_of_array_equals_other('impressions_count', 'impressions_graph');

Tentei Executar Comandos Dinâmicos fazendo EXECUTE 'SELECT $1 FROM NEW' INTO total USING TG_ARGV[0], mas PL/PGsql reclama que NEW é uma relação desconhecida.

Estou visando especificamente o PostgreSQL 9.1.

postgresql trigger

1 respostas

  1. Best Answer

    Na verdade, como NEWé um tipo composto bem definido, você pode acessar qualquer coluna com notação de atributo simples e simples. O próprio SQL não permite identificadores dinâmicos (nomes de tabelas ou colunas, etc.). Mas você pode usar SQL dinâmicoEXECUTE em uma função PL/pgSQL.

    Demonstração

    CREATE OR REPLACE FUNCTION trg_demo1()
  RETURNS TRIGGER AS
$func$
DECLARE
   _col_value text;
   _col_name  text := quote_ident(TG_ARGV[0]);  -- escape identifier
BEGIN
   EXECUTE format('SELECT ($1).%s::text', _col_name)
   USING NEW
   INTO  _col_value;

   -- do something with _col_value ...

   RAISE NOTICE 'It works. The value of NEW.% is >>%<<.', _col_name, _col_value;

   RETURN NEW;
END
$func$ LANGUAGE plpgsql;

    A conversão para texté opcional. Usando-o, porque funciona universalmente. Se você conhece o tipo, pode trabalhar sem fundição ...

    Usando format()com %s, porque o identificador já possui escape nesse ponto.
    Caso contrário, use format()with %Ipara proteger contra injeção de SQL.

    Como alternativa , no Postgres 9.3 ou posterior, você pode converter NEWpara JSON com to_json()e acessar colunas como chaves:

    CREATE OR REPLACE FUNCTION trg_demo2()
  RETURNS TRIGGER AS
$func$
DECLARE
   _col_value text := to_json(NEW) ->> TG_ARGV[0];  -- no need to escape identifier
BEGIN
   RAISE NOTICE 'It works. The value of NEW.% is >>%<<.', TG_ARGV[0], _col_value;
   RETURN NEW;
END
$func$ LANGUAGE plpgsql;

    Como o nome da coluna não é concatenado em uma string SQL, a injeção de SQL não é possível e o nome não precisa ser escapado.

    db<>fiddle aqui (com EXCEPTIONem vez de NOTICEpara tornar o efeito visível).

    Relacionado:

    • 18

relate perguntas