From: Mark Bannister <mark@injection-moldings.com>
To: pgsql-sql@postgresql.org
Subject: Recursive CTE with a function
Date: Wed, 3 Jun 2020 08:52:40 -0500
Message-ID: <b7cd727d-8010-857d-8755-19559cef37fb@injection-moldings.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mark@injection-moldings.com
Subject: Re: Recursive CTE with a function
In-Reply-To: <b7cd727d-8010-857d-8755-19559cef37fb@injection-moldings.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox