Postgraphile: relation from function to function?

[the-sun-will-rise-tomorrow]

Summary

Hello! I'm developing an app where all data is versioned. So, instead of an id primary key, IDs are not unique and tables instead have an (id, version) primary key.

A feature of the app is a "time machine" mode: you enter a date, and see data as it was at that date.

I can implement querying a single table as a stored function, which accepts a maximum-version parameter. This works well - Postgraphile sees the function and exposes it in GraphQL.

But, I can't figure out how to set up relations. I would like to somehow tell Postgraphile that some of the columns are relations to other types of data, and to get that data, it should call another function (with the same maximum-version parameter). For example:

create table foo(id integer not null, version integer not null, bar_id integer not null);
create table bar(id integer not null, version integer not null, data text);

create function foo_at(max_version integer) returns setof foo as $$ select distinct on (foo.id) * from foo where version <= max_version order by id, version $$ language sql stable;
create function bar_at(max_version integer) returns setof bar as $$ select distinct on (bar.id) * from bar where version <= max_version order by id, version $$ language sql stable;

This shows up as fooAt and barAt nodes in GraphQL... is there a way to tell it so that if we request fooAt(maxVersion: 44), then it should join foo_at(44) with bar_at(44) on foo_at.bar_id = bar_at.id?

Thanks!

Additional context

I am just starting to use Postgraphile.

[benjie]

I think you're looking for what we call a "computed column" function:

create function foo_bar_at_same_max(f foo) returns setof bar as $$
select distinct on (bar.id) * from bar where version <= f.version order by id, version
$$ language sql stable;

[the-sun-will-rise-tomorrow]

Thank you, but I don't think that's going to work - if the user wants to see the data as it was in 2020 and we have an object that was last edited in 2010 but references an object last edited in 2015 then they will not see the 2015 version.

[the-sun-will-rise-tomorrow]

I got it to work! Like this:

create table foo(id integer not null, version integer not null, bar_id integer not null);
create table bar(id integer not null, version integer not null, data text);

create type foo_snapshot as (row foo, max_version integer);
create type bar_snapshot as (row bar, max_version integer);
create function foo_snapshot_at(max_version integer) returns setof foo_snapshot as $$ select distinct on (foo.id) foo, max_version from foo where version <= max_version order by id, version $$ language sql stable;
create function bar_snapshot_at(max_version integer) returns setof bar_snapshot as $$ select distinct on (bar.id) bar, max_version from bar where version <= max_version order by id, version $$ language sql stable;

create function foo_snapshot_bar(x foo_snapshot) returns setof bar_snapshot as $$ select * from bar_snapshot_at(x.max_version) AS y where (y.row).id = (x.row).bar_id $$ language sql stable;

[benjie]

That’s a good solution 👍 Had you have made the max_version implicit rather than explicit you would have fallen foul of the problem described here: https://benjie.dev/graphql/ancestors

But you didn’t, so everything is well! 🙌