pgmt 0.4.8

PostgreSQL migration tool that keeps your schema files as the source of truth
Documentation 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116

use anyhow::Result;
use sqlx::postgres::PgConnection;
use tracing::info;

use crate::catalog::{DependsOn, comments::Commentable, id::DbObjectId};

/// Represents a PostgreSQL trigger
#[derive(Debug, Clone, PartialEq, Eq)]
pub struct Trigger {
    pub schema: String,
    pub table_name: String,
    pub name: String,
    pub function_schema: String,
    pub function_name: String,
    pub function_args: String,
    pub comment: Option<String>,
    pub depends_on: Vec<DbObjectId>,

    /// Complete trigger definition from pg_get_triggerdef()
    /// This is the authoritative source for trigger recreation
    pub definition: String,
}

impl DependsOn for Trigger {
    fn id(&self) -> DbObjectId {
        DbObjectId::Trigger {
            schema: self.schema.clone(),
            table: self.table_name.clone(),
            name: self.name.clone(),
        }
    }

    fn depends_on(&self) -> &[DbObjectId] {
        &self.depends_on
    }
}

impl Commentable for Trigger {
    fn comment(&self) -> &Option<String> {
        &self.comment
    }
}

/// Fetch all triggers from the database
pub async fn fetch(conn: &mut PgConnection) -> Result<Vec<Trigger>> {
    info!("Fetching triggers...");
    let triggers = sqlx::query!(
        r#"
        SELECT
            tn.nspname AS table_schema,
            c.relname AS table_name,
            t.tgname AS trigger_name,

            -- Function details
            p.proname AS function_name,
            fn.nspname AS function_schema,
            pg_catalog.pg_get_function_identity_arguments(p.oid) AS "function_args!",

            -- Comments
            d.description AS "comment?",

            -- Complete trigger definition (authoritative source)
            pg_get_triggerdef(t.oid) AS "definition!"

        FROM pg_trigger t
        JOIN pg_class c ON t.tgrelid = c.oid
        JOIN pg_namespace tn ON c.relnamespace = tn.oid
        JOIN pg_proc p ON t.tgfoid = p.oid
        JOIN pg_namespace fn ON p.pronamespace = fn.oid
        LEFT JOIN pg_description d ON d.objoid = t.oid AND d.objsubid = 0

        WHERE tn.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
          AND NOT t.tgisinternal  -- Exclude system-generated triggers
          AND c.relkind IN ('r', 'v', 'm')  -- Regular tables, views, materialized views

        ORDER BY tn.nspname, c.relname, t.tgname
        "#
    )
    .fetch_all(&mut *conn)
    .await?;

    let mut result = Vec::new();

    for row in triggers {
        // Build dependencies
        let depends_on = vec![
            // Triggers depend on their table
            DbObjectId::Table {
                schema: row.table_schema.clone(),
                name: row.table_name.clone(),
            },
            // Triggers depend on their function
            DbObjectId::Function {
                schema: row.function_schema.clone(),
                name: row.function_name.clone(),
                arguments: row.function_args.clone(),
            },
        ];

        let trigger = Trigger {
            schema: row.table_schema,
            table_name: row.table_name,
            name: row.trigger_name,
            function_schema: row.function_schema,
            function_name: row.function_name,
            function_args: row.function_args,
            comment: row.comment,
            depends_on,
            definition: row.definition,
        };

        result.push(trigger);
    }

    Ok(result)
}