supabase-js rpc call error: message: 'a column definition list is required for functions returning "record"'

[bbagherian]

Bug report

• [x] I confirm this is a bug with Supabase, not with my own application.
• [x] I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

Following this documentation leads to an error on rpc call: https://supabase.com/docs/guides/database/extensions/postgis

{
    "code": "42601",
    "details": null,
    "hint": null,
    "message": "a column definition list is required for functions returning \"record\""
}

To Reproduce

SQL on the website dashboard:

create table if not exists public.restaurants (
    id int generated by default as identity primary key,
    name text not null,
    location geography(POINT) not null
);

create index restaurants_geo_index
  on public.restaurants
  using GIST (location);

insert into public.restaurants
  (name, location)
values
  ('Supa Burger', st_point(-73.946823, 40.807416)),
  ('Supa Pizza', st_point(-73.94581, 40.807475)),
  ('Supa Taco', st_point(-73.945826, 40.80629));

create or replace function nearby_restaurants(lat float, long float)
returns setof record
language sql
as $$
  select id, name, st_astext(location) as location, st_distance(location, st_point(long, lat)::geography) as dist_meters
  from public.restaurants
  order by location <-> st_point(long, lat)::geography;
$$;

and now in the app:

const { data, error } = await supabase.rpc('nearby_restaurants', {
  lat: 40.807313,
  long: -73.946713,
})

console.log('rpc restaurant', data, error);

Expected behavior

List of results

Screenshots

System information

• OS: [windows]
• Browser (if applies) [chrome]
• Version of supabase-js: [2.26.0]
• Version of Node.js: [18.16.1]

[bbagherian]

Running as query is ok by the way, the api call is broken I believe:

[steve-chavez]

Running as query is ok by the way

Try it in SQL as:

select * from nearby_restaurants(40, -73);

And you'll see it fail in the same way as the RPC.

ERROR: a column definition list is required for functions returning "record"

---

When you declare a SETOF function, it is a table-valued function and thus a SELECT * FROM func() is the most suitable query for it.

Additionally:

• look at the select nearby_restaurants() result. It has the tuple format (3, \"..) which is not suitable for a web application.
• If you don't declare types for your table-valued function, then typescript types can't also be generated (@soedirgo CMIIW).

---

Although a better error message could be generated.

[steve-chavez]

The root cause of this issue seems to be this guide https://supabase.com/docs/guides/database/extensions/postgis#order-by-distance

This discussion reports it used to work before? Will check that out.

[steve-chavez]

Tracking this on https://github.com/PostgREST/postgrest/issues/2881

[steve-chavez]

I'll fix this on https://github.com/PostgREST/postgrest/issues/2881 and go back to the previous behavior. So this worked before because PostgREST considered a setof record as scalar to avoid OP's error.

However this method has the disadvantage of not being able to select particular columns from it:

create or replace function setof_record() returns setof record as $$
  select * from projects;
$$ language sql;

curl 'localhost:3000/rpc/setof_record?select=id'
{"code":"42703","details":null,"hint":null,"message":"column setof_record.id does not exist"}

Considering that, it would be better to fix the PostGIS guide to use this function signature:

create or replace function nearby_restaurants(lat float, long float)
returns TABLE (id public.restaurants.id%TYPE, name public.restaurants.name%TYPE, location text, dist_meters float)
language sql
as $$
  select id, name, st_astext(location) as location, st_distance(location, st_point(long, lat)::geography) as dist_meters
  from public.restaurants
  order by location <-> st_point(long, lat)::geography;
$$;

The above should work on the current version.

[srht]

I'll fix this on PostgREST/postgrest#2881 and go back to the previous behavior. So this worked before because PostgREST considered a setof record as scalar to avoid OP's error.

However this method has the disadvantage of not being able to select particular columns from it:

create or replace function setof_record() returns setof record as $$
  select * from projects;
$$ language sql;

curl 'localhost:3000/rpc/setof_record?select=id'
{"code":"42703","details":null,"hint":null,"message":"column setof_record.id does not exist"}

Considering that, it would be better to fix the PostGIS guide to use this function signature:

create or replace function nearby_restaurants(lat float, long float)
returns TABLE (id public.restaurants%TYPE, name public.restaurants%TYPE, location text, dist_meters float)
language sql
as $$
  select id, name, st_astext(location) as location, st_distance(location, st_point(long, lat)::geography) as dist_meters
  from public.restaurants
  order by location <-> st_point(long, lat)::geography;
$$;

The above should work on the current version.

Thanks for the solution. Btw I changed a little this because it was returning invalid sql error then I removed public schema name and wrote types hardcoded instead of variables (%TYPE) and here it's:

create or replace function nearby_restaurants(lat float, long float)
returns TABLE (id int, name varchar, location text, dist_meters float)
language sql
as $$
  select id, name, st_astext(location) as location, st_distance(location, st_point(long, lat)::geography) as dist_meters
  from restaurants
  order by location <-> st_point(long, lat)::geography;
$$;

[steve-chavez]

FYI, this is already fixed on PostgREST v11.2.0 (not deployed to Supabase yet).

[steve-chavez]

Deploy with the fix is already approved https://github.com/supabase/postgres/pull/720.

Additionally the docs have been updated https://github.com/supabase/supabase/pull/16177

[KA-devl]

Hello, i have this problem that suddenly appear on my app. When will the fix be deployed in supabase?