Update Family Release Dates

Before the products.json file can be created, the family table's release_date column must be updated with the latest release date found in the release set. This stored procedure does that.

This is the Kyu version of the query to update the release_date column in the family table. In Ocho, the update was done with whatever release set dates were in the release-set table. That didn't provide the option to roll-back the current releases by excluding release_set rows by release_date.

This rollback feature offers the ability to exclude one more release sets (by date) that might be recalled. This doesn't happen much, but it has happened.

By default, this query does what the Ocho query did. However, it optionally accepts one or more release set dates to exclude from the update.

CREATE OR REPLACE PROCEDURE public.update_family_release_dates(exclude_dates DATE[] DEFAULT NULL)
 LANGUAGE sql
AS $procedure$
WITH UniqueDates AS (
    SELECT family_id, MAX(release_date) as most_recent_release
    FROM release_set_normalized
    WHERE 
        -- If the array is NULL, everything is included.
        -- If the array has values, check that the release_date is NOT in that array.
        (exclude_dates IS NULL OR NOT (release_date = ANY(exclude_dates)))
        AND release_date IS NOT NULL
    GROUP BY family_id
)
UPDATE family AS f 
SET release_date = ud.most_recent_release
FROM UniqueDates ud
WHERE f.id = ud.family_id;
$procedure$;

--Call with no dates
CALL update_family_release_dates();

--Call with one date
CALL update_family_release_dates(ARRAY['2023-12-25']::DATE[]);

-- Call with two dates
CALL update_family_release_dates(ARRAY['2023-12-25', '2024-01-01']::DATE[]);

-- Shorthand, call with two dates
CALL update_family_release_dates('{2023-12-25, 2024-01-01}');

Read about release-set-normalized here. It is a normalized view of release_set and the tables that comprise release_set's foreign keys.

The steps to update the family table release_date are:

Step 1. UniqueDates

Creates a result that provides the most recent release date for each family (an example with three rows is shown below)

family_id	family_name	release_date
156	DataGate 17.1	2026-04-07
145	DataGate 17.0 - Beta	2024-10-18
157	DataGate 17.1 - Beta	2026-03-03
That release set is then used to update the release_date in the family table.

Archival queries as the new Kyu scheme evolved.

This is identical to the Ocho query--it's simpler. However, its output is the same as the Ocho query. It unconditionally includes all release set dates.

CREATE OR REPLACE PROCEDURE public.update_family_release_dates()
 LANGUAGE sql
AS $procedure$
WITH UniqueDates AS (
    SELECT family_id, family_name, MAX(release_date) as most_recent_release
    FROM release_set_normalized
    GROUP BY family_id, family_name
)

UPDATE family AS f SET release_date =
(
    SELECT release_date 
	FROM lastReleaseDate
    WHERE lastReleaseDate.family_id = f.id
) 
$procedure$;

This was the first pass at the Kyu query, before it occurred to that it might be good to exclude one more dates.

CREATE OR REPLACE PROCEDURE public.update_family_release_dates(exclude_date DATE DEFAULT NULL)
 LANGUAGE sql
AS $procedure$
WITH UniqueDates AS (
    SELECT family_id, MAX(release_date) as most_recent_release
    FROM release_set_normalized
    WHERE 
        -- This logic handles the optional exclusion:
        (exclude_date IS NULL OR release_date != exclude_date)
        -- Ensure we don't try to process actual NULL dates
        AND release_date IS NOT NULL
    GROUP BY family_id
)
UPDATE family AS f 
SET release_date = ud.most_recent_release
FROM UniqueDates ud
WHERE f.id = ud.family_id;
$procedure$;