You can regularly query your queries written with the Common Table Expression by converting them to Recursive View.
We have already done an example of Common Table.
You can find the details in the article titled “How To Create a CTE(Common Table Expression) in PostgreSQL”.
With the help of the following script, we can convert the CTE we created in the previous article into a recursive view.
1 2 3 4 5 6 | CREATE RECURSIVE VIEW Recursive_View_Example(name,employeeid,managerid,title) AS select name,employeeid,managerid,title from employee where employeeid=4 union all select e.name,e.employeeid,e.managerid,e.title from employee e join Recursive_View_Example on e.employeeid=Recursive_View_Example.managerid; |
You can also query the recursive view as follows.
1 | select * from Recursive_View_Example; |
You may want to look at the following articles about Views.
“How To Create a View On PostgreSQL“,
“How To Create a Materialized View On PostgreSQL“,
“How To Create an Updateable View On PostgreSQL“,
“How To Create an Updateable View WITH CHECK CONSTRAINT On PostgreSQL”