使用START WITH和CONNECT BY PRIOR将查询从Oracle迁移到PostgreSQL [英] Migrate query with START WITH and CONNECT BY PRIOR from oracle to postgresql

问题描述

我正在将一个进程从oracle迁移到postgresql，而在它们的转换方面又遇到了另一个问题。

I am migrating a process from oracle to postgresql, and I am in another problem with the conversion of them.

我一直在研究如何迁移oracle查询。我已经对此进行了记录，其中包含 START WITH和 PRIOR BY PRIOR，并且我认为最简单的方法是使用 WITH RECURSIVE

I have been researching how to migrate an oracle query, which has "START WITH" and "CONNECT BY PRIOR", I have documented with respect to this, and I think the easiest way to do it is with "WITH RECURSIVE"

进行查询的迁移，但是由于bd oracle和postgres不同，因此我不确定它们抛出的结果，并且不可能对bd进行认证。

Make the migration of the query, but I'm not sure about the results they throw since the bd oracle and postgres are different, and it is not possible to homologate the bd.

这是Oracle中的查询

This is the query in Oracle

SELECT edef_codigo, etdf_transac, edef_detail--, LEVEL
FROM edeft
WHERE edef_distrib in('OM', 'N/A')
AND  pers_codigo_socadm = 311745439
AND  ctac_correlativo = 7513
START WITH etdf_transac = 'SDN'
CONNECT BY PRIOR edef_codigo = edef_padre;

这是Postgresql中的查询

And this is the query in postgresql

WITH RECURSIVE edf AS ( SELECT ed.edef_codigo, ed.etdf_transac, 
                               ed.edef_detail
                        FROM edeft ed
                        WHERE ed.edef_distrib in('OM', 'N/A')
                        AND  ed.pers_codigo_socadm = 311745439
                        AND  ed.ctac_correlativo = 7513
                        AND ed.etdf_transac = 'SDN'

                        UNION ALL

                        SELECT ed.edef_codigo, ed.etdf_transac, 
                               ed.edef_detail
                        FROM edeft ed
                             JOIN edf ON edf.edef_codigo = ed.edef_padre    
                        WHERE ed.edef_distrib in('OM', 'N/A')
                        AND  ed.pers_codigo_socadm = 311745439
                        AND  ed.ctac_correlativo = 7513                 
                      )
                    SELECT * FROM edf;

我对Postgres还是陌生的，由于没有找到例子，这次咨询使我特别复杂

I am still new to postgres and this consultation has made me especially complicated, since I have not found examples similar to what I have.

推荐答案

是的，我还在Postgresql中使用带递归使用了按先连接转换。查询我发现这是正确的方法。

Yes, I have also used "Connect by prior" conversion in Postgresql using "With Recursive" queries And I find this is the right approach.

参考之前的一个简单示例：

One simple example in reference to connect by prior:

Oracle：

Select name, age from user_test connect by prior user_id=parent_id start with user_id='a';

Postgres：

with recursive cte_name as 
(select u1.name, u1.user_id, u1.age from user_test u1 where user_id='a' 
UNION ALL select u2.name, u2.user_id, u2.age from user_test u2 
join cte_name on cte_name.user_id=u2.parent_id) select name,age from cte_name;

这篇关于使用START WITH和CONNECT BY PRIOR将查询从Oracle迁移到PostgreSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！