---

WITH targets AS (
    SELECT
        'QueryAction' AS object_type,
        to_jsonb(a) ->> 'code' AS object_code
    FROM dev.query_action a
    WHERE a.id = 34715

    UNION ALL

    SELECT
        'QueryTable',
        to_jsonb(qt) ->> 'code'
    FROM dev.query_table qt
    WHERE qt.id = 34986
),
pages AS (
    SELECT
        'dev.VIEW' AS source_table,
        to_jsonb(v) ->> 'id' AS page_id,
        to_jsonb(v) ->> 'code' AS page_code,
        to_jsonb(v) ->> 'name' AS page_name,
        v.content::text AS content
    FROM dev."VIEW" v

    UNION ALL

    SELECT
        'dev_local.view',
        to_jsonb(v) ->> 'id',
        to_jsonb(v) ->> 'code',
        to_jsonb(v) ->> 'name',
        v.content::text
    FROM dev_local."view" v

    UNION ALL

    SELECT
        'dev.template_view',
        to_jsonb(v) ->> 'id',
        to_jsonb(v) ->> 'code',
        to_jsonb(v) ->> 'name',
        v.content::text
    FROM dev.template_view v
)
SELECT
    p.source_table,
    p.page_id,
    p.page_code,
    p.page_name,
    t.object_type,
    t.object_code,
    substring(
        p.content
        FROM greatest(strpos(p.content, t.object_code) - 150, 1)
        FOR 500
    ) AS matching_fragment
FROM pages p
JOIN targets t
    ON strpos(p.content, t.object_code) > 0
WHERE t.object_code IS NOT NULL
  AND t.object_code <> ''
ORDER BY p.source_table, p.page_code, t.object_type;