On Tue, Mar 30, 2021, at 16:25, Rod Taylor wrote: > On Sat, 27 Mar 2021 at 16:28, Joel Jacobson <j...@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.
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
