-
Notifications
You must be signed in to change notification settings - Fork 0
/
user.sql
93 lines (78 loc) · 1.84 KB
/
user.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
\set QUIET
\pset pager off
SET client_min_messages TO WARNING;
\set ECHO queries
\unset QUIET
CREATE EXTENSION jsonschema;
\echo
\prompt xxx
SET jsonschema.default_draft TO 'V2020';
\echo
\prompt xxx
CREATE TEMPORARY TABLE json_schemas(
schema JSON
);
\echo
\prompt xxx
\set ECHO ALL
\copy json_schemas FROM PROGRAM 'jq -c . eg/*.schema.json';
\set ECHO queries
\echo
\prompt xxx
SELECT jsonschema_is_valid(
'https://example.com/user-profile.schema.json',
VARIADIC ARRAY(SELECT schema from json_schemas)
);
\prompt xxx
SELECT jsonschema_is_valid(
'https://example.com/address.schema.json',
VARIADIC ARRAY(SELECT schema from json_schemas)
);
\prompt xxx
CREATE OR REPLACE FUNCTION validate_user(
data json
) RETURNS BOOLEAN LANGUAGE SQL STABLE AS $$
SELECT jsonschema_validates(
data, 'https://example.com/user-profile.schema.json',
VARIADIC ARRAY(SELECT schema from json_schemas)
);
$$;
\echo
\prompt xxx
CREATE TEMPORARY TABLE json_users (
id SERIAL PRIMARY KEY,
body JSON CHECK (validate_user(body))
);
\echo
\prompt xxx
INSERT INTO json_users (body) VALUES(json_build_object(
'username', 'theory',
'email', 'theory@example.com'
));
\echo
\prompt xxx
SELECT body FROM json_users WHERE body->>'username' = 'theory';
\prompt xxx
INSERT INTO json_users (body) VALUES(json_build_object(
'username', 'naomi',
'email', 'nagata@rocinante.ship',
'address', json_build_object(
'locality', 'Series',
'region', 'The Belt',
'countryName', 'Sol System'
)
));
\echo
\prompt xxx
SELECT body FROM json_users WHERE body->>'username' = 'naomi';
\prompt xxx
INSERT INTO json_users (body) VALUES(json_build_object(
'username', 42,
'email', 'hhgttg@example.com'
));
\echo
\prompt xxx
\set ECHO none
\set QUIET
DROP FUNCTION validate_user(json) CASCADE;
DROP EXTENSION jsonschema;