Início / dba / Perguntas / 247241
Accepted
zaadeh
Asked: 2019-09-07 19:59:14 +0800 CST

Converter uma linha em um documento JSON aninhado contendo todas as relações

  • 772

No PostgreSQL 10.10, criei uma função de gatilho em PL/pgSQL que converte a NEWlinha em um objeto JSON usando to_jsonb(NEW). Mas agora preciso incluir os registros do outro lado das chaves estrangeiras do NEWregistro no objeto JSON de maneira aninhada.

Por exemplo:

antes da:

employee = {
    "id": 1,
    "name": "myname",
    "department": 2,
    "phone_no": "123456789"
}

depois:

employee = {
    "id": 1,
    "name": "myname",
    "department": {
        "id": 2,
        "name": "IT"
    },
    "phone_no": "123456789"
}

Qual é a melhor e mais genérica maneira de fazer isso sem conhecimento prévio sobre o esquema do NEWregistro? Eu preciso manter essa função de gatilho o mais genérica possível porque pretendo usá-la em todas as tabelas. Atualmente, um nível de profundidade em seguir chaves estrangeiras é suficiente para mim. Também para simplificar, posso assumir que todas as chaves estrangeiras devem ser uma única coluna.

Pelo que entendi, preciso fazer um loop em todas as colunas no NEWregistro, descobrir se a coluna é uma chave estrangeira usando information_schemaou pg_catalog, encontrar os detalhes da chave estrangeira, como para qual coluna em qual tabela ela aponta, e executar um SQL dinâmico SELECT(porque Presumo que os nomes de tabela e coluna sejam strings, não identificadores SQL) sobre a tabela de destino para o registro de destino, converta o registro em JSON e, finalmente, atribua-o à chave apropriada do objeto JSON de linha de nível superior.

Ainda estou tentando escrever o código de trabalho real para isso, para o qual agradeço qualquer ajuda ou orientação. E pode haver soluções mais simples para este problema, que eu gostaria de saber.

postgresql trigger

1 respostas

  1. Best Answer

    Seu palpite está bem próximo, você precisará de SQL dinâmico.

    Mas isso deve ser consideravelmente mais rápido e elegante do que percorrer todas as colunas no NEWregistro etc.:

    CREATE OR REPLACE FUNCTION trg_jsonb_row_with_fk()
  RETURNS trigger AS
$func$
DECLARE
   _sql text;
   _jsonb_row jsonb;
BEGIN
   SELECT 'SELECT to_jsonb($1) || '
       || string_agg(
            format('(SELECT jsonb_build_object(%1$L, t.*)
                     FROM %2$s t WHERE %3$I = $1.%1$I)'
                 , a.attname                 -- %1$L, %1$I
                 , c.confrelid::regclass     -- %2$s
                 , f.attname)                -- %3$I
            , ' || ')
   FROM   pg_constraint c
   JOIN   pg_attribute  a ON a.attrelid = c.conrelid
   JOIN   pg_attribute  f ON f.attrelid = c.confrelid
   WHERE  c.conrelid = TG_RELID     
   AND    c.contype  = 'f'               -- to select only FK constraints
   AND    a.attnum   = c.conkey[1]       -- assuming only single-col FKs!
   AND    f.attnum   = c.confkey[1]
   INTO   _sql;

   IF FOUND THEN                         -- FKs found
      EXECUTE _sql USING NEW INTO _jsonb_row;
   ELSE                                  -- no FKs found, plain conversion
      _jsonb_row := to_jsonb(NEW);
   END IF;

   RAISE NOTICE '%', _jsonb_row;         -- do something with it ...
   RETURN NEW;                           -- proceed with org. row
END
$func$  LANGUAGE plpgsql;

    Exemplo de gatilho usando a função acima:

    CREATE TRIGGER upd_bef_jsonb_row_with_fk
  BEFORE UPDATE ON tbl
  FOR EACH ROW EXECUTE PROCEDURE trg_jsonb_row_with_fk();

    Isso cria subconsultas para todos os FKs das tabelas do catálogo Postgres e executa o comando SQL dinamicamente. Para simplificar, incluo todas as colunas de usuário das linhas correspondentes nas tabelas de pesquisa ( t.*).

    Nitpick: jsonb_build_object(%1$L, t.*) , não jsonb_build_object(%1$L, t).
    Parece adicionar ruído, mas evita um problema de caixa de canto: isso deve funcionar para qualquer tabela de entrada e uma pode conter uma coluna chamada t. Em seguida, o nome tna expressão acima seria resolvido para a coluna em vez do alias da tabela (a linha inteira ). O uso t.*remove essa ambiguidade, pois só pode ser resolvido para a linha inteira. (Os parênteses seriam necessários para se referir a uma coluna de tipo composto , como (t).*). Leia o manual aqui e aqui .

    Como ele usa o nome da coluna das colunas FK originais como nome de chave para objetos estendidos, a concatenação simples com|| faz o que você precisa: substitui o valor simples existente pelo objeto jsonb.

    Leitura adicional:

    • 2

relate perguntas