PostgreSQL MCP Server

Instructions

Implementation Reference

• src/index.ts:225-257 (registration)

  Central registration of all tools in allTools array, including manageTriggersTools (pg_manage_triggers) at line 233, which is passed to the PostgreSQLServer constructor.

  const allTools: PostgresTool[] = [
    // Core Analysis & Debugging
    analyzeDatabaseTool,
    debugDatabaseTool,
    
    // Schema & Structure Management (Meta-Tools)
    manageSchemaTools,
    manageFunctionsTool,
    manageTriggersTools,
    manageIndexesTool,
    manageConstraintsTool,
    manageRLSTool,
    
    // User & Security Management
    manageUsersTool,
    
    // Query & Performance Management
    manageQueryTool,
    
    // Data Operations (Enhancement Tools)
    executeQueryTool,
    executeMutationTool,
    executeSqlTool,
    
    // Documentation & Metadata
    manageCommentsTool,
    
    // Data Migration & Monitoring
    exportTableDataTool,
    importTableDataTool,
    copyBetweenDatabasesTool,
    monitorDatabaseTool
  ];

• src/tools/triggers.ts:555-701 (handler)

  The core handler implementation: the PostgresTool object for pg_manage_triggers, including the execute function that parses input, switches on 'operation' parameter, and delegates to specific helper execute functions for each trigger management action.

  export const manageTriggersTools: PostgresTool = {
    name: 'pg_manage_triggers',
    description: 'Manage PostgreSQL triggers - get, create, drop, and enable/disable triggers. Examples: operation="get" to list triggers, operation="create" with triggerName, tableName, functionName, operation="drop" with triggerName and tableName, operation="set_state" with triggerName, tableName, enable',
    inputSchema: z.object({
      connectionString: z.string().optional().describe('PostgreSQL connection string (optional)'),
      operation: z.enum(['get', 'create', 'drop', 'set_state']).describe('Operation: get (list triggers), create (new trigger), drop (remove trigger), set_state (enable/disable trigger)'),
      
      // Common parameters
      schema: z.string().optional().describe('Schema name (defaults to public)'),
      tableName: z.string().optional().describe('Table name (optional filter for get, required for create/drop/set_state)'),
      
      // Trigger identification (for create/drop/set_state)
      triggerName: z.string().optional().describe('Trigger name (required for create/drop/set_state)'),
      
      // Create trigger parameters
      functionName: z.string().optional().describe('Function name (required for create operation)'),
      timing: z.enum(['BEFORE', 'AFTER', 'INSTEAD OF']).optional().describe('Trigger timing (for create operation, defaults to AFTER)'),
      events: z.array(z.enum(['INSERT', 'UPDATE', 'DELETE', 'TRUNCATE'])).optional().describe('Trigger events (for create operation, defaults to ["INSERT"])'),
      forEach: z.enum(['ROW', 'STATEMENT']).optional().describe('FOR EACH ROW or STATEMENT (for create operation, defaults to ROW)'),
      when: z.string().optional().describe('WHEN clause condition (for create operation)'),
      replace: z.boolean().optional().describe('Whether to replace trigger if exists (for create operation)'),
      
      // Drop trigger parameters
      ifExists: z.boolean().optional().describe('Include IF EXISTS clause (for drop operation)'),
      cascade: z.boolean().optional().describe('Include CASCADE clause (for drop operation)'),
      
      // Set state parameters
      enable: z.boolean().optional().describe('Whether to enable the trigger (required for set_state operation)')
    }),
    // biome-ignore lint/suspicious/noExplicitAny: <explanation>
    execute: async (args: any, getConnectionStringVal: GetConnectionStringFn): Promise<ToolOutput> => {
      const { 
        connectionString: connStringArg,
        operation,
        schema,
        tableName,
        triggerName,
        functionName,
        timing,
        events,
        forEach,
        when,
        replace,
        ifExists,
        cascade,
        enable
      } = args as {
        connectionString?: string;
        operation: 'get' | 'create' | 'drop' | 'set_state';
        schema?: string;
        tableName?: string;
        triggerName?: string;
        functionName?: string;
        timing?: 'BEFORE' | 'AFTER' | 'INSTEAD OF';
        events?: ('INSERT' | 'UPDATE' | 'DELETE' | 'TRUNCATE')[];
        forEach?: 'ROW' | 'STATEMENT';
        when?: string;
        replace?: boolean;
        ifExists?: boolean;
        cascade?: boolean;
        enable?: boolean;
      };
  
      try {
        switch (operation) {
          case 'get': {
            const result = await executeGetTriggers({
              connectionString: connStringArg,
              tableName,
              schema: schema || 'public'
            }, getConnectionStringVal);
            const message = tableName 
              ? `Triggers for table ${schema || 'public'}.${tableName}` 
              : `Found ${result.length} triggers in schema ${schema || 'public'}`;
            return { content: [{ type: 'text', text: message }, { type: 'text', text: JSON.stringify(result, null, 2) }] };
          }
  
          case 'create': {
            if (!triggerName || !tableName || !functionName) {
              return { 
                content: [{ type: 'text', text: 'Error: triggerName, tableName, and functionName are required for create operation' }], 
                isError: true 
              };
            }
            const result = await executeCreateTrigger({
              connectionString: connStringArg,
              triggerName,
              tableName,
              functionName,
              schema: schema || 'public',
              timing: timing || 'AFTER',
              events: events || ['INSERT'],
              forEach: forEach || 'ROW',
              when,
              replace: replace || false
            }, getConnectionStringVal);
            return { content: [{ type: 'text', text: `Trigger ${result.name} created successfully on ${result.schema}.${result.table}` }, { type: 'text', text: JSON.stringify(result, null, 2) }] };
          }
  
          case 'drop': {
            if (!triggerName || !tableName) {
              return { 
                content: [{ type: 'text', text: 'Error: triggerName and tableName are required for drop operation' }], 
                isError: true 
              };
            }
            const result = await executeDropTrigger({
              connectionString: connStringArg,
              triggerName,
              tableName,
              schema: schema || 'public',
              ifExists: ifExists || false,
              cascade: cascade || false
            }, getConnectionStringVal);
            return { content: [{ type: 'text', text: `Trigger ${result.name} dropped successfully from ${result.schema}.${result.table}` }, { type: 'text', text: JSON.stringify(result, null, 2) }] };
          }
  
          case 'set_state': {
            if (!triggerName || !tableName || enable === undefined) {
              return { 
                content: [{ type: 'text', text: 'Error: triggerName, tableName, and enable are required for set_state operation' }], 
                isError: true 
              };
            }
            const result = await executeSetTriggerState({
              connectionString: connStringArg,
              triggerName,
              tableName,
              enable,
              schema: schema || 'public'
            }, getConnectionStringVal);
            return { content: [{ type: 'text', text: `Trigger ${result.name} ${result.enabled ? 'enabled' : 'disabled'} on ${result.schema}.${result.table}` }, { type: 'text', text: JSON.stringify(result, null, 2) }] };
          }
  
          default:
            return { 
              content: [{ type: 'text', text: `Error: Unknown operation "${operation}". Supported operations: get, create, drop, set_state` }], 
              isError: true 
            };
        }
  
      } catch (error) {
        const errorMessage = error instanceof McpError ? error.message : (error instanceof Error ? error.message : String(error));
        return { content: [{ type: 'text', text: `Error executing ${operation} operation: ${errorMessage}` }], isError: true };
      }
    }
  };

• src/tools/triggers.ts:558-583 (schema)

  Zod schema for validating input parameters to the pg_manage_triggers tool, supporting all operations via conditional fields.

  inputSchema: z.object({
    connectionString: z.string().optional().describe('PostgreSQL connection string (optional)'),
    operation: z.enum(['get', 'create', 'drop', 'set_state']).describe('Operation: get (list triggers), create (new trigger), drop (remove trigger), set_state (enable/disable trigger)'),
    
    // Common parameters
    schema: z.string().optional().describe('Schema name (defaults to public)'),
    tableName: z.string().optional().describe('Table name (optional filter for get, required for create/drop/set_state)'),
    
    // Trigger identification (for create/drop/set_state)
    triggerName: z.string().optional().describe('Trigger name (required for create/drop/set_state)'),
    
    // Create trigger parameters
    functionName: z.string().optional().describe('Function name (required for create operation)'),
    timing: z.enum(['BEFORE', 'AFTER', 'INSTEAD OF']).optional().describe('Trigger timing (for create operation, defaults to AFTER)'),
    events: z.array(z.enum(['INSERT', 'UPDATE', 'DELETE', 'TRUNCATE'])).optional().describe('Trigger events (for create operation, defaults to ["INSERT"])'),
    forEach: z.enum(['ROW', 'STATEMENT']).optional().describe('FOR EACH ROW or STATEMENT (for create operation, defaults to ROW)'),
    when: z.string().optional().describe('WHEN clause condition (for create operation)'),
    replace: z.boolean().optional().describe('Whether to replace trigger if exists (for create operation)'),
    
    // Drop trigger parameters
    ifExists: z.boolean().optional().describe('Include IF EXISTS clause (for drop operation)'),
    cascade: z.boolean().optional().describe('Include CASCADE clause (for drop operation)'),
    
    // Set state parameters
    enable: z.boolean().optional().describe('Whether to enable the trigger (required for set_state operation)')
  }),

• src/tools/triggers.ts:278-332 (helper)

  Helper function executeGetTriggers used by main handler for listing triggers information.

  async function executeGetTriggers(  input: GetTriggersInput,  getConnectionString: GetConnectionStringFn): Promise<TriggerInfo[]> {  const resolvedConnectionString = getConnectionString(input.connectionString);  const db = DatabaseConnection.getInstance();  const { tableName, schema } = input;    try {    await db.connect(resolvedConnectionString);        let query = `
        SELECT 
          t.tgname AS name,
          c.relname AS "tableName",
          n.nspname AS "tableSchema",
          CASE
            WHEN (t.tgtype & (1<<0)) != 0 THEN 'ROW'
            ELSE 'STATEMENT'
          END AS level,
          CASE
            WHEN (t.tgtype & (1<<1)) != 0 THEN 'BEFORE'
            WHEN (t.tgtype & (1<<6)) != 0 THEN 'INSTEAD OF'
            ELSE 'AFTER'
          END AS timing,
          CASE
            WHEN (t.tgtype & (1<<2)) != 0 THEN 'INSERT'
            WHEN (t.tgtype & (1<<3)) != 0 THEN 'DELETE'
            WHEN (t.tgtype & (1<<4)) != 0 THEN 'UPDATE'
            WHEN (t.tgtype & (1<<5)) != 0 THEN 'TRUNCATE'
            ELSE 'UNKNOWN'
          END AS event,
          p.proname AS function,
          pg_get_triggerdef(t.oid) AS definition,
          -- PostgreSQL version-compatible enabled status
          -- Note: For older PostgreSQL versions without tgenabled, this will always return true
          true AS enabled
        FROM pg_trigger t
        JOIN pg_class c ON t.tgrelid = c.oid
        JOIN pg_namespace n ON c.relnamespace = n.oid
        JOIN pg_proc p ON t.tgfoid = p.oid
        WHERE NOT t.tgisinternal
      `;
      
      const params = [];
      
      if (schema) {
        query += ` AND n.nspname = $${params.length + 1}`;
        params.push(schema);
      }
      
      if (tableName) {
        query += ` AND c.relname = $${params.length + 1}`;
        params.push(tableName);
      }
      
      query += ' ORDER BY c.relname, t.tgname';
      
      const triggers = await db.query<TriggerInfo>(query, params);
      return triggers;
    } catch (error) {
      throw new McpError(ErrorCode.InternalError, `Failed to get trigger information: ${error instanceof Error ? error.message : String(error)}`);
    } finally {
      await db.disconnect();
    }
  }

• src/tools/triggers.ts:372-418 (helper)

  Helper function executeCreateTrigger used by main handler for creating new triggers.

  async function executeCreateTrigger(
    input: CreateTriggerInput,
    getConnectionString: GetConnectionStringFn
  ): Promise<{ name: string; table: string; schema: string; timing: string; events: string[]; function: string }> {
    const resolvedConnectionString = getConnectionString(input.connectionString);
    const db = DatabaseConnection.getInstance();
    const { triggerName, tableName, functionName, schema, timing, events, when, forEach, replace } = input;
    
    try {
      await db.connect(resolvedConnectionString);
      
      const createOrReplace = replace ? 'CREATE OR REPLACE' : 'CREATE';
      const qualifiedTableName = `"${schema}"."${tableName}"`;
      const qualifiedFunctionName = `"${functionName}"`; // Assuming functionName might also need quoting or schema qualification
  
      let sql = `
        ${createOrReplace} TRIGGER "${triggerName}"
        ${timing} ${events.join(' OR ')}
        ON ${qualifiedTableName}
      `;
      
      if (forEach) {
        sql += ` FOR EACH ${forEach}`;
      }
      
      if (when) {
        sql += ` WHEN (${when})`;
      }
      
      sql += ` EXECUTE FUNCTION ${qualifiedFunctionName}()`; // Ensure function has () if it's a procedure/function call
      
      await db.query(sql);
      
      return {
        name: triggerName,
        table: tableName,
        schema,
        timing,
        events,
        function: functionName
      };
    } catch (error) {
      throw new McpError(ErrorCode.InternalError, `Failed to create trigger: ${error instanceof Error ? error.message : String(error)}`);
    } finally {
      await db.disconnect();
    }
  }