Estoy migrando un proyecto de Oracle a PG y tengo un problema con una consulta sql.
Esta es mi tabla:
CREATE TABLE descriptor_value ( descriptor_value_id bigint NOT NULL, descriptor_group_id bigint NOT NULL, full_value varchar(4000) NOT NULL, short_value varchar(250), value_code varchar(30), sort_order bigint NOT NULL, parent_value_id bigint, deleted smallint NOT NULL DEFAULT 0, portal smallint NOT NULL DEFAULT 0 ) ;Esta es la consulta original de Oracle que estoy migrando:
select rownum as ROW_NUM, DESCRIPTOR_VALUE_ID from DESCRIPTOR_VALUE connect by prior DESCRIPTOR_VALUE_ID = PARENT_VALUE_ID start with PARENT_VALUE_ID is null and DESCRIPTOR_GROUP_ID = (select DESCRIPTOR_GROUP_ID from DESCRIPTOR_VALUE where DESCRIPTOR_VALUE_ID = 867) order siblings by SORT_ORDER;Y esta es mi consulta postgresql:
WITH RECURSIVE descriptor_values AS ( SELECT ARRAY[descriptor_value_id] AS hierarchy, descriptor_value_id, parent_value_id, sort_order FROM descriptor_value WHERE parent_value_id IS NULL AND descriptor_group_id = ( SELECT descriptor_group_id FROM descriptor_value WHERE descriptor_value_id = 867) UNION ALL SELECT descriptor_values.hierarchy || dv.descriptor_value_id, dv.descriptor_value_id, dv.parent_value_id, dv.sort_order FROM descriptor_value dv JOIN descriptor_values ON dv.parent_value_id = descriptor_values.descriptor_value_id ) SELECT descriptor_value_id FROM descriptor_values order by hierarchy; Solución para order siblings by que tomé de aquí https://stackoverflow.com/a/17737560/5182503 . Sin embargo, el orden de las filas en el conjunto de resultados de postgresql difiere del orden en el conjunto de resultados de Oracle. ¿Qué hay de Rownum aquí? Me detuve por completo. ¿Alguien podría ayudarme a construir la consulta pg correcta?
La solución que vincula asume que hay un orden natural del descriptor_value_id .
Si sort_order es único en la tabla, la siguiente consulta debería funcionar.
Si no, entonces tendremos que hacer un poco de mono en los valores que pones en la matriz de hierarchy .
WITH RECURSIVE descriptor_values AS ( SELECT ARRAY[sort_order] AS hierarchy, descriptor_value_id, parent_value_id, sort_order FROM descriptor_value WHERE parent_value_id IS NULL AND descriptor_group_id = ( SELECT descriptor_group_id FROM descriptor_value WHERE descriptor_value_id = 867) UNION ALL SELECT descriptor_values.hierarchy || dv.sort_order, dv.descriptor_value_id, dv.parent_value_id, dv.sort_order FROM descriptor_value dv JOIN descriptor_values ON dv.parent_value_id = descriptor_values.descriptor_value_id ) SELECT descriptor_value_id FROM descriptor_values order by hierarchy;Actualizar en respuesta al comentario
Dado que sort_order no es único en la tabla, debe incorporarse a la matriz de hierarchy . También agregué el número de row_number() a la consulta final.
WITH RECURSIVE descriptor_values AS ( SELECT ARRAY[sort_order, descriptor_value_id] AS hierarchy, descriptor_value_id, parent_value_id, sort_order FROM descriptor_value WHERE parent_value_id IS NULL AND descriptor_group_id = ( SELECT descriptor_group_id FROM descriptor_value WHERE descriptor_value_id = 867) UNION ALL SELECT descriptor_values.hierarchy || array[dv.sort_order, dv.descriptor_value_id] dv.descriptor_value_id, dv.parent_value_id, dv.sort_order FROM descriptor_value dv JOIN descriptor_values ON dv.parent_value_id = descriptor_values.descriptor_value_id ) SELECT row_number() over (order by hierarchy), hierarchy, descriptor_value_id FROM descriptor_values order by hierarchy;