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(); } }