TLDR: CREATE OR REPLACE FUNCTION can change a Postgres function's body — but NOT its return shape. Add or remove a column from RETURNS TABLE(...) and Postgres throws 42P13. The fix is one line: DROP FUNCTION IF EXISTS your_fn(arg_types); before the CREATE.

The Setup

I've been building a supply-chain dashboard for an ecommerce business — live Shopify inventory, purchase orders, velocity reports, the works.

All the heavy aggregation lives in Postgres SQL functions exposed as Supabase RPCs.

That keeps the app layer thin and makes the numbers fast. Usually.

What Broke

Migration 015 created report_velocity returning three rate columns: rate_7d, rate_30d, rate_90d.

Migration 016 extended it — added a 180-day window, rate_180d. That migration was written correctly: it DROPped the function first, then recreated it with the expanded return table.

Then I wrote migration 023.

I was patching something unrelated in report_velocity and copy-pasted the function definition from… migration 015. The earliest version. Not the latest.

CREATE OR REPLACE FUNCTION report_velocity ... — with no rate_180d. Ran it against the live DB.

ERROR:  42P13: cannot change return type of existing function
DETAIL: Row type defined by OUT parameters is different.
HINT:   Use DROP FUNCTION report_velocity(timestamp with time zone) first.

Dead stop.

Why It Happens

Postgres is happy to let you swap out a function's body with CREATE OR REPLACE.

But the return signature is a contract — other objects may depend on it. So if you add, remove, rename, or retype any column in RETURNS TABLE(...), Postgres refuses and throws 42P13.

The subtle part: this error doesn't happen on body changes. It only fires when the OUT-column shape diverges from what's already registered. So if you're patching logic but keeping the same columns, CREATE OR REPLACE works fine. The moment the shape drifts — boom.

The Fix

Two things, both required:

  1. Prepend the DROP — DROP FUNCTION IF EXISTS report_velocity(timestamptz);
  2. Use the most recent signature as your baseline — I had to bring rate_180d back in, because 016 had added it and I'd written 023 against the stale 015 shape.

One line. Done.

A few things I learned the hard way about the DROP:

  • Always include the argument types. Postgres overloads functions by signature — DROP FUNCTION report_velocity() is NOT the same as DROP FUNCTION report_velocity(timestamptz). You'll drop nothing, or the wrong overload.
  • Use Postgres's internal type names. The error hint will say timestamp with time zone but in the DROP you want timestamptz — that's how Postgres stores it internally and what it matches against. Copy the hint Postgres prints; don't guess.

The Heuristic That Saves You

When you're writing a migration that touches an existing function, grep the migrations folder for every prior version of that function first.

Then baseline off the most recent one. Not the earliest. Not the one you remember writing.

If you're ever unsure whether the signature changed — just prepend the DROP anyway. The cost is one line. The benefit is not blowing up a production migration.

(There's a sibling foot-gun: renaming columns that live inside a function body. Different error, same genre. I've got notes on that too.)

Same rule applies to views, procedures, and types — CREATE OR REPLACE VIEW only allows additions at the end; anything else needs a DROP VIEW. Types have it worst: CREATE TYPE has no REPLACE at all. DROP TYPE CASCADE is the only path.

Why It Matters to Me

I lost maybe 30 minutes on this — not terrible.

But the reason I lost them is exactly the kind of thing that compounds at scale: I grabbed the old signature because it was the easiest thing to find, not because it was correct.

The most recent migration is the source of truth. Always.

That's the rep I'm building into muscle memory.