Thread: Recursive CTE with a function

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;