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:
_10create function "addNums"(a int, b int)_10 returns int_10 immutable_10 language sql_10as $$ 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:
_10create table account(_10 id serial primary key,_10 email varchar(255) not null_10);_10_10create function "addAccount"(email text)_10 returns int_10 volatile_10 language sql_10as $$ 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:
_15create table account(_15 id serial primary key,_15 email varchar(255) not null_15);_15_15insert into account(email)_15values_15 ('a@example.com'),_15 ('b@example.com');_15_15create function "accountById"("accountId" int)_15 returns account_15 stable_15 language sql_15as $$ 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:
_15create table account(_15 id int,_15 email varchar(255),_15 name text null_15);_15_15insert into account(id, email, name)_15values_15 (1, 'aardvark@x.com', 'aardvark'),_15 (2, 'bat@x.com', null),_15 (null, null, null);_15_15create function "returnsAccountWithAllNullColumns"()_15 returns account language sql stable_15as $$ select id, email, name from account where id is null; $$;
Functions returning multiple rows of a table or view are exposed as collections.
_16create table "Account"(_16 id serial primary key,_16 email varchar(255) not null_16);_16_16insert into "Account"(email)_16values_16 ('a@example.com'),_16 ('a@example.com'),_16 ('b@example.com');_16_16create function "accountsByEmail"("emailToSearch" text)_16 returns setof "Account"_16 stable_16 language sql_16as $$ 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:
_16create table "Account"(_16 id serial primary key,_16 email varchar(255) not null_16);_16_16insert into "Account"(email)_16values_16 ('a@example.com'),_16 ('b@example.com'),_16 ('c@example.com');_16_16create function "accountsByIds"("ids" int[])_16 returns setof "Account"_16 stable_16 language sql_16as $$ 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.
_10create function "addNums"(a int default 1, b int default 2)_10 returns int_10 immutable_10 language sql_10as $$ select a + b; $$;
If there is no sensible default, and you still want to make the argument optional, consider using the default value null.
_22create function "addNums"(a int default null, b int default null)_22 returns int_22 immutable_22 language plpgsql_22as $$_22begin_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;_22end;_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