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:
- root_group CTE to get the root store group (needs RECURSIVE)
- 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