Skip to content

Functions

Functions can be exposed by pg_graphql to allow running custom queries or mutations.

Query vs Mutation

For example, a function to add two numbers will be available on the query type as a field:

1
2
3
4
5
create function "addNums"(a int, b int)
  returns int
  immutable
  language sql
as $$ select a + b; $$;

1
2
3
type Query {
  addNums(a: Int!, b: Int!): Int
}

1
2
3
query {
  addNums(a: 2, b: 3)
}

1
2
3
4
5
{
  "data": {
    "addNums": 5
  }
}

Functions marked immutable or stable are available on the query type. Functions marked with the default volatile category are available on the mutation type:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
create table account(
  id serial primary key,
  email varchar(255) not null
);

create function "addAccount"(email text)
  returns int
  volatile
  language sql
as $$ insert into account (email) values (email) returning id; $$;

1
2
3
type Mutation {
  addAccount(email: String!): Int
}

1
2
3
mutation {
  addAccount(email: "email@example.com")
}

1
2
3
4
5
{
  "data": {
    "addAccount": 1
  }
}

Supported Return Types

Built-in GraphQL scalar types Int, Float, String, Boolean and custom scalar types are supported as function arguments and return types. Function types returning a table or view are supported as well. Such functions implement the Node interface:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
create table account(
  id serial primary key,
  email varchar(255) not null
);

insert into account(email)
values
  ('a@example.com'),
  ('b@example.com');

create function "accountById"("accountId" int)
  returns account
  stable
  language sql
as $$ select id, email from account where id = "accountId"; $$;

1
2
3
type Query {
  accountById(email: String!): Account
}

1
2
3
4
5
6
7
query {
  accountById(accountId: 1) {
      id
      email
      nodeId
  }
}

1
2
3
4
5
6
7
8
9
{
  "data": {
    "accountById": {
      "id": 1,
      "email": "a@example.com"
      "nodeId": "WyJwdWJsaWMiLCAiYWNjb3VudCIsIDFd"
    }
  }
}

Since Postgres considers a row/composite type containing only null values to be null, the result can be a little surprising in this case. Instead of an object with all columns null, the top-level field is null:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
create table account(
    id int,
    email varchar(255),
    name text null
);

insert into account(id, email, name)
values
    (1, 'aardvark@x.com', 'aardvark'),
    (2, 'bat@x.com', null),
    (null, null, null);

create function "returnsAccountWithAllNullColumns"()
    returns account language sql stable
as $$ select id, email, name from account where id is null; $$;

1
2
3
4
5
6
7
8
query {
  returnsAccountWithAllNullColumns {
    id
    email
    name
    __typename
  }
}

1
2
3
4
5
{
  "data": {
    "returnsAccountWithAllNullColumns": null
  }
}

Functions returning multiple rows of a table or view are exposed as collections.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
create table "Account"(
  id serial primary key,
  email varchar(255) not null
);

insert into "Account"(email)
values
  ('a@example.com'),
  ('a@example.com'),
  ('b@example.com');

create function "accountsByEmail"("emailToSearch" text)
  returns setof "Account"
  stable
  language sql
as $$ select id, email from "Account" where email = "emailToSearch"; $$;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
type Query {
  accountsByEmail(
    emailToSearch: String!

    """Query the first `n` records in the collection"""
    first: Int

    """Query the last `n` records in the collection"""
    last: Int

    """Query values in the collection before the provided cursor"""
    before: Cursor

    """Query values in the collection after the provided cursor"""
    after: Cursor

    """Filters to apply to the results set when querying from the collection"""
    filter: AccountFilter

    """Sort order to apply to the collection"""
    orderBy: [AccountOrderBy!]
  ): AccountConnection
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
query {
  accountsByEmail(emailToSearch: "a@example.com", first: 1) {
    edges {
      node {
        id
        email
      }
    }
  }
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
{
  "data": {
    "accountsByEmail": {
      "edges": [
        {
          "node": {
            "id": 1,
            "email": "a@example.com"
          }
        }
      ]
    }
  }
}

Note

A set returning function with any of its argument names clashing with argument names of a collection (first, last, before, after, filter, or orderBy) will not be exposed.

Functions accepting or returning arrays of non-composite types are also supported. In the following example, the ids array is used to filter rows from the Account table:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
create table "Account"(
  id serial primary key,
  email varchar(255) not null
);

insert into "Account"(email)
values
  ('a@example.com'),
  ('b@example.com'),
  ('c@example.com');

create function "accountsByIds"("ids" int[])
  returns setof "Account"
  stable
  language sql
as $$ select id, email from "Account" where id = any(ids); $$;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
type Query {
  accountsByIds(
    ids: Int[]!

    """Query the first `n` records in the collection"""
    first: Int

    """Query the last `n` records in the collection"""
    last: Int

    """Query values in the collection before the provided cursor"""
    before: Cursor

    """Query values in the collection after the provided cursor"""
    after: Cursor

    """Filters to apply to the results set when querying from the collection"""
    filter: AccountFilter

    """Sort order to apply to the collection"""
    orderBy: [AccountOrderBy!]
  ): AccountConnection
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
query {
  accountsByIds(ids: [1, 2]) {
    edges {
      node {
        id
        email
      }
    }
  }
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
{
  "data": {
    "accountsByIds": {
      "edges": [
        {
          "node": {
            "id": 1,
            "email": "a@example.com"
          }
        },
        {
          "node": {
            "id": 2,
            "email": "b@example.com"
          }
        }
      ]
    }
  }
}

Default Arguments

Functions with default arguments can have their default arguments omitted.

1
2
3
4
5
create function "addNums"(a int default 1, b int default 2)
  returns int
  immutable
  language sql
as $$ select a + b; $$;

1
2
3
type Query {
  addNums(a: Int, b: Int): Int
}

1
2
3
query {
  addNums(b: 20)
}

1
2
3
4
5
{
  "data": {
    "addNums": 21
  }
}

Limitations

The following features are not yet supported. Any function using these features is not exposed in the API:

  • Functions that accept a table's tuple type
  • Overloaded functions
  • Functions with a nameless argument
  • Functions returning void
  • Variadic functions
  • Functions that accept or return an array of composite type
  • Functions that accept or return an enum type or an array of enum type