App Finishers

Fix guide · updated 2026-11-16

Supabase "structure of query does not match function result type" (42804)

{"code":"42804","details":"Returned type bigint does not match expected type integer in column 2.","hint":null,"message":"structure of query does not match function result type"}

Calling a database function with supabase.rpc() fails with "structure of query does not match function result type", code 42804, HTTP 400. The function promises one set of columns in its returns table (...), and its query returns something slightly different. The details line says exactly what. We reproduced the usual mismatches on Postgres 16 behind Supabase's Data API (PostgREST 13, November 2026).

What the error means

In a PL/pgSQL function, return query must return exactly the columns of returns table (...): the same number, in the same order, with the same types. Close isn't enough. In our test, each of these failed:

The function is created without complaint. The error comes when it runs.

The one-minute check

Read the details of the error, in supabase-js error.details. It names the column by position, "column 2" being the second one in returns table (...), and gives both types. Then open the function in the SQL Editor:

select pg_get_functiondef('public.order_counts'::regproc);

Fix 1: cast in the query

Make the query return the declared type. In our test this worked:

return query select o.product_id, count(*)::int from public.orders o group by o.product_id;

The same goes for the others: p.name::text, p.price::float8. Prefer a cast that can't lose anything: numeric to double precision can round cents, so for money, change the declared type instead.

Fix 2: declare the real type

Change returns table (...) to what the query really returns: orders bigint, name varchar, price numeric. In our test, declaring name varchar fixed the varchar case. Changing the return type needs drop function first, because create or replace can't change it:

drop function public.order_counts();
create function public.order_counts()
returns table (product_id bigint, orders bigint)
language plpgsql stable as $$
begin
  return query select o.product_id, count(*) from public.orders o group by o.product_id;
end $$;

A bigint arrives in JavaScript as a normal number, so the app code doesn't change.

Fix 3: a plain SQL function

A function written in language sql converts compatible types by itself. In our test, the same count(*) query declared as integer worked as a SQL function:

create function public.order_counts()
returns table (product_id bigint, orders integer)
language sql stable as $$
  select o.product_id, count(*) from public.orders o group by o.product_id;
$$;

It doesn't fix a wrong number of columns: there, make the query and returns table (...) list the same columns.

If an AI tool built your app

Give your AI tool this prompt, with the details line from your error:

The Supabase function public.order_counts fails with structure of query does not match function result type (42804): "Returned type bigint does not match expected type integer in column 2". Make its returns table (...) list exactly the columns its query returns, with the same types, in the same order: declare the real types (bigint for count(*), varchar or text as the column is, numeric for money), dropping and recreating the function if the return type changes. Show me the SQL before running it. Don't change anything else.

Not quite your error?

Still stuck?

Run the function's query on its own in the SQL Editor and look at the result's column types, then compare them one by one with returns table (...). The first one that differs is the one in the error.

Other Supabase problems? See Supabase fixes.