Skip to content

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

1
2
3
4
5
6
type Person implements Node {
  """Globally Unique Record Identifier"""
  nodeId: ID!
  ...
  primaryAddress: Address
}

and can be queried like a natively enforced relationship

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
{
  personCollection {
    edges {
      node {
        id
        name
        primaryAddress {
          address
        }
      }
    }

  }
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
{
  "data": {
    "personCollection": {
      "edges": [
        {
          "node": {
            "id": 1,
            "name": "Foo Barington",
            "primaryAddress": {
              "address": "1 Main St."
            }
          }
        }
      ]
    }
  }
}

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
type Person implements Node {
  """Globally Unique Record Identifier"""
  nodeId: ID!
  ...
  addresses(
    first: Int
    last: Int
    before: Cursor
    after: Cursor
    filter: AddressFilter
    orderBy: [AddressOrderBy!]
  ): AddressConnection
}

and can be queried like a natively enforced relationship

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
{
  personCollection {
    edges {
      node {
        id
        name
        addresses {
          edges {
            node {
              id
              address
            }
          }
        }
      }
    }
  }
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
{
  "data": {
    "personCollection": {
      "edges": [
        {
          "node": {
            "id": 1,
            "name": "Foo Barington",
            "addresses": {
              "edges": [
                {
                  "node": {
                    "id": 4,
                    "address": "1 Main St."
                  }
                }
              ]
            }
          }
        }
      ]
    }
  }
}