postgreSQL_def/how_to_update_views.md

Допустим, что мы хотим изменить представление document_details

CREATE OR REPLACE VIEW public.document_details AS
SELECT 
    d.doc_id,
    d.title,
    dt.type_name,
    ldv.content,
    ldv.updated_at,
    d.created_at -- Новый столбец
FROM documents d
JOIN document_types dt ON d.type_id = dt.type_id
LEFT JOIN latest_document_versions ldv ON d.doc_id = ldv.doc_id;

1. Определить зависимости представления

WITH RECURSIVE view_dependencies AS (
    -- Начинаем с document_details
    SELECT 
        c.oid AS view_oid,
        c.relname AS view_name,
        n.nspname AS schema_name,
        NULL::text COLLATE "C" AS depends_on -- явная коллация
    FROM pg_class c
    JOIN pg_namespace n ON c.relnamespace = n.oid
    WHERE c.relkind = 'v' -- Только представления
        AND c.relname = 'document_details'

    UNION ALL

    -- Находим представления, зависящие от текущего списка
    SELECT 
        v.oid AS view_oid,
        v.relname AS view_name,
        n.nspname AS schema_name,
        d.view_name COLLATE "C" AS depends_on -- явная коллация
    FROM pg_class v
    JOIN pg_namespace n ON v.relnamespace = n.oid
    JOIN pg_views pv ON pv.schemaname = n.nspname AND pv.viewname = v.relname
    JOIN view_dependencies d ON pv.definition ILIKE '%' || d.view_name || '%'
    WHERE v.relkind = 'v' -- Только представления
)
SELECT * FROM view_dependencies;

получим вывод

view_oid,"view_name","schema_name","depends_on"
24685,"document_details","public",NULL
24689,"author_documents","public","document_details"

2.Сохранить текущие скрипты создания представлений

SELECT format('CREATE OR REPLACE VIEW %I.%I AS %s;', 
              n.nspname, c.relname, pg_get_viewdef(c.oid, true)) AS create_script
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE c.relkind = 'v' 
    AND n.nspname = 'public'
    AND c.relname IN ('document_details', 'author_documents');

получим вывод

"create_script"
"CREATE OR REPLACE VIEW public.author_documents AS  SELECT a.name AS author_name,
    a.email,
    dd.doc_id,
    dd.title,
    dd.type_name,
    dd.content,
    dd.updated_at
   FROM authors a
     JOIN document_authors da ON a.author_id = da.author_id
     JOIN document_details dd ON da.doc_id = dd.doc_id;;"
"CREATE OR REPLACE VIEW public.document_details AS  SELECT d.doc_id,
    d.title,
    dt.type_name,
    ldv.content,
    ldv.updated_at,
    d.created_at
   FROM documents d
     JOIN document_types dt ON d.type_id = dt.type_id
     LEFT JOIN latest_document_versions ldv ON d.doc_id = ldv.doc_id;;"

3. Получить корректный порядок удаления

WITH RECURSIVE dependencies AS (
    SELECT 
        c.oid,
        c.relname AS object_name,
        n.nspname AS schema_name,
        'VIEW' AS object_type,
        NULL::text AS depends_on
    FROM pg_class c
    JOIN pg_namespace n ON c.relnamespace = n.oid
    WHERE c.relkind = 'v' 
        AND c.relname = 'document_details'

    UNION ALL

    SELECT 
        d.refobjid AS oid,
        c.relname AS object_name,
        n.nspname AS schema_name,
        'VIEW' AS object_type,
        d.objid::regclass::text AS depends_on
    FROM pg_depend d
    JOIN pg_class c ON d.refobjid = c.oid
    JOIN pg_namespace n ON c.relnamespace = n.oid
    WHERE d.classid = 'pg_class'::regclass
        AND d.refclassid = 'pg_class'::regclass
        AND c.relkind = 'v'
)
SELECT format('DROP VIEW IF EXISTS %I.%I CASCADE;', schema_name, object_name) AS drop_script
FROM dependencies
ORDER BY object_type DESC;

4. Удалить полученным скриптом из п.3 представление

DROP VIEW IF EXISTS public.document_details CASCADE;

5. Обновляем document_details с измененной структурой

CREATE OR REPLACE VIEW public.document_details AS
SELECT 
    d.doc_id,
    d.title,
    dt.type_name,
    ldv.content,
    ldv.updated_at,
    d.created_at -- Добавили поле created_at
FROM documents d
JOIN document_types dt ON d.type_id = dt.type_id
LEFT JOIN latest_document_versions ldv ON d.doc_id = ldv.doc_id;

6. Восстанавливаем необходимое, скриптами из п.2

CREATE OR REPLACE VIEW public.author_documents AS  SELECT a.name AS author_name,
    a.email,
    dd.doc_id,
    dd.title,
    dd.type_name,
    dd.content,
    dd.updated_at
   FROM authors a
     JOIN document_authors da ON a.author_id = da.author_id
     JOIN document_details dd ON da.doc_id = dd.doc_id;