Re: Idea: Avoid JOINs by using path expressions to follow FKs

On Sat, 27 Mar 2021 at 16:28, Joel Jacobson <joel@compiler.org> wrote:

Imagine if we could simply write the SQL query like this:

SELECT DISTINCT od.order_id.customer_id.company_name

FROM order_details AS od

WHERE od.product_id.product_name = 'Chocolade';

I regularly do this type of thing via views. It's a bit confusing as writes go to one set of tables while selects often go through the view with all the details readily available.

I think I'd want these shortcuts to be well defined and obvious to someone exploring via psql. I can also see uses where a foreign key might not be available (left join rather than join).

I wonder if GENERATED ... VIRTUAL might be a way of defining this type of added record.

ALTER TABLE order ADD customer record GENERATED JOIN customer USING (customer_id) VIRTUAL;

ALTER TABLE order_detail ADD order record GENERATED JOIN order USING (order_id) VIRTUAL;

SELECT order.customer.company_name FROM order_detail;

Of course, if they don't reference the GENERATED column then the join isn't added to the query.