Re: Idea: Avoid JOINs by using path expressions to follow FKs - Mailing list pgsql-hackers

From Isaac Morland
Subject Re: Idea: Avoid JOINs by using path expressions to follow FKs
Date
Msg-id CAMsGm5fmZSaMnHEOLxGS+Ekg6jNsOSgfRu3qd8fxUo=Pfky=ww@mail.gmail.com
Whole thread Raw
In response to Re: Idea: Avoid JOINs by using path expressions to follow FKs  ("Joel Jacobson" <joel@compiler.org>)
Responses Re: Idea: Avoid JOINs by using path expressions to follow FKs  ("Joel Jacobson" <joel@compiler.org>)
List pgsql-hackers
On Tue, 30 Mar 2021 at 14:30, Joel Jacobson <joel@compiler.org> wrote:

If the expression ends with a column_name,
you get the value for the column.

If the expression ends with a constraint_name,
you get the referenced table as a record.

Can’t you just leave off the “ends with a column_name” part? If you want one of its columns, just put .column_name:

table -> constraint -> ... -> constraint . column_name

Then you know that -> expects a constraint_name and only that to its right.

Also, should the join be a left join, which would therefore return a NULL when there is no matching record? Or could we have a variation such as ->? to give a left join (NULL when no matching record) with -> using an inner join (record is not included in result when no matching record).

For the record I would find something like this quite useful. I constantly find myself joining in code lookup tables and the like, and while from a mathematical view it’s just another join, explicitly listing the table in the FROM clause of a large query does not assist with readability to say the least.

pgsql-hackers by date:

Previous
From: igor levshin
Date:
Subject: Re: Failed assertion on standby while shutdown
Next
From: David Zhang
Date:
Subject: Re: Support tab completion for upper character inputs in psql