Weird Syntax - Combine Recursive CTE with Normal CTE (PostgreSQL)

While writing recursive SQL queries  in PostgreSQL , there is a very weird syntax of Recursive Common Table Expression(CTE) .

Single CTE clause :

WITH cte AS(
-- query
)
-- main-query;

Multiple CTE clause :

WITH cte1 AS(
-- query
),
cte2 AS(
-- query
)
-- main-query;

Single Recursive CTE clause :

WITH RECURSIVE recur_cte AS(
-- base-query
UNION [ALL]
-- recur-query
)
-- main-query;

Things look pretty normal until here, but now comes the syntax twist.

Combined Recursive CTE clause :

WITH RECURSIVE cte AS(
-- query
),
recur_cte AS(
-- base-query
UNION [ALL]
-- recur-query
)
-- main-query;

Conclusion : 
Even though we have our second CTE as recursive nature,
PostgresSQL wants us to put RECURSIVE keyword right after the WITH keyword in the first CTE itself and it will identify the cte_recur itself, where you have written the base-query & recur-query.

Comments

Popular posts from this blog

Calculating Top N items per Group (Without Window Functions)

Single vs Double Quotation marks (In PostgreSQL)

Leverage the mind smartly, How?