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.
