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:
12345create function "addNums"(a int, b int) returns int immutable language sqlas $$ 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:
12345678910create table account( id serial primary key, email varchar(255) not null);create function "addAccount"(email text) returns int volatile language sqlas $$ 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:
123456789101112131415create 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 sqlas $$ 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:
123456789101112131415create 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 stableas $$ select id, email, name from account where id is null; $$;
Functions returning multiple rows of a table or view are exposed as collections.
12345678910111213141516create 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 sqlas $$ select id, email from "Account" where email = "emailToSearch"; $$;
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:
12345678910111213141516create 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 sqlas $$ 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.
12345create function "addNums"(a int default 1, b int default 2) returns int immutable language sqlas $$ select a + b; $$;
If there is no sensible default, and you still want to make the argument optional, consider using the default value null.
12345678910111213141516171819202122create function "addNums"(a int default null, b int default null) returns int immutable language plpgsqlas $$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