RECURSIVE allowed only ONCE in a CTE

[Jitendra Loyal <jitendra.loyal@xxxxxxxxx>]

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