Query

What became of a saved list of references

For each URN of a comma-separated list, its status (current, merged, retracted, unknown), the URN that stands for it now, its current name and the groups of that subject now.

urns (text) The SQL (always shown and editable; runs on a read-only connection with a 5 second limit and at most 500 rows)

WITH r AS (
    SELECT trim(x.ref) AS ref, x.n FROM unnest(string_to_array(%(urns)s, ',')) WITH ORDINALITY AS x (ref, n)
    WHERE trim(x.ref) <> ''
), s AS (  -- each reference's subject (an index read of its UUID) and the subject that stands for it now
    SELECT r.ref, r.n, x.id, canonical_of(x.id) AS cid
    FROM r
    LEFT JOIN LATERAL (SELECT subject_of_urn(r.ref) AS id) u ON true
    LEFT JOIN subject x ON x.id = u.id AND x.kind <> 'offering'
)
SELECT s.ref AS urn, CASE WHEN s.id IS NULL THEN 'unknown' ELSE subject_status(s.id) END AS status,
       subject_urn(s.cid) AS stands_for_now,
       (SELECT c.value #>> '{}'  -- the name rule of v_subject_name, for this subject only
        FROM current_values_now(ARRAY['urn:matchory:attribute:profile:legal_name',
                                      'urn:matchory:attribute:profile:trade_name'], ARRAY[s.cid]) c
        ORDER BY (c.attribute_urn = 'urn:matchory:attribute:profile:legal_name') DESC, c.observation_id
        LIMIT 1) AS current_name,
       (SELECT string_agg(DISTINCT COALESCE(
                   (SELECT c.value #>> '{}'
                    FROM current_values_now(ARRAY['urn:matchory:attribute:profile:legal_name',
                                                  'urn:matchory:attribute:profile:trade_name'],
                                            ARRAY[canonical_of(m.group_id)]) c
                    ORDER BY (c.attribute_urn = 'urn:matchory:attribute:profile:legal_name') DESC, c.observation_id
                    LIMIT 1),
                   subject_urn(canonical_of(m.group_id))), ', ')
        FROM membership m
        WHERE m.entity_id IN (SELECT s.cid UNION SELECT subject_id FROM canonical_subject WHERE canonical_id = s.cid)
          AND (upper(m.valid_during) IS NULL OR upper(m.valid_during) > now())
       ) AS groups_now
FROM s
ORDER BY s.n