[FEATURE] Add Option to Automatically Drop Functions When Return Type Changes

[supunnilakshana]

Description

When attempting to migrate a PL/pgSQL function where the return type needs to change (e.g., from json to jsonb), pg-schema-diff generates a CREATE OR REPLACE FUNCTION statement. However, PostgreSQL rejects this with ERROR: cannot change return type of existing function (SQLSTATE 42P13), as it doesn't allow changing the return type via REPLACE.

This requires manual intervention (e.g., adding a DROP FUNCTION before the CREATE) or custom scripting outside the tool. To make the tool more robust for common schema evolution scenarios, it would be great to add a flag like --allow-drop-functions (or --allow-destructive-changes) that automatically inserts DROP FUNCTION IF EXISTS statements when a return type mismatch is detected in the diff.

Use Case

• Existing function: CREATE FUNCTION get_numbers() RETURNS json ...
• Target: CREATE FUNCTION get_numbers() RETURNS jsonb ...
• Current behavior: Generates only CREATE OR REPLACE, which fails.
• Desired: With flag, generate DROP FUNCTION IF EXISTS public.get_numbers(); followed by the new CREATE.

This aligns with the tool's safety-first approach (opt-in for destructive changes) and handles the "HAS_UNTRACKABLE_DEPENDENCIES" hazard by ensuring dependencies are considered (perhaps with warnings).

Proposed Implementation

• Detect signature changes (focus on return type) during diff computation.
• If the flag is enabled, prepend DROP to the migration plan for affected functions.
• Add warnings about potential dependency breaks (e.g., views/triggers relying on the function).

Alternatives Considered

• Users can manually edit the plan, but automating this reduces friction.

Additional Context

• PostgreSQL version: 13+ (issue persists across versions).
• Tool version: Latest (as of Nov 2025).
• Example error from apply:

  Error: generating plan: validating migration plan: running migration plan: executing migration statement: {CREATE OR REPLACE FUNCTION public.get_numbers() RETURNS jsonb ...}: ERROR: cannot change return type of existing function (SQLSTATE 42P13)
  

Thanks for the great tool—happy to provide more details or a PR if helpful!