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

Question

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;

Answer 1

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.