Resource embedding on an RPC function

[BorislavZlatanov]

I have an RPC function endpoint that looks like this (simplified):

CREATE FUNCTION poll()
RETURNS TABLE (post_title TEXT)
AS $$

SELECT post_title
FROM polls p
ORDER BY p.created DESC;
      
$$
LANGUAGE SQL
STABLE;

I want to do a JOIN with another table in order to filter the results based on a column from that other table.

The docs on Resource Embedding state that "For table-valued functions, it generates a join condition based on the foreign key columns of the returned table type". But I don't exactly understand what this means (what foreign key columns are in this context).

I tried the obvious thing with https://localhost/rpc/poll?select=post_title,poll_choices(id)

i.e. I tried to join a poll_choices table which has a foreign key but this foreign key is to another table, not to the table returned by the function. So I got the error Could not find a relationship between 'record' and 'poll_choices' in the schema cache

How can I accomplish my objective?

[steve-chavez]

@BorislavZlatanov Change the return type from RETURNS TABLE (post_title TEXT) to RETURNS SETOF polls and then the request should work. For this you'll also need to change to SELECT * inside the function.

[steve-chavez]

Yeah that's a current limitation, we cannot detect the join columns needed if we don't have the table in the return type. The only option you have is to do the join inside the function.

One way we could improve this and make the embedding work is by making the user create a child table using inheritance:

CREATE TABLE polls_extended (
    another json,
   -- other new columns
) INHERITS (polls);

And then use that as the return type RETURNS SETOF polls_extended instead of RETURNS TABLE (..).

But it has the drawback of creating an empty table + having to create a new type for each case.

Would that work for you?

[BorislavZlatanov]

I don't mind the drawback of creating an empty table. And it seems that there wouldn't need to be many types that have to be created. So I tried making a child table but encountered a couple of issues:

• As per the docs, FOREIGN KEY constraints don't propagate to child tables. Since I'm creating an empty table in this case, I'm not sure how to work around this.
• When I do http://localhost:3001/rpc/poll?post_title=eq.test&select=post_title, it works fine, but having any other columns in the SELECT (for example http://localhost:3001/rpc/poll?post_title=eq.test&select=id) gives me: column polls_extended.post_title does not exist

Since I already have the FOREIGN KEY in my tables, if there were some simple way to propagate/specify that in my function definition, it might be a good solution. Because the RETURNS TABLE style seems more flexible and better suited but just has the single disadvantage of not being able to specify (or automatically pick up) the FOREIGN KEY of the underlying tables the function is querying.

[steve-chavez]

Other types of constraints (unique, primary key, and foreign key constraints) are not inherited.

Right, looks like this idea isn't going to work then 😞

if there were some simple way to propagate/specify that in my function definition, it might be a good solution.

Since there's no native way to do this in PostgreSQL, we could come up with a convention. Another idea:

CREATE FUNCTION poll()
RETURNS TABLE (
  "..." polls, -- `...` would mean that on PostgREST side we won't assign a key to the result and instead we'll spread "..."
  other_stuff text
)
LANGUAGE sql
AS $$
  SELECT ROW(1, 'poll 1')::polls, 'stuff'; -- the caveat here is that you'd need to convert your table to a row
$$;

Then on PostgREST side we'd query like this:

select ("...").*, other_stuff from test.f();
id   |   name   | other_stuff 
----+-------+-------------
 1 | poll-1 | stuff

And finally convert to json.

[BorislavZlatanov]

If postgREST can somehow detect the underlying tables that a function queries, there would be no need to introduce any additional syntax. There would be consistent behaviour between tables, views and functions - in all three cases you'd just declare your foreign keys on the table.

If I understood the current behaviour ("For table-valued functions, it generates a join condition based on the foreign key columns of the returned table type"), the join conditions happen based on foreign keys in the returned table. If instead they can happen based on the tables being queried inside the function, that might do it. Or at least, if the function uses RETURNS SETOF, then we already have this behaviour (but it leaves no flexibility for returning anything different than the table type), but if the function uses RETURNS TABLE, it would be nice if the foreign keys of tables queried in the function are detected automatically.

[wolfgangwalther]

If postgREST can somehow detect the underlying tables that a function queries

This won't be enough. To actually make the join happen, the column to join on also needs to be returned from the function. Otherwise the result can't be matched up to the other table. Your original example only returned post_title from the function, but no matter how you look at it, this won't work.

The correct solution is to return polls, which allows us to both defer the right table to join and to actually proceed on the join, because the respective column is available, too.

TLDR: Steve already gave the correct answer initially.