CODEWITHSUNDEEP
sqlpostgresqldatabase-migrationrecursive-query
Stefan Kahr
Stefan Kahr

Reputation: 21

Migrate Oracle CONNECT BY to postgresql 10

i have this SQL:

SELECT count(*) as ct
          FROM classifications cls
WHERE
   cls.classification_id = :classification_id
START WITH cls.classification_id = :root_classification_id
CONNECT BY NOCYCLE PRIOR cls.classification_id = cls.parent_id

and need to migrate it to postgresql 10.

I have already installed the extension tablefunc and tried with connectedby. here my try:

SELECT count(*) as ct
          FROM classifications cls
WHERE
   cls.classification_id = :classification_id

union
                  SELECT count(classification_id) FROM connectby('classifications','classification_id','parent_id',:root_classification_id,5)
                  as t(classification_id varchar, parent_id varchar,level int)

Problem is that the union is the wrong way because then you get 2 results of the count.

Upvotes: 2

Views: 480

Answers (1)

user330315
user330315

Reputation:

No need to use the tablefunc extension. This can easily be done using a recursive CTE

with recursive tree as (

  select cls.classification_id, cls.parent_id 
  from classifications cls
  where cls.classification_id = :root_classification_id

  union all

  select child.classification_id, child.parent_id
  from classifications child
     join tree parent on child.parent_id = parent.classification_id
)
select count(*)
from tree;

The first query inside the CTE matches the start with part from Oracle's start with. And the JOIN back to the CTE in the second query matches the connect by part in the Oracle query.

Upvotes: 2

Related Questions