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

Arguments without a default value are required in the GraphQL schema, to make them optional they should have a default value.

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
  }
}

If there is no sensible default, and you still want to make the argument optional, consider using the default value null.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
create function "addNums"(a int default null, b int default null)
    returns int
    immutable
    language plpgsql
as $$
begin

    if a is null and b is null then
        raise exception 'a and b both can''t be null';
    end if;

    if a is null then
        return b;
    end if;

    if b is null then
        return a;
    end if;

    return a + b;
end;
$$;

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

1
2
3
query {
  addNums(a: 42)
}

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

Currently, null defaults are only supported as simple expressions, as shown in the previous example.

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