citus: support relationships in a function's response

create function search_disasters(search text)
returns setof disaster as $$
begin
    return query select *
    from disaster
    where
      name ilike ('%' || search || '%');
end;
$$ language plpgsql stable;

However, trying to use a relationship will result in an error:

query function {
  search_disasters(args: {search: "sarga"}) {
    name
    affected_states {
      state_id
    }
  }
}

message: "cannot pushdown the subquery",
status_code: "0A000",
description: "Complex subqueries and CTEs cannot be in the outer part of the outer join"

We'll need to use the primary key columns of the table that the function returns and change the LHS to

select * from table_the_function_returns where pkey_columns in (select pkey_columns from function(args))

from

select * from function(args)

hasura / graphql-engine

citus: support relationships in a function's response #7186