Skip to content
GraphQL

Functions

Using Postgres Functions with GraphQL.

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:

create function "addNums"(a int, b int)
  returns int
  immutable
  language sql
as $$ select a + b; $$;

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

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; $$;

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:

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"; $$;

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:

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; $$;

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

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"; $$;

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:

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); $$;

Default Arguments#

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

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

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

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;
$$;

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