On Mon, Dec 5, 2011 at 2:45 PM, David Johnston <pol...@yahoo.com> wrote:
> On Dec 4, 2011, at 22:28, Maxim Boguk <maxim.bo...@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.