PostgreSQL RECURSIVE VIEW

PostgreSQL RECURSIVE VIEW is a SQL statement in the PostgreSQL Specific category. PostgreSQL: a view defined as a recursive query, useful for hierarchical data traversal. The syntax is CREATE RECURSIVE VIEW view_name (columns) AS SELECT ... UNION ALL SELECT ...;. It returns recursive view. A typical example: CREATE RECURSIVE VIEW org_chart AS SELECT id, name, manager_id, 1 AS level, name AS path FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.name, e.manager_id, oc.level + 1, oc.path || ' > ' || e.name FROM employees e JOIN org_chart oc ON e.manager_id = oc.id; SELECT * FROM org_chart ORDER BY path; -- id | name | manager_id | level | path -- 1 | Alice | NULL… A close relative is SERIAL, which postgreSQL auto-increment type. Creates an INTEGER column with a sequence. Equivalent to AUTO_INCREMENT in MySQL. A close relative is RETURNING, which postgreSQL extension that returns values from INSERT/UPDATE/DELETE statements (like OUTPUT in SQL Server).