uNGQsAZZ

1/*
2Experimental PostgreSQL schema that keeps a universal_id table as a materialized
3view of all UUIDs in the system.
4
5Simply inserts each new UUID inserted into the tables with the trigger into the
6universal ID table.
7
8Rational is that a REST API could provide an endpoint like /object/:uuid which
9returns whatever that object happens to be, or a redirect to its canonical URL
10perhaps, no matter the type. In the unlikely event of a UUID collision between
11the many types, this will prevent that.
12*/
13
14CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
15
16CREATE OR REPLACE FUNCTION trigger_universal_id () RETURNS trigger
17LANGUAGE plpgsql
18VOLATILE
19AS $BODY$
20DECLARE
21    column_name text := TG_ARGV[0];
22    type_name text := TG_ARGV[1];
23BEGIN
24  CASE TG_OP
25  WHEN 'INSERT' THEN
26      EXECUTE FORMAT('
27          INSERT INTO universal_id
28          VALUES (
29            ($1)."' || column_name || '",
30            ($2))')
31        USING NEW, type_name;
32      RETURN NEW;
33  WHEN 'DELETE' THEN
34      EXECUTE FORMAT('
35          DELETE FROM universal_id
36          WHERE uuid = ($1)."' || column_name || '"')
37        USING OLD;
38      RETURN OLD;
39  END CASE;
40END
41$BODY$;
42
43
44CREATE TABLE factory (
45    factory_id uuid DEFAULT uuid_generate_v4() PRIMARY KEY,
46    factory_name text UNIQUE NOT NULL,
47    city text
48);
49
50CREATE TABLE widget (
51    widget_id uuid DEFAULT uuid_generate_v4() PRIMARY KEY,
52    widget_name text UNIQUE NOT NULL,
53    width integer,
54    height integer
55);
56
57CREATE TABLE universal_id (
58    uuid uuid PRIMARY KEY,
59    type text NOT NULL
60);
61
62CREATE TABLE factory_widget (
63    factory_id uuid REFERENCES factory (factory_id),
64    widget_id uuid REFERENCES widget (widget_id),
65    PRIMARY KEY (factory_id, widget_id)
66);
67
68
69CREATE TRIGGER factory_universal_id
70    AFTER INSERT OR DELETE ON factory
71    FOR EACH ROW EXECUTE PROCEDURE trigger_universal_id('factory_id', 'factory');
72
73CREATE TRIGGER widget_universal_id
74    AFTER INSERT OR DELETE ON widget
75    FOR EACH ROW EXECUTE PROCEDURE trigger_universal_id('widget_id', 'widget');