invalid_function_definition cannot change return type of existing function — 42P13

Symptoms

A CREATE OR REPLACE FUNCTION tried to change the return type of a function that already exists. Replacing a function may change its body but not its signature/return type, so PostgreSQL raises SQLSTATE 42P13 (invalid_function_definition).

• Happens on redeploys where a function’s return type was edited.
• A HINT often suggests DROP FUNCTION first.
• Argument-type changes create a new overload instead of replacing.

What the server log shows

ERROR:  cannot change return type of existing function
HINT:  Use DROP FUNCTION get_total(integer) first.

Why PostgreSQL raises this — what the manual says

the CREATE FUNCTION reference (Notes):

“Also, CREATE OR REPLACE FUNCTION will not let you change the return type of an existing function.”

Other objects (views, defaults, dependent functions) may rely on the function’s declared return type. Allowing REPLACE to silently change it could break them, so PostgreSQL forbids the change and requires an explicit DROP + CREATE, raising 42P13.

Common causes

• Editing a function’s return type and redeploying with CREATE OR REPLACE.
• Switching between SETOF/scalar/table return forms.
• Changing OUT-parameter shape that alters the effective return type.

How to fix it

1. Drop and recreate: DROP FUNCTION get_total(integer); CREATE FUNCTION get_total(integer) RETURNS numeric …;.
2. If dependents exist, drop them too (or use DROP … CASCADE deliberately) and recreate.
3. Keep return types stable across deploys; add a new function name when the contract changes.

Related & next steps

• SQLSTATE 42P13 — Invalid Function Definition (full reference)
• Message: function … already exists with same argument types
• Glossary: Function

Reference: PostgreSQL 18 — CREATE FUNCTION.