PostgreSQL - migrate a query with 'start with' and 'connect by' in oracle

connect-by, oracle, postgresql

Solution

A something like this should work for you (SQL Fiddle):

WITH RECURSIVE q AS (
    SELECT po.user_id,po.user_name,po.reports_to,po.position
      FROM pr_operators po
     WHERE po.reports_to = 'dpercival'
    UNION ALL
    SELECT po.user_id,po.user_name,po.reports_to,po.position
      FROM pr_operators po
      JOIN q ON q.user_id=po.reports_to
)
SELECT * FROM q;

You can read more on recursive CTE's in the docs.

Note: your design looks strange -- `reports_to` contains string literals, yet it is being comapred with `user_id` which typicaly is of type `integer`.

Problem

I have the following query in oracle. I want to convert it to PostgreSQL form. Could someone help me out in this, ``` SELECT user_id, user_name, reports_to, position FROM pr_operators START WITH reports_to = 'dpercival' CONNECT BY PRIOR user_id = reports_to; ```

Original source