Alternatives to psequel8/5/2023 ![]() ![]() INSERT INTO emp_test VALUES (3, 'def', 2) INSERT INTO emp_test VALUES (2, 'xyz', 1) INSERT INTO emp_test VALUES (1, 'abc', null) Where the recursive term includes a reference to the query's output. The structure of a WITH RECURSIVE query is always: a) Non-recursive termī) UNION (or UNION ALL), then a recursive term Recursive queries are used to deal with hierarchical queries or tree-structured data. Postgres=# SELECT * FROM connectby('dummy_table', 'emp_no', 'manager_no', '10', 0, '->') AS t(emp_no int, manager_no int, level int, ord text) order by emp_no Then, use tablefunc’s connectby function to display results hierarchically: The tablefunc extension is a contrib module that resides in the contrib/ folder in PostgreSQL sources. Postgres=#WITH RECURSIVE cte AS ( SELECT emp_no, ename, manager_no, 1 AS level FROM test_table where manager_no is null UNION ALL SELECT e.emp_no, e.ename, e.manager_no, c.level + 1 FROM cte c JOIN test_table e ON e.manager_no = c.emp_no ) SELECT * FROM cte ![]()
0 Comments
Leave a Reply.AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |