eu tenho umparent echild tabela da seguinte forma:

create table parent
(
  identifier serial primary key,
  name text
);
create table child
(
  identifier serial primary key,
  name text, parent_identifier integer references parent
);

Criei duas funções utilitárias para formatar umparent echild linha para um objeto JSON:

create function format(child) returns json
  as $
  select json_build_object('identifier', $1.identifier, 'name', $1.name)
  $ language sql stable;

create function format(parent) returns json
  as $
  select json_build_object('identifier', $1.identifier, 'name', $1.name, 
                           'children', array(select format(child) from child where parent_identifier = $1.identifier))
  $ language sql stable;

Vamos testar isso:

insert into parent(name) values('first parent');
insert into parent(name) values('second parent');

insert into child(name, parent_identifier) values('first child first parent', (select identifier from parent where name = 'first parent'));
insert into child(name, parent_identifier) values('second child first parent', (select identifier from parent where name = 'first parent'));
insert into child(name, parent_identifier) values('first child second parent', (select identifier from parent where name = 'second parent'));

select format(parent) from parent;

Isso retorna os seguintes objetos JSON:

{
   "identifier":5,
   "name":"first parent",
   "children":[
      {
        "identifier":7,
        "name":"first child first parent"
      },
      {
        "identifier":8,
        "name":"second child first parent"
      }
   ]
}
{
   "identifier":6,
   "name":"second parent",
   "children":[
      {
        "identifier":9,
        "name":"first child second parent"
      }
   ]
}

Ótimo! Porém, isso tem um grande problema: se outra transação fizer algumas alterações entre nossosinsert eselect consultas, oselect consulta retorna exatamente o que acabamos de inserir. Podemos corrigir isso definindo o nível de isolamento da transação comorepeatable read, mas isso tem seus custos de desempenho e outras desvantagens (talvez seja necessário tentar novamente).

Então, pensei em reescrever as consultas acima em um único CTE. Se não me engano, isso não sofrerá com esses problemas de simultaneidade. Comecei da seguinte forma:

with
parents as
(
  insert into parent(name)
  select 'first parent'
  union all
  select 'second parent'
  returning parent.identifier, parent.name
),
children as
(
  insert into child(name, parent_identifier)
  select 'first child first parent', identifier from parents where name = 'first parent'
  union all
  select 'second child first parent', identifier from parents where name = 'first parent'
  union all
  select 'first child second parent', identifier from parents where name = 'second parent'
)
select format(parents::parent) from parents;

Isso não funciona conforme o esperado. Retorna os seguintes objetos JSON:

{
   "identifier":7,
   "name":"first parent",
   "children":[]
}
{
   "identifier":8,
   "name":"second parent",
   "children":[]
}

Como você pode ver, não há crianças incluídas. Depois de algumas leituras, entendo o que está acontecendo. O CTE funciona em um instantâneo criado logo antes de a consulta ser iniciada. Noformat(parent)estamos fazendoselect format(child) from child where parent_identifier = $1.identifier), mas isso não gera nenhuma linha filho, porque as linhas filho não estão na captura instantânea. Então, minha pergunta não é sobre isso, como eu entendo isso.

Claro, eu poderia consertar isso facilmente se eu simplesmente fizesse ojson_build_object coisas, exatamente o mesmo que noformat funções, na consulta principal, mas depois estou duplicando o código. Eu estou usando essesformat funciona também em outras consultas, não relacionadas a essa pergunta. Idealmente, quero evitar a duplicação de código na minha solução. Então, eu gostaria de continuar usando-os e provavelmente precisará refatorá-los primeiro, para que possam ser usados no cenário desta pergunta.

Estou bastante preso agora. Eu realmente gostaria de continuar com o CTE (para evitar evitar definir o nível de isolamento da transação comorepeatable read), mas não consigo encontrar uma maneira de re-fatorar oformat(parent) eformat(child) funções e / ou CTE, para não acabar com duplicatas de código em todo o lugar. Existe uma alma inteligente em SO com algumas idéias inteligentes?

Note que estou usando o PostgreSQL 10.1. Por favor, encontre um violino aqui:http://sqlfiddle.com/#!17/a251d/2

atualização sobre a resposta de Laurenz Albe

Contexto:https://stackoverflow.com/revisions/48152380/1

Na pergunta acima, eu estava realmente simplificando minha situação. Deixe-me explicar o cenário real mais de perto, sem entrar em muitos detalhes que serão confusos.

No cenário, o usuário está fornecendo dados (=parents e sua correspondentechildren) para um determinado período, por exemplo, o mês de janeiro de 2018. Além disso, não estou apenas inserindo, estou fazendo upserts e exclusões de linhas órfãs. Portanto, o cenário é simples: o cliente está substituindo todos os dados para um determinado período.

Se eu fizerselect format(parent) from parent where <parent is in date range as provided> após as upserts e exclusões, algum outro cliente pode ter alterado um intervalo de datas sobreposto no meio. Nesse caso, estou retornando resultados diferentes, conforme fornecidos pelo cliente, que podem apresentar erros se os clientes não forem implementados corretamente. Portanto, é por isso que acredito que as inserções e a seleção precisam fazer parte da mesma transação com o nível de isolamento da transação definido comorepeatable read.

Mas então, comecei a pensar em uma única CTE gorda, daí a minha pergunta.

Espero que isso esclareça o cenário.