Query

Everything known about one URN, with evidence

The current values of the subject a URN stands for, each with its evidence class, source, quote and page. A merged URN shows the surviving subject. Leave the date empty for now. Personal data (officers, owners, contact people) only with personal = 1.

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

WITH s AS (  -- the one subject (an index read of its UUID), and the canonical subject it stands for now
    SELECT canonical_of(id) AS cid FROM subject
    WHERE id = (SELECT subject_of_urn(%(urn)s)) AND kind <> 'offering'
), c AS (  -- its current values: read from the projection for now, computed for one subject for a date
    SELECT v.subject_id, v.attribute_urn, v.observation_id, v.value, v.evidence_class, v.source, v.confidence,
           v.observed_at, v.disagreeing_sources
    FROM s, current_values_now(NULL, ARRAY[s.cid]) v WHERE %(as_of)s::date IS NULL
    UNION ALL
    SELECT v.subject_id, v.attribute_urn, v.observation_id, v.value, v.evidence_class, v.source, v.confidence,
           v.observed_at, v.disagreeing_sources
    FROM s, current_values(%(as_of)s::timestamptz, NULL, ARRAY[s.cid]) v WHERE %(as_of)s::date IS NOT NULL
)
SELECT subject_urn(c.subject_id) AS subject, c.attribute_urn AS attribute, c.value, c.evidence_class, c.source,
       c.confidence, c.observed_at, c.disagreeing_sources, ev.quote, ev.url AS page, ev.retrieved_at
FROM c
LEFT JOIN evidence ev ON ev.observation_id = c.observation_id AND ev.retracted_at IS NULL
WHERE (%(personal)s = 1 OR c.attribute_urn NOT IN (SELECT urn FROM attribute WHERE personal_data))
  AND NOT starts_with(c.attribute_urn, 'urn:matchory:attribute:custom:')  -- a customer's own, as v_current
ORDER BY c.attribute_urn, c.observed_at, ev.quote