Skip to content

Computed Fields

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 parameter of the table's tuple type
  • has a name starting with an underscore
 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;