162 lines
4.8 KiB
Markdown
162 lines
4.8 KiB
Markdown
Допустим, что мы хотим изменить представление document_details
|
||
```sql
|
||
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. Определить зависимости представления
|
||
|
||
```sql
|
||
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;
|
||
```
|
||
получим вывод
|
||
```csv
|
||
view_oid,"view_name","schema_name","depends_on"
|
||
24685,"document_details","public",NULL
|
||
24689,"author_documents","public","document_details"
|
||
```
|
||
|
||
### 2.Сохранить текущие скрипты создания представлений
|
||
|
||
```sql
|
||
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');
|
||
|
||
```
|
||
получим вывод
|
||
```csv
|
||
"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. Получить корректный порядок удаления
|
||
|
||
```sql
|
||
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 представление
|
||
```sql
|
||
DROP VIEW IF EXISTS public.document_details CASCADE;
|
||
```
|
||
|
||
### 5. Обновляем document_details с измененной структурой
|
||
```sql
|
||
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
|
||
```sql
|
||
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;
|
||
```
|