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

On 3/29/21 11:59 AM, Joel Jacobson wrote:
> On Sun, Mar 28, 2021, at 16:04, Tom Lane wrote:
>> I'm imagining a syntax in which
>> you give the constraint name instead of the column name. Thought
>> experiment: how could the original syntax proposal make any use of
>> a multi-column foreign key?
>
> Thanks for coming up with this genius idea.
>
> At first I didn't see the beauty of it; I wrongly thought the constraint name needed to be
> unique per schema, but I realize we could just use the foreign table's name
> as the constraint name, which will allow a nice syntax:
>
> SELECT DISTINCT order_details.orders.customers.company_name
> FROM order_details
> WHERE order_details.products.product_name = 'Chocolade';
>
> Given this data model:
>
> CREATE TABLE customers (
> customer_id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
> company_name text,
> PRIMARY KEY (customer_id)
> );
>
> CREATE TABLE orders (
> order_id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
> customer_id bigint NOT NULL,
> PRIMARY KEY (order_id),
> CONSTRAINT customers FOREIGN KEY (customer_id) REFERENCES customers
> );
>
> CREATE TABLE products (
> product_id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
> product_name text NOT NULL,
> PRIMARY KEY (product_id)
> );
>
> CREATE TABLE order_details (
> order_id bigint NOT NULL,
> product_id bigint NOT NULL,
> PRIMARY KEY (order_id, product_id),
> CONSTRAINT orders FOREIGN KEY (order_id) REFERENCES orders,
> CONSTRAINT products FOREIGN KEY (product_id) REFERENCES products
> );

If you write your schema like this, then it becomes standards compliant:

CREATE TYPE customers AS (
company_name text
);
CREATE TABLE customers OF customers (
REF IS customer_id SYSTEM GENERATED
);

CREATE TYPE orders AS (
customer REF(customers) NOT NULL
);
CREATE TABLE orders OF orders (
REF IS order_id SYSTEM GENERATED
);

CREATE TYPE products AS (
product_name text
);
CREATE TABLE products OF products (
REF IS product_id SYSTEM GENERATED
);

CREATE TABLE order_details (
"order" REF(orders),
product REF(products),
quantity integer,
PRIMARY KEY ("order", product)
);

And the query would be:

SELECT DISTINCT order_details."order"->customer->company_name
FROM order_details
WHERE order_details.product->product_name = 'Chocolade';

Postgres already supports most of that, but not all of it.
--
Vik Fearing