RECURSIVE allowed only ONCE in a CTE

[Jitendra Loyal]

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