Computed Fields
Computed Values
PostgreSQL Builtin (Preferred)
PostgreSQL has a builtin method for adding generated columns to tables. Generated columns are reflected identically to non-generated columns. This is the recommended approach to adding computed fields when your computation meets the restrictions. Namely:
- expression must be immutable
- expression may only reference the current row
For example:
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 | begin;
comment on schema public is '@graphql({"inflect_names": true})';
create table public.account(
id serial primary key,
first_name varchar(255) not null,
last_name varchar(255) not null,
-- Computed Column
full_name text generated always as (first_name || ' ' || last_name) stored
);
insert into public.account(first_name, last_name)
values
('Foo', 'Fooington');
select jsonb_pretty(
graphql.resolve($$
{
accountCollection {
edges {
node {
id
firstName
lastName
fullName
}
}
}
}
$$)
);
jsonb_pretty
------------------------------------------------------
{ +
"data": { +
"accountCollection": { +
"edges": [ +
{ +
"node": { +
"id": 1, +
"fullName": "Foo Fooington",+
"lastName": "Fooington", +
"firstName": "Foo" +
} +
} +
] +
} +
} +
}
(1 row)
rollback;
|
Extending Types with Functions
For arbitrary computations that do not meet the requirements for generated columns, a table's reflected GraphQL type can be extended by creating a function that:
- accepts a single argument of the table's tuple type
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 | begin;
comment on schema public is '@graphql({"inflect_names": true})';
create table public.account(
id serial primary key,
first_name varchar(255) not null,
last_name varchar(255) not null,
parent_id int references account(id)
);
-- Extend with function
create function public._full_name(rec public.account)
returns text
immutable
strict
language sql
as $$
select format('%s %s', rec.first_name, rec.last_name)
$$;
insert into public.account(first_name, last_name, parent_id)
values
('Foo', 'Fooington', 1);
select jsonb_pretty(
graphql.resolve($$
{
accountCollection {
edges {
node {
id
firstName
lastName
fullName
parent {
fullName
}
}
}
}
}
$$)
);
jsonb_pretty
---------------------------------------------------------
{ +
"data": { +
"accountCollection": { +
"edges": [ +
{ +
"node": { +
"id": 1, +
"parent": { +
"fullName": "Foo Fooington"+
}, +
"fullName": "Foo Fooington", +
"lastName": "Fooington", +
"firstName": "Foo" +
} +
} +
] +
} +
} +
}
(1 row)
rollback;
|
If the function is written in SQL, its volatility can impact freshness of data returned in mutations:
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
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187 | begin;
-- A computed field function written in SQL and marked stable might return stale results.
-- Directly from the postgres docs(https://www.postgresql.org/docs/current/xfunc-volatility.html):
--For functions written in SQL or in any of the standard procedural languages,
--there is a second important property determined by the volatility category,
--namely the visibility of any data changes that have been made by the SQL
--command that is calling the function. A VOLATILE function will see such
--changes, a STABLE or IMMUTABLE function will not. This behavior is
--implemented using the snapshotting behavior of MVCC (see Chapter 13): STABLE
--and IMMUTABLE functions use a snapshot established as of the start of the
--calling query, whereas VOLATILE functions obtain a fresh snapshot at the
--start of each query they execute.
--The solution is to mark these functions as volatile
create table parent
(
id uuid primary key default gen_random_uuid(),
count int2
);
create table child
(
id uuid primary key default gen_random_uuid(),
parent_id uuid references parent not null,
count int2
);
-- note that the function is marked stable and in written in sql
create or replace function _count(rec parent)
returns smallint
stable
language sql
as
$$
select sum(count)
from child
where parent_id = rec.id
$$;
insert into parent (id, count)
values ('8bcf0ee4-95ed-445f-808f-17b8194727ca', 1);
insert into child (id, parent_id, count)
values ('57738181-3d0f-45ad-96dd-3ba799b2d21d', '8bcf0ee4-95ed-445f-808f-17b8194727ca', 2),
('cb5993ff-e693-49cd-9114-a6510707e628', '8bcf0ee4-95ed-445f-808f-17b8194727ca', 3);
select jsonb_pretty(
graphql.resolve($$
query ParentQuery {
parentCollection {
edges {
node {
id
count
childCollection {
edges {
node {
count
}
}
}
}
}
}
}
$$)
);
jsonb_pretty
-----------------------------------------------------------------------
{ +
"data": { +
"parentCollection": { +
"edges": [ +
{ +
"node": { +
"id": "8bcf0ee4-95ed-445f-808f-17b8194727ca",+
"count": 5, +
"childCollection": { +
"edges": [ +
{ +
"node": { +
"count": 2 +
} +
}, +
{ +
"node": { +
"count": 3 +
} +
} +
] +
} +
} +
} +
] +
} +
} +
}
(1 row)
-- since _count is stable, the value returned in parent.count field will be stale
-- i.e. parent.count is still 5 instead of (3 + 5) = 8
select jsonb_pretty(
graphql.resolve($$
mutation ChildMutation {
updateChildCollection(
filter: { id: { eq: "57738181-3d0f-45ad-96dd-3ba799b2d21d" } }
set: { count: 5 }
) {
records {
id
count
parent {
id
count
}
}
}
}
$$)
);
jsonb_pretty
-----------------------------------------------------------------------
{ +
"data": { +
"updateChildCollection": { +
"records": [ +
{ +
"id": "57738181-3d0f-45ad-96dd-3ba799b2d21d", +
"count": 5, +
"parent": { +
"id": "8bcf0ee4-95ed-445f-808f-17b8194727ca",+
"count": 5 +
} +
} +
] +
} +
} +
}
(1 row)
-- note that the function is marked volatile
create or replace function _count(rec parent)
returns smallint
volatile
language sql
as
$$
select sum(count)
from child
where parent_id = rec.id
$$;
-- since _count is volatile, the value returned in parent.count field will be fresh
-- i.e. parent.count is correctly at (3 + 7) 10
select jsonb_pretty(
graphql.resolve($$
mutation ChildMutation {
updateChildCollection(
filter: { id: { eq: "57738181-3d0f-45ad-96dd-3ba799b2d21d" } }
set: { count: 7 }
) {
records {
id
count
parent {
id
count
}
}
}
}
$$)
);
jsonb_pretty
-----------------------------------------------------------------------
{ +
"data": { +
"updateChildCollection": { +
"records": [ +
{ +
"id": "57738181-3d0f-45ad-96dd-3ba799b2d21d", +
"count": 7, +
"parent": { +
"id": "8bcf0ee4-95ed-445f-808f-17b8194727ca",+
"count": 10 +
} +
} +
] +
} +
} +
}
(1 row)
rollback;
|
Computed Relationships
Computed relations can be helpful to express relationships:
- between entities that don't support foreign keys
- too complex to be expressed via a foreign key
If the relationship is simple, but involves an entity that does not support foreign keys e.g. Foreign Data Wrappers / Views, defining a comment directive is the easiest solution. See the view doc for a complete example. Note that for entities that do not support a primary key, like views, you must define one using a comment directive to use them in a computed relationship.
Alternatively, if the relationship is complex, or you need compatibility with PostgREST, you can define a relationship using set returning functions.
To-One
To One relationships can be defined using a function that returns setof <entity> rows 1
For example
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 | create table "Person" (
id int primary key,
name text
);
create table "Address"(
id int primary key,
"isPrimary" bool not null default false,
"personId" int references "Person"(id),
address text
);
-- Example computed relation
create function "primaryAddress"("Person")
returns setof "Address" rows 1
language sql
as
$$
select addr
from "Address" addr
where $1.id = addr."personId"
and addr."isPrimary"
limit 1
$$;
insert into "Person"(id, name)
values (1, 'Foo Barington');
insert into "Address"(id, "isPrimary", "personId", address)
values (4, true, 1, '1 Main St.');
|
results in the GraphQL type
and can be queried like a natively enforced relationship
To-Many
To-many relationships can be defined using a function that returns a setof <entity>
For example:
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 | create table "Person" (
id int primary key,
name text
);
create table "Address"(
id int primary key,
address text
);
create table "PersonAtAddress"(
id int primary key,
"personId" int not null,
"addressId" int not null
);
-- Computed relation to bypass "PersonAtAddress" table for cleaner API
create function "addresses"("Person")
returns setof "Address"
language sql
as
$$
select
addr
from
"PersonAtAddress" pa
join "Address" addr
on pa."addressId" = "addr".id
where
pa."personId" = $1.id
$$;
insert into "Person"(id, name)
values (1, 'Foo Barington');
insert into "Address"(id, address)
values (4, '1 Main St.');
insert into "PersonAtAddress"(id, "personId", "addressId")
values (2, 1, 4);
|
results in the GraphQL type
and can be queried like a natively enforced relationship