Postgres 18

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE profile (
  id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY
  , email TEXT NOT NULL UNIQUE
  , name TEXT NOT NULL
  , type TEXT NOT NULL CHECK (type IN ('client', 'manager', 'admin'))
  , client_id BIGINT UNIQUE GENERATED ALWAYS AS (CASE WHEN type = 'client' THEN id END) STORED
  , manager_id BIGINT UNIQUE GENERATED ALWAYS AS (CASE WHEN type = 'manager' THEN id END) STORED
  , created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE profile_detail (
  profile_id BIGINT PRIMARY KEY REFERENCES profile (id)
  , bio TEXT
  , phone TEXT
);

CREATE TABLE address (
  id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY
  , profile_id BIGINT NOT NULL REFERENCES profile (id)
  , city TEXT NOT NULL
  , street TEXT NOT NULL
  , UNIQUE (id, profile_id)
);

CREATE INDEX address_profile_id_idx ON address (profile_id);

CREATE TABLE item (
  id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY
  , parent_item_id BIGINT REFERENCES item (id)
  , sku TEXT NOT NULL UNIQUE
  , name TEXT NOT NULL
  , price NUMERIC(12, 2) NOT NULL DEFAULT 0
);

CREATE INDEX item_parent_item_id_idx ON item (parent_item_id);

CREATE TABLE document (
  id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY
  , client_id BIGINT NOT NULL REFERENCES profile (client_id)
  , manager_id BIGINT REFERENCES profile (manager_id)
  , delivery_address_id BIGINT
  , doc_number TEXT NOT NULL UNIQUE
  , issued_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
  , CONSTRAINT document_delivery_address_id_client_id_fkey
    FOREIGN KEY (delivery_address_id, client_id) REFERENCES address (id, profile_id)
);

CREATE INDEX document_client_id_idx ON document (client_id);
CREATE INDEX document_manager_id_idx ON document (manager_id);
CREATE INDEX document_delivery_address_id_idx ON document (delivery_address_id);

CREATE TABLE document_item (
  document_id BIGINT NOT NULL REFERENCES document (id)
  , item_id BIGINT NOT NULL REFERENCES item (id)
  , quantity INT NOT NULL DEFAULT 1 CHECK (quantity > 0)
  , PRIMARY KEY (document_id, item_id)
);

CREATE INDEX document_item_item_id_idx ON document_item (item_id);

INSERT INTO profile (email, name, type) VALUES
  ('c1@x.io', 'Client One', 'client')
  , ('c2@x.io', 'Client Two', 'client')
  , ('m@x.io', 'Manager', 'manager')
  , ('c3@x.io', 'Client Three', 'client')
;

INSERT INTO profile_detail (profile_id, bio, phone) VALUES
  (1, 'Regular buyer', '+49 30 1111111')
  , (3, 'Sales manager', '+49 30 5555555')
;

INSERT INTO address (profile_id, city, street) VALUES
  (1, 'Berlin', 'Alexanderplatz 1')
  , (2, 'Paris', 'Rue de Rivoli 2')
;

INSERT INTO item (parent_item_id, sku, name, price) VALUES
  (NULL, 'SKU-1', 'Laptop', 1200)
  , (1, 'SKU-2', 'Mouse', 25)
  , (1, 'SKU-3', 'Keyboard', 90)
  , (2, 'SKU-4', 'Mouse Pro', 45)
;

INSERT INTO document (client_id, manager_id, delivery_address_id, doc_number, issued_at) VALUES
  (1, 3, 1, 'DOC-1', '2026-07-01')
  , (1, NULL, NULL, 'DOC-2', '2026-07-15')
  , (2, 3, 2, 'DOC-3', '2026-07-20')
;

INSERT INTO document_item (document_id, item_id, quantity) VALUES
  (1, 1, 1)
  , (1, 2, 2)
  , (2, 3, 1)
  , (3, 2, 5)
;
CREATE OR REPLACE FUNCTION public.address_list(profile)
 RETURNS SETOF address
 LANGUAGE sql
 STABLE PARALLEL SAFE
AS $function$SELECT * FROM public.address WHERE (profile_id) = (($1).id)$function$
;
CREATE OR REPLACE FUNCTION public.client(document)
 RETURNS SETOF profile
 LANGUAGE sql
 STABLE PARALLEL SAFE
AS $function$SELECT * FROM public.profile WHERE (client_id) = (($1).client_id)$function$
;
CREATE OR REPLACE FUNCTION public.client_document_list(profile)
 RETURNS SETOF document
 LANGUAGE sql
 STABLE PARALLEL SAFE
AS $function$SELECT * FROM public.document WHERE (client_id) = (($1).client_id)$function$
;
CREATE OR REPLACE FUNCTION public.delivery_address(document)
 RETURNS SETOF address
 LANGUAGE sql
 STABLE PARALLEL SAFE
AS $function$SELECT * FROM public.address WHERE (id, profile_id) = (($1).delivery_address_id, ($1).client_id)$function$
;
CREATE OR REPLACE FUNCTION public.document(document_item)
 RETURNS SETOF document
 LANGUAGE sql
 STABLE PARALLEL SAFE
AS $function$SELECT * FROM public.document WHERE (id) = (($1).document_id)$function$
;
CREATE OR REPLACE FUNCTION public.document_item_list(document)
 RETURNS SETOF document_item
 LANGUAGE sql
 STABLE PARALLEL SAFE
AS $function$SELECT * FROM public.document_item WHERE (document_id) = (($1).id)$function$
;
CREATE OR REPLACE FUNCTION public.document_item_list(item)
 RETURNS SETOF document_item
 LANGUAGE sql
 STABLE PARALLEL SAFE
AS $function$SELECT * FROM public.document_item WHERE (item_id) = (($1).id)$function$
;
CREATE OR REPLACE FUNCTION public.document_list(address)
 RETURNS SETOF document
 LANGUAGE sql
 STABLE PARALLEL SAFE
AS $function$SELECT * FROM public.document WHERE (delivery_address_id, client_id) = (($1).id, ($1).profile_id)$function$
;
CREATE OR REPLACE FUNCTION public.item(document_item)
 RETURNS SETOF item
 LANGUAGE sql
 STABLE PARALLEL SAFE
AS $function$SELECT * FROM public.item WHERE (id) = (($1).item_id)$function$
;
CREATE OR REPLACE FUNCTION public.item_list(item)
 RETURNS SETOF item
 LANGUAGE sql
 STABLE PARALLEL SAFE
AS $function$SELECT * FROM public.item WHERE (parent_item_id) = (($1).id)$function$
;
CREATE OR REPLACE FUNCTION public.manager(document)
 RETURNS SETOF profile
 LANGUAGE sql
 STABLE PARALLEL SAFE
AS $function$SELECT * FROM public.profile WHERE (manager_id) = (($1).manager_id)$function$
;
CREATE OR REPLACE FUNCTION public.manager_document_list(profile)
 RETURNS SETOF document
 LANGUAGE sql
 STABLE PARALLEL SAFE
AS $function$SELECT * FROM public.document WHERE (manager_id) = (($1).manager_id)$function$
;
CREATE OR REPLACE FUNCTION public.parent_item(item)
 RETURNS SETOF item
 LANGUAGE sql
 STABLE PARALLEL SAFE
AS $function$SELECT * FROM public.item WHERE (id) = (($1).parent_item_id)$function$
;
CREATE OR REPLACE FUNCTION public.profile(profile_detail)
 RETURNS SETOF profile
 LANGUAGE sql
 STABLE PARALLEL SAFE
AS $function$SELECT * FROM public.profile WHERE (id) = (($1).profile_id)$function$
;
CREATE OR REPLACE FUNCTION public.profile(address)
 RETURNS SETOF profile
 LANGUAGE sql
 STABLE PARALLEL SAFE
AS $function$SELECT * FROM public.profile WHERE (id) = (($1).profile_id)$function$
;
CREATE OR REPLACE FUNCTION public.profile_detail(profile)
 RETURNS SETOF profile_detail
 LANGUAGE sql
 STABLE PARALLEL SAFE
AS $function$SELECT * FROM public.profile_detail WHERE (profile_id) = (($1).id)$function$
;

CREATE TABLE



CREATE TABLE



CREATE TABLE



CREATE INDEX



CREATE TABLE



CREATE INDEX



CREATE TABLE



CREATE INDEX



CREATE INDEX



CREATE INDEX



CREATE TABLE



CREATE INDEX



INSERT 0 4



INSERT 0 2



INSERT 0 2



INSERT 0 4



INSERT 0 3



INSERT 0 4



CREATE FUNCTION



CREATE FUNCTION



CREATE FUNCTION



CREATE FUNCTION



CREATE FUNCTION



CREATE FUNCTION



CREATE FUNCTION



CREATE FUNCTION



CREATE FUNCTION



CREATE FUNCTION



CREATE FUNCTION



CREATE FUNCTION



CREATE FUNCTION



CREATE FUNCTION



CREATE FUNCTION



CREATE FUNCTION


-- document lines with item and client: three relations, zero ON
SELECT item.name item_name, client.name client_name, document_item.quantity
FROM document_item, document(document_item), item(document_item), client(document);


item_name
client_name
quantity


Laptop
Client One
1

Mouse
Client One
2

Keyboard
Client One
1

Mouse
Client Two
5



SELECT 4


-- optional manager: LEFT JOIN ... ON true
SELECT d.doc_number, p.name client_name, m.name manager_name
FROM document d, client(d) p
LEFT JOIN manager(d) m ON true;


doc_number
client_name
manager_name


DOC-1
Client One
Manager

DOC-2
Client One
null

DOC-3
Client Two
Manager



SELECT 3


-- composite FK stays hidden
SELECT d.doc_number, a.city, a.street
FROM document d, delivery_address(d) a;


doc_number
city
street


DOC-1
Berlin
Alexanderplatz 1

DOC-3
Paris
Rue de Rivoli 2



SELECT 2


-- the whole related row as a field
SELECT doc_number, (document.client).* FROM document;


doc_number
id
email
name
type
client_id
manager_id
created_at


DOC-1
1
c1@x.io
Client One
client
1
null
2026-08-02 18:59:51.201116+00

DOC-2
1
c1@x.io
Client One
client
1
null
2026-08-02 18:59:51.201116+00

DOC-3
2
c2@x.io
Client Two
client
2
null
2026-08-02 18:59:51.201116+00



SELECT 3


-- clients without documents
SELECT name FROM profile
WHERE type = 'client' AND NOT EXISTS (SELECT FROM client_document_list(profile));


name


Client Three



SELECT 1
item_name	client_name	quantity
Laptop	Client One	1
Mouse	Client One	2
Keyboard	Client One	1
Mouse	Client Two	5
doc_number	client_name	manager_name
DOC-1	Client One	Manager
DOC-2	Client One	null
DOC-3	Client Two	Manager
doc_number	id	email	name	type	client_id	manager_id	created_at
DOC-1	1	c1@x.io	Client One	client	1	null	2026-08-02 18:59:51.201116+00
DOC-2	1	c1@x.io	Client One	client	1	null	2026-08-02 18:59:51.201116+00
DOC-3	2	c2@x.io	Client Two	client	2	null	2026-08-02 18:59:51.201116+00