hsEekSYA

1CREATE TABLE IF NOT EXISTS projects_t
2(
3  name     TEXT NOT NULL UNIQUE,
4  metadata JSONB,
5  content  BYTEA,
6  version  TIMESTAMP WITH TIME ZONE DEFAULT now(),
7  PRIMARY KEY (name, version)
8);
9	
10CREATE OR REPLACE VIEW projects AS
11  SELECT DISTINCT ON (name)
12    name,
13    metadata,
14    "content"
15  FROM projects_t
16  ORDER BY name, version DESC;
17
18
19CREATE OR REPLACE FUNCTION f_insert_projects()
20  RETURNS TRIGGER
21LANGUAGE plpgsql AS
22$$
23BEGIN
24  INSERT INTO projects_t (name, metadata, content) VALUES (NEW.name, NEW.metadata, NEW.content);
25END;
26$$;
27
28CREATE OR REPLACE FUNCTION f_update_projects()
29  RETURNS TRIGGER
30LANGUAGE plpgsql AS
31$$
32BEGIN
33  INSERT INTO projects_t (name, metadata, content) VALUES (NEW.name, NEW.metadata, NEW.content);
34END;
35$$;
36
37CREATE OR REPLACE FUNCTION f_delete_projects()
38  RETURNS TRIGGER
39LANGUAGE plpgsql AS
40$$
41BEGIN
42  DELETE FROM projects_t
43  WHERE name = OLD.name;
44END;
45$$;
46
47CREATE TRIGGER insert_project_via_view_trig
48INSTEAD OF INSERT ON projects
49FOR EACH ROW EXECUTE PROCEDURE f_insert_projects();
50
51CREATE TRIGGER update_project_via_view_trig
52INSTEAD OF UPDATE ON projects
53FOR EACH ROW EXECUTE PROCEDURE f_update_projects();
54
55CREATE TRIGGER delete_project_via_view_trig
56INSTEAD OF DELETE ON projects
57FOR EACH ROW EXECUTE PROCEDURE f_delete_projects();
58	
59INSERT INTO projects VALUES ('test', ('{ "last_modified_time": "' || (now() at time zone 'utc')::text || '", "last_modified_user": "' || current_user || '" }')::jsonb) ON CONFLICT (name) DO UPDATE SET content = EXCLUDED.content, metadata = EXCLUDED.metadata;