From: | Jitendra Loyal <jitendra(dot)loyal(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | RECURSIVE allowed only ONCE in a CTE |
Date: | 2019-02-22 07:53:11 |
Message-ID: | CAGBkuscXcXtN=1YTjvBKn4qx+ZZ__g3fEdzgRXwu_kVKLJqHbQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Gierth | 2019-02-22 10:06:06 | Re: RECURSIVE allowed only ONCE in a CTE |
Previous Message | Michael Paquier | 2019-02-22 05:30:26 | Re: How many billion rows of data I can store in PostgreSQL RDS. |