tengo unparent ychild tabla de la siguiente manera:

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

Creé dos funciones de utilidad para formatear unparent ychild remar a un 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;

Probemos esto:

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;

Esto devuelve los siguientes 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"
      }
   ]
}

¡Excelente! Sin embargo, esto tiene un gran problema: si otra transacción hace algunos cambios entre nuestrosinsert yselect consultas, elselect la consulta devuelve exactamente lo que acabamos de insertar. Podemos solucionar esto estableciendo el nivel de aislamiento de transacción enrepeatable read, pero eso tiene sus costos de rendimiento y otros inconvenientes (es posible que tengamos que volver a intentarlo).

Entonces pensé en reescribir las consultas anteriores en un solo CTE. Si no me equivoco, esto no tendrá problemas de concurrencia. Empecé de la siguiente manera:

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;

Esto no funciona como se esperaba. Devuelve los siguientes objetos JSON:

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

Como puede ver, no hay niños incluidos. Después de leer un poco, entiendo lo que está sucediendo. El CTE funciona en una instantánea creada justo antes de que se iniciara la consulta. Enformat(parent), nosotros estamos haciendoselect format(child) from child where parent_identifier = $1.identifier), pero eso no genera filas secundarias, porque las filas secundarias no están en la instantánea. Entonces mi pregunta no es sobre esto, como lo entiendo.

Por supuesto, podría solucionarlo fácilmente si simplemente hago eljson_build_object cosas, exactamente lo mismo que en elformat funciones, en la consulta principal, pero luego estoy duplicando el código. Estoy usando estosformat funciona también en otras consultas, no relacionadas con esta pregunta. Idealmente, quiero evitar la duplicación de código en mi solución. Por lo tanto, me gustaría seguir usándolos y probablemente necesite refactorizarlos primero para que puedan usarse en el escenario de esta pregunta.

Estoy bastante atrapado ahora. Realmente me gustaría continuar con el CTE (para evitar tener que establecer el nivel de aislamiento de la transacción enrepeatable read), pero no puedo encontrar una manera de refactorizar elformat(parent) yformat(child) funciones y / o el CTE, así que no termino con duplicados de código por todas partes. ¿Hay un alma inteligente en SO con algunas ideas inteligentes?

Tenga en cuenta que estoy usando PostgreSQL 10.1. Encuentra un violín aquí:http://sqlfiddle.com/#!17/a251d/2

actualización sobre la respuesta de Laurenz Albe

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

En la pregunta anterior, realmente estaba simplificando mi situación. Permítanme explicar el escenario real más de cerca, sin entrar en demasiados detalles que serán confusos.

En el escenario, el usuario está proporcionando datos (=parents y sus correspondienteschildren) para un determinado intervalo de fechas, por ejemplo, el mes de enero de 2018. Además, no solo estoy haciendo inserciones, sino que estoy haciendo upserts y eliminaciones de filas huérfanas. Entonces, el escenario es simple: el cliente está reemplazando todos los datos para un rango de fechas dado.

Si entonces lo hagoselect format(parent) from parent where <parent is in date range as provided> después de las actualizaciones y eliminaciones, algún otro cliente podría haber modificado un intervalo de fechas superpuesto en el medio. En ese caso, estoy devolviendo resultados diferentes según lo provisto por el cliente, lo que podría introducir errores si los clientes no se implementan correctamente. Por lo tanto, es por eso que creo que las inserciones y la selección deben ser parte de la misma transacción con el nivel de aislamiento de transacción establecido enrepeatable read.

Pero luego, comencé a pensar en un solo CTE gordo, de ahí mi pregunta.

Espero que esto aclare el escenario.