dZkBEcEd

1setup.sql
2------
3CREATE TABLE IF NOT EXISTS parent_table (
4    guid    varchar(64) UNIQUE NOT NULL,
5    kind    varchar(64),
6    name    varchar(64)
7);
8
9CREATE TABLE IF NOT EXISTS naughty_children
10(
11CHECK (kind = 'Naughty'),
12LIKE parent_table INCLUDING ALL
13) INHERITS (parent_table);
14
15CREATE TABLE IF NOT EXISTS nice_children
16(
17CHECK (kind = 'Nice'),
18LIKE parent_table INCLUDING ALL
19) INHERITS (parent_table);
20
21create or replace function on_parent_table_insert() returns trigger as $$
22begin
23  if ( new.kind = 'Naughty') then
24    insert into naughty_children values (new.*);
25  elsif ( new.kind = 'Nice') then 
26    insert into nice_children values (new.*);
27  else
28    raise exception 'unrecognized schema';
29  end if;
30
31  return null;
32end;
33$$ language plpgsql;
34
35DROP TRIGGER IF EXISTS parent_insert_trigger ON parent_table;
36
37CREATE TRIGGER parent_insert_trigger
38BEFORE INSERT ON parent_table
39FOR EACH ROW EXECUTE PROCEDURE on_parent_table_insert();
40
41-----
42example.rb
43-----
44require 'pg'
45require 'sequel'
46
47def new_guid
48  "sample-guid-#{rand(1_000_000)}"
49end
50
51@pg_conn = PG.connect(dbname: "northpole_inc")
52def pg_example
53  @pg_conn.exec("insert into parent_table (guid, kind, name) VALUES ('#{new_guid}', 'Naughty', 'Veronica')")
54  @pg_conn.exec("insert into parent_table (guid, kind, name) VALUES ('#{new_guid}', 'Nice', 'Charlie')")
55  puts "successfully added two kids."
56end
57
58DB = Sequel.connect("postgres://localhost:5432/northpole_inc")
59@parent_table = DB[:parent_table]
60def sequel_dataset_example
61  @parent_table.insert(name: 'Cindy Lou Who', kind: "Nice", guid: "#{new_guid}")
62  @parent_table.insert(name: 'The Grinch', kind: "Naughty", guid: "#{new_guid}")
63  puts "successfully added two kids."
64end
65
66# With transactions turned off, you can see the first one is saved before the error.
67# Sequel::Model.use_transactions = false
68class ParentTable < Sequel::Model(:parent_table)
69end
70
71def sequel_model_example
72  ParentTable.create(name: 'Chris', kind: "Nice", guid: "#{new_guid}")
73  ParentTable.create(name: 'Stewie', kind: "Naughty", guid: "#{new_guid}")
74  puts "successfully added two kids."
75end
76
77-----
78SQL log
79-----
80
81# from sequel_dataset_example:
82LOG: statement: INSERT INTO "parent_table" ("name", "kind", "guid") VALUES ('Cindy Lou Who', 'Nice', 'sample-guid-893737') RETURNING NULL
83LOG: duration: 1.441 ms
84
85
86# from sequel_model_example:
87LOG: statement: INSERT INTO "parent_table" ("name", "kind", "guid") VALUES ('Chris', 'Nice', 'sample-guid-622719') RETURNING *
88LOG: duration: 4.539 ms
89LOG: statement: INSERT INTO "parent_table" ("name", "kind", "guid") VALUES ('Chris', 'Nice', 'sample-guid-622719') RETURNING NULL
90ERROR: duplicate key value violates unique constraint "nice_children_guid_key"
91DETAIL: Key (guid)=(sample-guid-622719) already exists.
92CONTEXT: SQL statement "insert into nice_children values (new.*)"
93PL/pgSQL function on_parent_table_insert() line 6 at SQL statement
94STATEMENT: INSERT INTO "parent_table" ("name", "kind", "guid") VALUES ('Chris', 'Nice', 'sample-guid-622719') RETURNING NULL