Thanks.... Got it Regards, Jitendra
On Fri 22 Feb, 2019, 4:03 PM mariusz <mar...@mtvk.pl wrote: > On Fri, 22 Feb 2019 13:23:11 +0530 > Jitendra Loyal <jitendra.lo...@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 >
