Regression when arrow filtering a composite column in a function that returns TABLE or SETOF composite_type

[laurenceisla]

Environment

• PostgreSQL version: 14.8
• PostgREST version: 11.2.0
• Operating system: NixOS

Description of issue

If the function RETURNS TABLE that has a composite type or a SETOF <composite_type> directly, then filtering on that type will return an error. Version 11.1.0 handles the filter correctly while 11.2.0 does not. (Based on this issue: supabase/postgrest-js#475)

Steps to reproduce:

create type type_x as (
  id int,
  val text
);

create or replace function test.returns_type()
returns table(id int, val type_x) as $$
  select 1, row(1, 'value')::type_x ;
$$ language sql;

Then this request will err in v11.2.0:

$ curl "http://localhost:3000/rpc/returns_type?val->>id=eq.1"

{
  "code":"42883",
  "details":null,
  "hint":"No operator matches the given name and argument types. You might need to add explicit type casts.",
  "message":"operator does not exist: type_x ->> unknown"
}

While in v11.1.0, it works:

$ curl "http://localhost:3000/rpc/returns_type?val->>id=eq.1"

[{"id":1,"val":{"id":1,"val":"value"}}]

[steve-chavez]

Looks it's caused by #2859

-- old version query

WITH pgrst_source AS (SELECT "pgrst_call".* FROM "test"."returns_type"() pgrst_call)
SELECT
    null::bigint AS total_result_set,
    pg_catalog.count(_postgrest_t) AS page_total,
    coalesce(json_agg(_postgrest_t), '[]') AS body,
    nullif(current_setting('response.headers', true), '') AS response_headers,
    nullif(current_setting('response.status', true), '') AS response_status
FROM (SELECT "record".* FROM "pgrst_source" AS "record"  WHERE  to_jsonb("record"."val")->>$1 = $2  ) _postgrest_t;

-- new version query

 ERROR:  operator does not exist: type_x ->> unknown at character 446
 HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
 STATEMENT:

WITH pgrst_source AS (SELECT "pgrst_call".* FROM "test"."returns_type"() pgrst_call)
SELECT
    null::bigint AS total_result_set,
    pg_catalog.count(_postgrest_t) AS page_total,
    coalesce(json_agg(_postgrest_t), '[]') AS body,
    nullif(current_setting('response.headers', true), '') AS response_headers,
    nullif(current_setting('response.status', true), '') AS response_status
FROM (SELECT "record".* FROM "pgrst_source" AS "record"  WHERE  "record"."val"->>$1 = $2  ) _postgrest_t;

The to_jsonb is missing.

[steve-chavez]

This does work on a table though:

create table table_c (
  id int
, val type_x
);

$ curl "http://localhost:3000/table_c?val->>id=eq.1"
[]

Looks like the main issue is that for RPC, the type of the filter is not searched here:

postgrest/src/PostgREST/Plan.hs

Lines 260 to 263 in 290d906

	resolveTypeOrUnknown :: ResolverContext -> Field -> CoercibleField
	resolveTypeOrUnknown ResolverContext{..} field@(fn, jp) =
	fromMaybe (unknownField fn jp) $ HM.lookup qi tables >>=
	Just . flip resolveTableField field

It was mentioned that RPC was not supported here #2523 (comment)