Re: WITH and WITH RECURSIVE in single query - Mailing list pgsql-general

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

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.

Read the documentation closely, the syntax definition for WITH is precise and accurate.

No matter how many queries you want to create you write the word WITH one time.  If ANY of your queries require iterative behavior you put the word RECURSIVE after the word WITH.  Between individual queries you may only put the name, and optional column alias, along with the required comma.

As a side benefit to adding RECURSIVE the order in which the queries appear is no longer relevant.  Without RECURSIVE you indeed must list the queries in order of use.

David J.