Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jgTp3-0000Rl-DH for pgsql-sql@arkaria.postgresql.org; Wed, 03 Jun 2020 13:52:49 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jgTp2-0004L0-9n for pgsql-sql@arkaria.postgresql.org; Wed, 03 Jun 2020 13:52:48 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jgTp2-0004JD-1R for pgsql-sql@lists.postgresql.org; Wed, 03 Jun 2020 13:52:48 +0000 Received: from p3plsmtpa11-06.prod.phx3.secureserver.net ([68.178.252.107]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jgToy-0000cU-Nk for pgsql-sql@postgresql.org; Wed, 03 Jun 2020 13:52:47 +0000 Received: from [192.168.1.110] ([216.186.138.192]) by :SMTPAUTH: with ESMTPSA id gToujBO8wJ4FLgTovjtOeQ; Wed, 03 Jun 2020 06:52:41 -0700 X-CMAE-Analysis: v=2.3 cv=beIVr9HB c=1 sm=1 tr=0 a=Nv6Qle2Mig8/UoIcj+nzwA==:117 a=Nv6Qle2Mig8/UoIcj+nzwA==:17 a=r77TgQKjGQsHNAKrUKIA:9 a=epTmVMiNAAAA:8 a=pN0cZpUREVnwU5Vjq2sA:9 a=elNfqnEZZ-s1MvPb:21 a=9qFlZg0VePL7w9N9:21 a=QEXdDO2ut3YA:10 a=pIlDy6L7vVoA:10 a=KbxM5RnWoXIA:10 a=osT_ipygedA-Kq7g6PcA:9 a=EXnLprJF3ZgP0qrc:21 a=VQDTrsitSGarbfnl:21 a=Z7YImCNrmAK5YELB:21 a=_W_S_7VecoQA:10 a=ndEWmUVY6Yapc0oHF_P4:22 X-SECURESERVER-ACCT: mark@injection-moldings.com To: pgsql-sql@postgresql.org From: Mark Bannister Subject: Recursive CTE with a function Autocrypt: addr=mark@injection-moldings.com; keydata= mDMEXlRD5BYJKwYBBAHaRw8BAQdAf0DaEnnHZfPD4U98z5IVeL2HATFxh9z0F9JUJFGYuAq0 LE1hcmsgQmFubmlzdGVyIDxtYXJrQGluamVjdGlvbi1tb2xkaW5ncy5jb20+iJYEExYIAD4W IQQMqD7xDKym7UU9Quasjmss9dWHhgUCXlRD5AIbAwUJCWYBgAULCQgHAgYVCgkICwIEFgID AQIeAQIXgAAKCRCsjmss9dWHhrsvAQDe7wEULUzEHhEa2bCljyuDT2z1RMO6BEa5KwPZ+g7P AgD+KHhkW0iU1ib/oYkMBqKsvdFQrNg2mguhvjZcE+PRVAa4OAReVEPkEgorBgEEAZdVAQUB AQdAzZ08KPCBxA19rAUzlpvBbmlUy89LbCscd51eYi0QpHUDAQgHiH4EGBYIACYWIQQMqD7x DKym7UU9Quasjmss9dWHhgUCXlRD5AIbDAUJCWYBgAAKCRCsjmss9dWHhkwQAQDzC1TTD0Z+ g7ZBdE/kRhcu1C5cg0mTvt1za33phdrRbwEA+RXZIZYHo4b2yjptsLrCEIC38GOevGNKWcjf sUVvkgc= Message-ID: Date: Wed, 3 Jun 2020 08:52:40 -0500 User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:68.0) Gecko/20100101 Thunderbird/68.8.1 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="------------3987899B42381D1E407B9A4D" Content-Language: en-US X-CMAE-Envelope: MS4wfOhA3IC9+QpypgkAP8cYbW43BlPzSwDXDIe8peLXp4MYYjphwW3p3YHkcSk3ZqbL8ABdYh4uQMO0zRqXslg9hm14FirLxF9/NJmSKRlVDIwv9cIHBPw3 qtYSMfAAbXqp1I+wKZJJcrLuLrtmUS+xdy5I914QfWMrcNBISrw7aAlLNfhU/k6VXhWH8dy27uOwog== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------3987899B42381D1E407B9A4D Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: base64 UG9zdGdyZXNxbCAxMSAod2lsbCBiZSB1cGRhdGluZyB0byAxMiBzb29uKQ0KDQpXaGF0IGlz IHRoZSBiZXN0IHdheSB0byBtYWtlIHRoZSBmdW5jdGlvbiAncG5fZ3JvdXBfbWVtYmVycycg YmUNCnJlY3Vyc2l2ZT/CoCBJJ20gdHJ5aW5nIHdpdGggYSByZWN1cnNpdmUgQ1RFIHF1ZXJ5 IGJ1dCBub3QgZ2V0dGluZyBpdA0KcmlnaHQgZXZpZGVudGx5LiBGb2xsb3dpbmcNCmh0dHBz Oi8vd3d3LnBvc3RncmVzcWwub3JnL2RvY3MvMTEvcXVlcmllcy13aXRoLmh0bWwgYXQgYm90 dG9tIG9mIHRoZSBwYWdlLg0KDQpUaGUgdGFibGUgczBwbmdyb3VweGwgaXMgZ3JvdXBzIHBh cnQgbnVtYmVycyBpbnRvIGdyb3Vwcy7CoCBHcm91cHMgYXJlDQpkZWZpbmVkIGJ5IHMwcG5n cm91cHMgYW5kIHMwcG5ncm91cHhsIGFyZSB0aGUgbWVtYmVycyBvZiBlYWNoIGdyb3VwLsKg DQpGdW5jdGlvbiBwbl9ncm91cF9tZW1iZXJzIHJldHVybnMgZm9yIHRoZSByZXF1ZXN0ZWQg cGFydCBudW1iZXIsIGEgdGFibGUNCndpdGggYWxsIHRoZSBwYXJ0IG51bWJlcnMgaW4gdGhl IGdyb3VwIHdoZXJlIHRoYXQgcGFydCBudW1iZXIgaXMgYQ0KbWVtYmVyLsKgIFRoZXJlIGFy ZSBjYXNlcyB3aGVyZSBJIHJlcXVpcmUgdG8gaGF2ZSB0aGlzIHRvwqAgYmUgcmVjdXJzaXZl DQphbmQgcmV0dXJuIGFsbCB0aGUgZ3JvdXBzIGZvciBhbnkgZm91bmQgcGFydCBudW1iZXJz ICh3aXRob3V0IGluZmluaXRlDQpsb29waW5nKS7CoA0KDQoNCg0KV0lUSCBSRUNVUlNJVkUg c2VhcmNoX2dwcyAocG5pZCwgZ3JvdXBpZCwgZGVwdGgsIHBhdGgsY3ljbGUgKSBBUyAoDQrC oMKgwqAgU0VMRUNUIGdtLnBuaWQsIGdtLnBuZ3JvdXANCsKgwqDCoCAsMQ0KwqDCoMKgICxB UlJBWVtST1coZ20ucG5ncm91cCxnbS5wbmlkKV0NCsKgwqDCoCAsRkFMU0UNCg0KwqDCoMKg IEZST00gcG5fZ3JvdXBfbWVtYmVycygxNzM0NCxGQUxTRSwnezEsNiw1LDN9JykgZ20NClVO SU9OIEFMTA0KwqDCoMKgIFNFTEVDVCBnbS5wbmlkLCBnbS5wbmdyb3VwDQrCoMKgwqAgLCBn cHMxLmRlcHRoKzENCsKgwqDCoCAscGF0aCB8fCBST1coZ20ucG5ncm91cCxnbS5wbi5wbmlk KQ0KwqDCoMKgICxST1coZ20ucG5ncm91cCxnbS5wbmlkKSA9IEFOWShwYXRoKQ0KDQrCoMKg wqAgRlJPTSBwbl9ncm91cF9tZW1iZXJzKGdwczEucG5pZCxGQUxTRSwnezEsNiw1LDN9Jykg Z20sIHNlYXJjaF9ncHMgZ3BzMQ0KwqDCoMKgDQrCoMKgwqAgV0hFUkUgTk9UIENZQ0xFDQop wqDCoMKgDQpTRUxFQ1QgKiBmcm9tIGdwczsNCg0KDQpDUkVBVEUgT1IgUkVQTEFDRSBGVU5D VElPTiBwdWJsaWMucG5fZ3JvdXBfbWVtYmVycygNCsKgwqDCoCBfcG5pZCBpbnRlZ2VyLA0K wqDCoMKgIF9wcmltYXJ5X29ubHkgYm9vbGVhbiwgLS1saW1pdCB0byBncm91cHMgd2hlcmUg cG5pZCBpcyB0aGUgcHJpbWFyeQ0KZ3JvdXAgbWVtYmVyDQrCoMKgwqAgX2dyb3VwcyBpbnRl Z2VyW10gLS0gbGltaXQgZ3JvdXBzIHRvIHRoZXNlIChpZCBmcm9tIHMwcG5ncm91cHMgKQ0K J3t9JyBmb3IgYWxsIHBvc3NpYmxlIGdyb3Vwcw0KKQ0KwqDCoMKgIFJFVFVSTlMgVEFCTEUo cG5ncm91cCBpbnRlZ2VyLCBwcmltYXJ5cG4gc21hbGxpbnQsIHBuaWQgaW50ZWdlciwNCmdy b3VwdHlwZSBpbnRlZ2VyKQ0KwqDCoMKgIExBTkdVQUdFICdwbHBnc3FsJw0KDQrCoMKgwqAg Q09TVCAxMDANCsKgwqDCoCBWT0xBVElMRQ0KwqDCoMKgIFJPV1MgMTAwMA0KQVMgJEJPRFkk REVDTEFSRQ0KwqDCoMKgIGdyb3VwbGlzdCBURVhUIDo9ICcnOw0KwqDCoMKgIGdyb3VwaWTC oCBJTlRFR0VSOw0KwqDCoMKgDQrCoMKgwqAgcHJpbWFyeW9ubHkgVEVYVCA6PScnOw0KwqDC oMKgDQpCRUdJTg0KwqDCoMKgIC0tcHV0IHRoZSBncm91cHMgaW4gYSBsaXN0DQrCoMKgwqAg Rk9SRUFDSCBncm91cGlkIElOIEFSUkFZIF9ncm91cHMNCsKgwqDCoCBMT09QDQrCoMKgwqAg wqAgaWYgKCBncm91cGxpc3Q9JycpIHRoZW4NCsKgwqDCoCDCoCDCoMKgwqAgZ3JvdXBsaXN0 IDo9IGdyb3VwaWQ7DQrCoMKgwqAgwqAgRUxTRQ0KwqDCoMKgIMKgIMKgwqDCoCBncm91cGxp c3QgOj3CoMKgwqAgZ3JvdXBsaXN0IHx8ICcsJyB8fCBncm91cGlkOw0KwqDCoMKgIMKgIEVO RCBJRjsNCsKgwqDCoCBFTkQgTE9PUDsNCg0KwqDCoMKgIGlmIE5PVCAoZ3JvdXBsaXN0wqAg PSAnJykgVEhFTg0KwqDCoMKgIMKgwqDCoCBncm91cGxpc3QgOj0gJyBBTkQgZzEuZ3JvdXB0 eXBlZmtleSBJTiAoJyB8fCBncm91cGxpc3QgfHwnKSc7DQrCoMKgwqAgRU5EIElGOw0KwqDC oMKgDQrCoMKgwqAgaWYgX3ByaW1hcnlfb25seSBUSEVODQrCoMKgwqAgwqDCoMKgIHByaW1h cnlvbmx5IDo9ICdBTkQgeGwucHJpbWFyeXBuID0gMSc7DQrCoMKgwqAgRU5EIElGOw0KwqDC oMKgDQrCoMKgwqAgUkVUVVJOIFFVRVJZIEVYRUNVVEUgJ1NFTEVDVCBESVNUSU5DVCB4bDIu UE5Hcm91cEZrZXkswqANCnhsMi5QcmltYXJ5UE7CoMKgICwgeGwyLlBORktFWSAsIGcxLkdy b3VwVHlwZUZrZXknDQrCoMKgwqDCoMKgwqDCoMKgwqDCoCB8fCAnIEZST00gczBwbmdyb3Vw eGwgeGwnDQoNCsKgwqDCoCDCoMKgwqAgwqDCoCB8fCAnIExFRlQgSk9JTsKgIFMwUE5HUk9V UFMgZzENCsKgwqDCoCDCoMKgwqAgwqDCoMKgIMKgwqDCoCBPTiB4bC5wbmdyb3VwZmtleSA9 IGcxLmlkJw0KDQrCoMKgwqDCoMKgwqDCoMKgwqDCoCB8fCAnIExFRlQgSk9JTiBzMHBuZ3Jv dXB4bCB4bDINCsKgwqDCoMKgIMKgwqDCoCDCoMKgwqAgwqDCoMKgIE9OIGcxLmlkID0geGwy LnBuZ3JvdXBma2V5Jw0KDQrCoMKgwqAgwqDCoMKgIMKgIHx8ICcgV0hFUkUNCsKgwqDCoMKg IMKgwqDCoCDCoMKgwqAgwqDCoCB4bC5QbkZrZXkgPSAnIHx8X3BuaWQNCsKgwqDCoCDCoMKg wqDCoA0KwqANCsKgwqDCoCDCoMKgwqDCoMKgIHx8ICcgJyB8fCBncm91cGxpc3QNCsKgwqDC oCDCoMKgwqAgwqAgfHwgJyAnIHx8IHByaW1hcnlvbmx5DQrCoMKgwqDCoMKgwqDCoMKgwqDC oCDCoMKgwqAgfHwgJyBPUkRFUiBCWSB4bDIuUE5Hcm91cEZrZXksIHhsMi5QcmltYXJ5UE4n Ow0KwqDCoMKgIMKgwqDCoA0KwqDCoMKgIFJFVFVSTjvCoMKgwqAgwqDCoMKgDQpFTkQ7DQok Qk9EWSQ7DQoNCg0KDQpDUkVBVEUgVEFCTEUgcHVibGljLnMwcG5ncm91cHhsDQooDQrCoMKg wqAgaWQgaW50ZWdlciBOT1QgTlVMTCwNCsKgwqDCoCBwbmdyb3VwZmtleSBpbnRlZ2VyLA0K wqDCoMKgIHBuZmtleSBpbnRlZ2VyLA0KwqDCoMKgIHByaW1hcnlwbiBzbWFsbGludCwNCsKg wqDCoCBzb3J0b3JkZXIgcmVhbCwNCsKgwqDCoCB1cGRhdGVkZGF0ZXRpbWUgdGltZXN0YW1w IHdpdGhvdXQgdGltZSB6b25lLA0KwqDCoMKgIHVwZGF0ZWRlbXBma2V5IGludGVnZXIsDQrC oMKgwqAgQ09OU1RSQUlOVCAiUzBQTkdST1VQWExfcGtleSIgUFJJTUFSWSBLRVkgKGlkKSwN CsKgwqDCoCBDT05TVFJBSU5UIHMwcG5ncm91cHhsX3BuZmtleV9ma2V5IEZPUkVJR04gS0VZ IChwbmZrZXkpDQrCoMKgwqDCoMKgwqDCoCBSRUZFUkVOQ0VTIHB1YmxpYy5wYXJ0bnVtIChp ZCkgTUFUQ0ggU0lNUExFDQrCoMKgwqDCoMKgwqDCoCBPTiBVUERBVEUgQ0FTQ0FERQ0KwqDC oMKgwqDCoMKgwqAgT04gREVMRVRFIENBU0NBREUNCsKgwqDCoMKgwqDCoMKgIE5PVCBWQUxJ RCwNCsKgwqDCoCBDT05TVFJBSU5UIHMwcG5ncm91cHhsX3BuZ3JvdXBma2V5X2ZrZXkgRk9S RUlHTiBLRVkgKHBuZ3JvdXBma2V5KQ0KwqDCoMKgwqDCoMKgwqAgUkVGRVJFTkNFUyBwdWJs aWMuczBwbmdyb3VwcyAoaWQpIE1BVENIIFNJTVBMRQ0KwqDCoMKgwqDCoMKgwqAgT04gVVBE QVRFIENBU0NBREUNCsKgwqDCoMKgwqDCoMKgIE9OIERFTEVURSBDQVNDQURFDQrCoMKgwqDC oMKgwqDCoCBOT1QgVkFMSUQNCikNCldJVEggKA0KwqDCoMKgIE9JRFMgPSBGQUxTRQ0KKQ0K VEFCTEVTUEFDRSBwZ19kZWZhdWx0Ow0KDQoNCkNSRUFURSBUQUJMRSBwdWJsaWMuczBwbmdy b3Vwcw0KKA0KwqDCoMKgIGlkIGludGVnZXIgTk9UIE5VTEwsDQrCoMKgwqAgZ3JvdXB0eXBl ZmtleSBpbnRlZ2VyLA0KwqDCoMKgIHVwZGF0ZWRlbXBma2V5IGludGVnZXIsDQrCoMKgwqAg dXBkYXRlZGRhdGV0aW1lIHRpbWVzdGFtcCB3aXRob3V0IHRpbWUgem9uZSwNCsKgwqDCoCBt b3N0cmVjZW50cmV2IHNtYWxsaW50IERFRkFVTFQgMCwNCsKgwqDCoCBub3RlIHRleHQgQ09M TEFURSBwZ19jYXRhbG9nLiJkZWZhdWx0IiwNCsKgwqDCoCBDT05TVFJBSU5UICJTMFBOR1JP VVBTX3BrZXkiIFBSSU1BUlkgS0VZIChpZCksDQrCoMKgwqAgQ09OU1RSQUlOVCBzMHBuZ3Jv dXBzX2dyb3VwdHlwZWZrZXlfZmtleSBGT1JFSUdOIEtFWSAoZ3JvdXB0eXBlZmtleSkNCsKg wqDCoMKgwqDCoMKgIFJFRkVSRU5DRVMgcHVibGljLnMwcG5ncm91cHR5cGVsayAoaWQpIE1B VENIIFNJTVBMRQ0KwqDCoMKgwqDCoMKgwqAgT04gVVBEQVRFIENBU0NBREUNCsKgwqDCoMKg wqDCoMKgIE9OIERFTEVURSBDQVNDQURFDQrCoMKgwqDCoMKgwqDCoCBOT1QgVkFMSUQNCikN CldJVEggKA0KwqDCoMKgIE9JRFMgPSBGQUxTRQ0KKQ0KVEFCTEVTUEFDRSBwZ19kZWZhdWx0 Ow0KDQotLSANCg0KTWFyayBCDQoNCg== --------------3987899B42381D1E407B9A4D Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

Postgresql 11 (will be updating to 12 soon)

What is the best way to make the function 'pn_group_members' be recursive?  I'm trying with a recursive CTE query but not getting it right evidently. Following https://www.postgresql.org/docs/11/queries-with.html at bottom of the page.

The table s0pngroupxl is groups part numbers into groups.  Groups are defined by s0pngroups and s0pngroupxl are the members of each group.  Function pn_group_members returns for the requested part number, a table with all the part numbers in the group where that part number is a member.  There are cases where I require to have this to  be recursive and return all the groups for any found part numbers (without infinite looping). 



WITH RECURSIVE search_gps (pnid, groupid, depth, path,cycle ) AS (
    SELECT gm.pnid, gm.pngroup
    ,1
    ,ARRAY[ROW(gm.pngroup,gm.pnid)]
    ,FALSE

    FROM pn_group_members(17344,FALSE,'{1,6,5,3}') gm
UNION ALL
    SELECT gm.pnid, gm.pngroup
    , gps1.depth+1
    ,path || ROW(gm.pngroup,gm.pn.pnid)
    ,ROW(gm.pngroup,gm.pnid) = ANY(path)

    FROM pn_group_members(gps1.pnid,FALSE,'{1,6,5,3}') gm, search_gps gps1
   
    WHERE NOT CYCLE
)   
SELECT * from gps;


CREATE OR REPLACE FUNCTION public.pn_group_members(
    _pnid integer,
    _primary_only boolean, --limit to groups where pnid is the primary group member
    _groups integer[] -- limit groups to these (id from s0pngroups ) '{}' for all possible groups
)
    RETURNS TABLE(pngroup integer, primarypn smallint, pnid integer, grouptype integer)
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE
    ROWS 1000
AS $BODY$DECLARE
    grouplist TEXT := '';
    groupid  INTEGER;
   
    primaryonly TEXT :='';
   
BEGIN
    --put the groups in a list
    FOREACH groupid IN ARRAY _groups
    LOOP
      if ( grouplist='') then
          grouplist := groupid;
      ELSE
          grouplist :=    grouplist || ',' || groupid;
      END IF;
    END LOOP;

    if NOT (grouplist  = '') THEN
        grouplist := ' AND g1.grouptypefkey IN (' || grouplist ||')';
    END IF;
   
    if _primary_only THEN
        primaryonly := 'AND xl.primarypn = 1';
    END IF;
   
    RETURN QUERY EXECUTE 'SELECT DISTINCT xl2.PNGroupFkey,  xl2.PrimaryPN   , xl2.PNFKEY , g1.GroupTypeFkey'
           || ' FROM s0pngroupxl xl'

           || ' LEFT JOIN  S0PNGROUPS g1
                ON xl.pngroupfkey = g1.id'

           || ' LEFT JOIN s0pngroupxl xl2
                 ON g1.id = xl2.pngroupfkey'

          || ' WHERE
                xl.PnFkey = ' ||_pnid
        
 
          || ' ' || grouplist
          || ' ' || primaryonly
               || ' ORDER BY xl2.PNGroupFkey, xl2.PrimaryPN';
       
    RETURN;       
END;
$BODY$;



CREATE TABLE public.s0pngroupxl
(
    id integer NOT NULL,
    pngroupfkey integer,
    pnfkey integer,
    primarypn smallint,
    sortorder real,
    updateddatetime timestamp without time zone,
    updatedempfkey integer,
    CONSTRAINT "S0PNGROUPXL_pkey" PRIMARY KEY (id),
    CONSTRAINT s0pngroupxl_pnfkey_fkey FOREIGN KEY (pnfkey)
        REFERENCES public.partnum (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
        NOT VALID,
    CONSTRAINT s0pngroupxl_pngroupfkey_fkey FOREIGN KEY (pngroupfkey)
        REFERENCES public.s0pngroups (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
        NOT VALID
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;


CREATE TABLE public.s0pngroups
(
    id integer NOT NULL,
    grouptypefkey integer,
    updatedempfkey integer,
    updateddatetime timestamp without time zone,
    mostrecentrev smallint DEFAULT 0,
    note text COLLATE pg_catalog."default",
    CONSTRAINT "S0PNGROUPS_pkey" PRIMARY KEY (id),
    CONSTRAINT s0pngroups_grouptypefkey_fkey FOREIGN KEY (grouptypefkey)
        REFERENCES public.s0pngrouptypelk (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
        NOT VALID
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

--

Mark B

--------------3987899B42381D1E407B9A4D--