Support self-referencing foreign key

[ilanbenb]

Problem

Self-referencing foreign keys cannot be embedded using PostgREST's standard resource embedding. While regular FKs between different tables are auto-detected and embeddable via the !hint disambiguation syntax, self-referencing FKs require manually defining computed relationship functions.

For example, given a table with a self-referencing FK:

  create table patients (
    patient_id int primary key generated always as identity,
    first_name text not null,
    last_name text not null,
    referring_patient_id int references patients(patient_id)
  );

There is no way to query the referring patient via standard embedding. You must define a boilerplate function:

  create or replace function referred_by(patients)
  returns setof patients rows 1 as $$
    select * from patients where patient_id = $1.referring_patient_id
  $$ stable language sql;

This is the only documented approach for recursive relationships. It creates downstream issues — for example, Supabase's type generator now embeds these functions into the table's Row type, forcing workarounds in application code.

Solution

Support self-referencing FK embedding natively using the existing !hint disambiguation syntax:

GET /patients?select=*,referred_by:patients!referring_patient_id(patient_id,first_name,last_name)

PostgREST already detects the FK (referring_patient_id -> patient_id). The !fk_name hint provides enough information to resolve the self-join unambiguously, the same way it resolves ambiguity when multiple FKs point to different tables.

[ilanbenb]

Related: supabase/cli#4982 — the computed function workaround causes issues with Supabase's type generator.

[wolfgangwalther]

Self-referencing foreign keys are already supported.

See all these test-cases:

postgrest/test/spec/Feature/Query/EmbedDisambiguationSpec.hs

Lines 367 to 496 in bf7dc48

	context "tables with self reference foreign keys" $ do
	context "one self reference foreign key" $ do
	it "embeds parents recursively" $
	get "/family_tree?id=in.(3,4)&select=id,parent(id,name,parent(*))" `shouldRespondWith`
	[json|[
	{ "id": "3", "parent": { "id": "1", "name": "Parental Unit", "parent": null } },
	{ "id": "4", "parent": { "id": "2", "name": "Kid One", "parent": { "id": "1", "name": "Parental Unit", "parent": null } } }
	]|]
	{ matchHeaders = [matchContentTypeJson] }
	it "embeds children recursively" $
	get "/family_tree?id=eq.1&select=id,name, children:family_tree!parent(id,name,children:family_tree!parent(id,name))" `shouldRespondWith`
	[json|[{
	"id": "1", "name": "Parental Unit", "children": [
	{ "id": "2", "name": "Kid One", "children": [ { "id": "4", "name": "Grandkid One" } ] },
	{ "id": "3", "name": "Kid Two", "children": [ { "id": "5", "name": "Grandkid Two" } ] }
	]
	}]|] { matchHeaders = [matchContentTypeJson] }
	it "embeds parent and then embeds children" $
	get "/family_tree?id=eq.2&select=id,name,parent(id,name,children:family_tree!parent(id,name))" `shouldRespondWith`
	[json|[{
	"id": "2", "name": "Kid One", "parent": {
	"id": "1", "name": "Parental Unit", "children": [ { "id": "2", "name": "Kid One" }, { "id": "3", "name": "Kid Two"} ]
	}
	}]|] { matchHeaders = [matchContentTypeJson] }
	it "embeds parent and then embeds children on a view" $
	get "/job?select=id,parent_id(*),children:job!parent_id(id,parent_id)" `shouldRespondWith`
	[json|[
	{
	"id": 1,
	"parent_id": null,
	"children": [ { "id": 2, "parent_id": 1 } ]
	},
	{
	"id": 2,
	"parent_id": { "id": 1, "parent_id": null },
	"children": []
	}
	]|] { matchHeaders = [matchContentTypeJson] }
	it "can specify all view column names that reference the same base column" $ do
	get "/i2459_self_v1?select=*,parent(*),grandparent(*)" `shouldRespondWith`
	[json| [] |]
	{ matchHeaders = [matchContentTypeJson] }
	get "/i2459_self_v2?select=*,parent(*),grandparent(*)" `shouldRespondWith`
	[json| [] |]
	{ matchHeaders = [matchContentTypeJson] }
	context "two self reference foreign keys" $ do
	it "embeds parents" $
	get "/organizations?select=id,name,referee(id,name),auditor(id,name)&id=eq.3" `shouldRespondWith`
	[json|[{
	"id": 3, "name": "Acme",
	"referee": {
	"id": 1,
	"name": "Referee Org"
	},
	"auditor": {
	"id": 2,
	"name": "Auditor Org"
	}
	}]|] { matchHeaders = [matchContentTypeJson] }
	it "embeds children" $ do
	get "/organizations?select=id,name,refereeds:organizations!referee(id,name)&id=eq.1" `shouldRespondWith`
	[json|[{
	"id": 1, "name": "Referee Org",
	"refereeds": [
	{
	"id": 3,
	"name": "Acme"
	},
	{
	"id": 4,
	"name": "Umbrella"
	}
	]
	}]|] { matchHeaders = [matchContentTypeJson] }
	get "/organizations?select=id,name,auditees:organizations!auditor(id,name)&id=eq.2" `shouldRespondWith`
	[json|[{
	"id": 2, "name": "Auditor Org",
	"auditees": [
	{
	"id": 3,
	"name": "Acme"
	},
	{
	"id": 4,
	"name": "Umbrella"
	}
	]
	}]|] { matchHeaders = [matchContentTypeJson] }
	it "embeds other relations(manager) besides the self reference" $ do
	get "/organizations?select=name,manager(name),referee(name,manager(name),auditor(name,manager(name))),auditor(name,manager(name),referee(name,manager(name)))&id=eq.5" `shouldRespondWith`
	[json|[{
	"name":"Cyberdyne",
	"manager":{"name":"Cyberdyne Manager"},
	"referee":{
	"name":"Acme",
	"manager":{"name":"Acme Manager"},
	"auditor":{
	"name":"Auditor Org",
	"manager":{"name":"Auditor Manager"}}},
	"auditor":{
	"name":"Umbrella",
	"manager":{"name":"Umbrella Manager"},
	"referee":{
	"name":"Referee Org",
	"manager":{"name":"Referee Manager"}}}
	}]|] { matchHeaders = [matchContentTypeJson] }
	get "/organizations?select=name,manager(name),auditees:organizations!auditor(name,manager(name),refereeds:organizations!referee(name,manager(name)))&id=eq.2" `shouldRespondWith`
	[json|[{
	"name":"Auditor Org",
	"manager":{"name":"Auditor Manager"},
	"auditees":[
	{"name":"Acme",
	"manager":{"name":"Acme Manager"},
	"refereeds":[
	{"name":"Cyberdyne",
	"manager":{"name":"Cyberdyne Manager"}},
	{"name":"Oscorp",
	"manager":{"name":"Oscorp Manager"}}]},
	{"name":"Umbrella",
	"manager":{"name":"Umbrella Manager"},
	"refereeds":[]}]
	}]|] { matchHeaders = [matchContentTypeJson] }

I'm unsure why the docs say computed relationships are needed for that. This piece in the docs was introduced in 9ed7ff8, but I can't find a PR with discussion for this commit.

Support self-referencing FK embedding natively using the existing !hint disambiguation syntax:

GET /patients?select=*,referred_by:patients!referring_patient_id(patient_id,first_name,last_name)

According to our test-cases, this should already work.

[steve-chavez]

This piece in the docs was introduced in 9ed7ff8, but I can't find a PR with discussion for this commit.

Yeah, the PR history was lost since this was done on the old docs repo PostgREST/postgrest-docs#666

The self-join syntax has limitations (see #1643 (comment)), at the time of rewriting the resource embedding docs I found those really hard to explain compared to just linking computed relationships which do solve everything. Also, I wasn't sure if the self-join syntax was stable enough, I can't find the reference now but I remember discussing an alternative syntax.

We can revisit this now of course.