Thread: How to use recursive clause in one with query

Andrus Moor wrote
> How to use one recursive query if there are may queries in WITH statement
> ?
>
> I tried
>
> with
> a as ( select 1 as col1 ),
> RECURSIVE t(n) AS (
>     VALUES (1)
>   UNION ALL
>     SELECT n+1 FROM t WHERE n < 100
> ),
> c as (select * from t)
>   select * from c
>
> but got error
>
> syntax error at or near "t"
>
> at line
>
> RECURSIVE t(n) AS (
>
> recursive b as ( shown in comment
>
> with clause is used to create some non recursive queries (a) .
> After them recursive query is defined (b) and after it there are some
> other non-recursive queries (c)
>
> Using Postgres 9.1 and above.
>
> Andrus.

http://www.postgresql.org/docs/9.1/interactive/sql-select.html

The expression is defined as "WITH [RECURSIVE] withquery [, ...]"

WITH [RECURSIVE]
  query1 AS ( SELECT ... )
, query2 AS ( SELECT ... )
SELECT ...

Both WITH and RECURSIVE only appear once in the statement no matter how many
withquery CTEs are defined.

The RECURSIVE modifier allows any of the CTEs to reference themselves AND
ALSO allow the queries to be defined "out-of-order".  Without RECURSIVE
earlier queries cannot reference later queries.

While the absence of RECURSIVE prevents the advanced behavior from working
any WITH query-set that works without RECURSIVE will work identically even
if RECURSIVE is added.

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/How-to-use-recursive-clause-in-one-with-query-tp5814261p5814262.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.