PostgREST / PostgREST/postgrest

Computed relationships are not consistent when used through RPC

Open
#4,232 4 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

idea
Dominant language
Haskell
Stars
27.7k
Forks
1.2k
Avg merge
1d 9h
Merged PRs (30d)
54

Description

Problem

Having a computed relationship like this:

CREATE FUNCTION test.computed_designers(x test.videogames) RETURNS SETOF test.designers AS $$
  SELECT * FROM test.designers WHERE id = $1.designer_id;
$$ LANGUAGE sql STABLE ROWS 1;

(Basically adding a named parameter x to the function we have in fixtures)

It will be callable and give a result like:

curl localhost:3000/rpc/computed_designers -H "Content-Type:application/json" -d @- <<EOF
{"x": {"designer_id": 2}}
EOF

[{"id":2,"name":"Hironobu Sakaguchi"}]

But [{"id":2,"name":"Hironobu Sakaguchi"}] is not a single object, thus inconsistent when using the function through computed rels:

curl 'localhost:3000/videogames?select=*,computed_designers(*)&id=eq.4'

[{"id":4,"name":"Final Fantasy II","designer_id":2,"computed_designers":{"id": 2, "name": "Hironobu Sakaguchi"}}]

Which gives {"id": 2, "name": "Hironobu Sakaguchi"} for the computed rel.

The option of changing the function to RETURNS test.designers comes to mind, that will work to give a single object through RPC, but now it would not work as computed relationship.

Proposal

Convert results of RPC that RETURNS SETOF test.designers ROWS 1 to a single json object.

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start with the RPC endpoint behavior and the computed relationship example, then inspect the referenced test/spec/fixtures/schema.sql function near the linked fixture lines. Compare the RPC and computed-relationship responses for RETURNS SETOF ... ROWS 1. Done means the RPC response is a single JSON object while the function remains usable as a computed relationship.

Written by the indexing model from the issue text.

Assessment

Tech stack
haskell, postgresql
Domain
api, databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
42/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.