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:


_10
create function "addNums"(a int, b int)
_10
returns int
_10
immutable
_10
language sql
_10
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:


_10
create table account(
_10
id serial primary key,
_10
email varchar(255) not null
_10
);
_10
_10
create function "addAccount"(email text)
_10
returns int
_10
volatile
_10
language sql
_10
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:


_15
create table account(
_15
id serial primary key,
_15
email varchar(255) not null
_15
);
_15
_15
insert into account(email)
_15
values
_15
('a@example.com'),
_15
('b@example.com');
_15
_15
create function "accountById"("accountId" int)
_15
returns account
_15
stable
_15
language sql
_15
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:


_15
create table account(
_15
id int,
_15
email varchar(255),
_15
name text null
_15
);
_15
_15
insert into account(id, email, name)
_15
values
_15
(1, 'aardvark@x.com', 'aardvark'),
_15
(2, 'bat@x.com', null),
_15
(null, null, null);
_15
_15
create function "returnsAccountWithAllNullColumns"()
_15
returns account language sql stable
_15
as $$ select id, email, name from account where id is null; $$;

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


_16
create table "Account"(
_16
id serial primary key,
_16
email varchar(255) not null
_16
);
_16
_16
insert into "Account"(email)
_16
values
_16
('a@example.com'),
_16
('a@example.com'),
_16
('b@example.com');
_16
_16
create function "accountsByEmail"("emailToSearch" text)
_16
returns setof "Account"
_16
stable
_16
language sql
_16
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:


_16
create table "Account"(
_16
id serial primary key,
_16
email varchar(255) not null
_16
);
_16
_16
insert into "Account"(email)
_16
values
_16
('a@example.com'),
_16
('b@example.com'),
_16
('c@example.com');
_16
_16
create function "accountsByIds"("ids" int[])
_16
returns setof "Account"
_16
stable
_16
language sql
_16
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.


_10
create function "addNums"(a int default 1, b int default 2)
_10
returns int
_10
immutable
_10
language sql
_10
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.


_22
create function "addNums"(a int default null, b int default null)
_22
returns int
_22
immutable
_22
language plpgsql
_22
as $$
_22
begin
_22
_22
if a is null and b is null then
_22
raise exception 'a and b both can''t be null';
_22
end if;
_22
_22
if a is null then
_22
return b;
_22
end if;
_22
_22
if b is null then
_22
return a;
_22
end if;
_22
_22
return a + b;
_22
end;
_22
$$;

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