On Tue, Mar 30, 2021, at 16:25, Rod Taylor 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.
Interesting idea, but not sure I like it, since you would need twice as many columns,
and you would still need the foreign keys, right?
/Joel
