Re: WITH and WITH RECURSIVE in single query

On Mon, Dec 5, 2011 at 2:45 PM, David Johnston <polobo@yahoo.com> wrote:

On Dec 4, 2011, at 22:28, Maxim Boguk <maxim.boguk@gmail.com> wrote:

> Hi.
>
> Is here any way to combine WITH and WITH RECURSIVE into single query?
>
> Something like:
>
> WITH t AS (some complicated select to speed up recursive part),
> RECURSIVE r AS
> (
> ...
> UNION ALL
> ...
> )
>
> ?
>
> --
> Maxim Boguk
> Senior Postgresql DBA.

WITH RECURSIVE q1 As (), q2 AS () ...

Add RECURSIVE after the WITH; it then applies to any/all the CTEs.

Look at the specification (and description) in the SELECT documentation closely.

David J.

Trouble is I trying to precalculate some data through WITH syntax (non recursive).
To be used later in WITH RECURSIVE part (and keep a single of that data instead of N).

Something like:

WITH _t AS (some complicated select to speed up recursive part),
RECURSIVE r AS
(
     ...
UNION ALL
    SELECT * FROM r
    JOIN t ON ...
)

So I need have precalculated t table before I start an iterator.

Now instead of _t  I using record[] + unnest  but that appoach very memory hungry for long iterations:

WITH RECURSIVE r AS
(
      SELECT ...
          ARRAY(SELECT ROW(t.*) FROM some complicated select to speed up recursive part) as _t_array
      FROM ...

UNION ALL
      SELECT
          ...,
          _t_array
      FROM r
      JOIN (unnest(_t_array) ...)  ON something
)

However that approach lead to having copy of the _t_array per each final row, so can use a lot of memory.

PS: Yes I know about pl/pgsql but WITH RECURSIVE iterators can give 2-10 time performance gains over implemenation of the same algorythm inside pl/pgsql.

--
Maxim Boguk
Senior Postgresql DBA.