Re: RECURSIVE allowed only ONCE in a CTE

On Fri, 22 Feb 2019 13:23:11 +0530
Jitendra Loyal <jitendra.loyal@gmail.com> wrote:

> I find that the RECURSIVE can be used only once in a CTE.
> 
> I have the following use-case where there is a hierarchy of
> store_groups, and then there are stores associated with a
> store_group. Requirement is to ensure that a store can be used only
> once in a store group hierarchy. Following definitions help:
> 
> CREATE TABLE store_groups
> (
> store_group_id SERIAL NOT NULL CONSTRAINT StoreGroups_PK_StoreGroupID
> PRIMARY KEY,
> store_group_nm STXT NOT NULL,
> -- On update, parent_store_group_id should not exist in the sub-tree
> of store_group_id (if any). This is to prevent cycles. Trigger
> ensures the same.
> -- Another trigger ensures that the stores are unique in the new store
> group hierarchy.
> parent_store_group_id INTEGER NULL CONSTRAINT
> StoreGroups_FK_ParentStoreGroupID
> REFERENCES store_groups,
> CONSTRAINT StoreGroups_UK_ParentStoreGroupID_StoreGroupName
> UNIQUE ( parent_store_group_id, store_group_nm )
> );
> 
> CREATE TABLE store_group_stores
> (
> store_group_store_id SERIAL NOT NULL CONSTRAINT
> StoreGroupStores_PK_StoreID PRIMARY KEY,
> store_group_id INTEGER NOT NULL CONSTRAINT
> StoreGroupStores_FK_StoreGroupID REFERENCES store_groups,
> -- Trigger ensures that a store exists only once in a Group hierarchy
> store_id INTEGER NOT NULL -- CONSTRAINT StoreGroupStores_FK_StoreID
> -- REFERENCES stores,
> -- Display order of the store in the Store Group
> -- If display_order is not specified, stores should be listed in
> alphabetical order
> store_seq INTEGER NULL,
> CONSTRAINT StoreGroupStores_UK_StoreGroupID_StoreID
> UNIQUE ( store_group_id, store_id )
> );
> 
> To meet the above need, I was writing a funciton (which will be
> called from triigers) with following algorithm:
> 
>    1. root_group CTE to get the root store group (needs RECURSIVE)
>    2. all_groups to collect all the store groups in root_group (needs
>    RECURSIVE)
> 
> On the second use, I get syntax error.  Kindly confirm that RECURSIVE
> can be used only once. I will find an alternate mechanism.
> 
> Thanks,
> Jitendra Loyal

hi,

you CAN use multiple recursive ctes within one query, i've done this
many times without issues having two or three recursive ctes between
nonrecursive ones.

you probably assumed that you need RECURSIVE keyword applied to cte,
and got syntax errors with multiple RECURSIVE keywords.

what you do is declare WITH RECURSIVE (RECURSIVE added to WITH keyword)
stating that following ctes may contain recursive one (maybe more, and
the recursive one need not be the first one).

what makes cte a recursive one is it's content, that is union
referencing self-cte within from clause.

i admit that i haven't analyzed your store case to make even a simple
working example of what you need, neither i'm pasting any exapmles of
my own real queries (one i got open in terminal just now has about 400
lines, 8 cte, 3 of which are recursive, that would rather be counter
productive as a working example)

just wanted to assure you that multiple recursive ctes within one query
are possible and need only one RECURSIVE keyword appended to WITH
keyword.

regards, mariusz