Skip to main content

Custom Queries

You can add root-level Query fields to your GraphQL schema using “Custom Queries”. These are PostgreSQL functions, similar to computed columns, that can return scalars, records, lists or sets. Sets (denoted by RETURNS SETOF ...) are exposed as connections or lists (depending on your behavior configuration). The arguments to these functions will be exposed via GraphQL — named arguments are preferred, if your arguments are not named we will assign them an auto-generated name such as arg1.

你可以通过“自定义 Query(Custom Queries)”向 GraphQL schema 添加根级 Query 字段。它们本质上是与计算列类似的 PostgreSQL 函数,可以返回标量、单条记录、数组列表或结果集。结果集(以 RETURNS SETOF ... 声明)会根据你的 behavior 配置暴露为 Connection 或普通列表。函数参数也会同步暴露到 GraphQL 中——强烈建议使用命名参数;若参数未命名,PostGraphile 会自动分配诸如 arg1 的默认名称。

Prefer JavaScript?更倾向于用 JavaScript?

If you would rather add the field from JavaScript/TypeScript, use extendSchema. It allows you to specify with ergonomic GraphQL type extension SDL exactly what you want to add to your schema, and also allows you to define arbitrary Grafast plans to detail how to resolve the fields.

如果你更希望使用 JavaScript 或 TypeScript 添加字段,请使用 extendSchema。它能让你用直观顺手的 GraphQL 类型扩展 SDL 精准声明要加入 schema 的内容,并支持通过自定义的 Grafast 执行计划来控制字段的解析方式。

To create a function that PostGraphile will recognize as a custom query, it must obey the following rules:

要想让 PostgreSQL 函数被 PostGraphile 识别为自定义 Query,它必须满足以下规则:

  • if the function accepts arguments, the first argument must NOT be a table type (see computed columns)

    如果函数接收参数,第一个参数不得为表类型(参见计算列)

  • must NOT return VOID

    返回值不得为 VOID

  • must be marked as STABLE (or IMMUTABLE, though that tends to be less common)

    必须标记为 STABLE(或 IMMUTABLE,不过后者相对少见)

  • must be defined in one of the introspected schemas

    必须定义在纳入 introspection 的某个 schema 中

Prefer named return types优先使用具名返回类型

Functions that use returns table(...) are exposed as anonymous GraphQL types with auto-generated names and no attachable relationships. Always prefer named types, e.g. returns setof my_type, so you can augment the type later. (This also allows you to return single records: returns my_type.)

使用 returns table(...) 的函数会暴露为带有自动生成名称的匿名 GraphQL 类型,且无法挂载关联关系(relation)。请始终优先使用具名类型(例如 returns setof my_type),方便日后对类型进行扩展(同时这种方式也支持返回单条记录:returns my_type)。

For example the functions:

例如以下函数:

create function my_function(a int, b int) returns int as $$ … $$ language sql immutable;
create function my_other_function(a int, b int) returns my_table as $$ … $$ language sql stable;

could be queried in GraphQL like this:

在 GraphQL 中可以这样查询:

{
# For a function without arguments
myFunction

# For a function with arguments
myFunction(a: 1, b: 2)

# For a function that returns a row
myOtherFunction(a: 1, b: 2) {
id
}
}

Example示例​

Here we write a search query for our forum example using the PostgreSQL LIKE operator variant, ILIKE, which is case insensitive. The custom query we create is included in the forum example’s schema, so if you want to run that example locally you can try it out.

下面我们为论坛示例项目编写一个搜索查询,这里使用了 PostgreSQL LIKE 操作符的大小写不敏感变体 ILIKE。这个自定义 Query 已经包含在论坛示例的 schema 中,如果你在本地运行了该示例,可以直接上手测试。

-- Columns unnecessary to this demo were omitted. You can find the full table in
-- our forum example.
create table post (
…
headline text not null,
body text,
…
);

-- Create the function named `search_posts` with a text argument named `search`.
-- This will expose `Query.searchPosts(search: String!, ...)` to GraphQL.
create function search_posts(search text)
-- This function will return a set of posts from the `post` table. The
-- `setof` part is important to PostGraphile, check out our Functions article
-- to learn why.
returns setof post as $$
-- Write our advanced query as a SQL query!
select *
from post
where
-- Use the `ILIKE` operator on both the `headline` and `body` columns. If
-- either return true, return the post.
headline ilike ('%' || search || '%') or
body ilike ('%' || search || '%')
-- End the function declaring the language we used as SQL and add the
-- `STABLE` marker so PostGraphile knows its a query and not a mutation.
$$ language sql stable;

And that’s it! You can now use this function in your GraphQL like so:

大功告成!现在你就可以在 GraphQL 中像这样调用该函数了:

{
searchPosts(search: "Hello world", first: 5) {
pageInfo {
hasNextPage
}
totalCount
nodes {
headline
body
}
}
}
Performance Note性能提示

This function will have poor performance because ILIKE specifications of this form (beginning and ending with %) do not utilize indexes. If you’re doing this in a real application then it’s highly recommended that you look into PostgreSQL’s Full Text Search capabilities which can be exposed by a similar function. You may want to check out websearch_to_tsquery in PG11 as part of this.

这个示例函数的性能并不理想,因为前后均带 % 通配符的 ILIKE 匹配条件无法有效利用索引。如果在真实应用中实现搜索,强烈建议了解 PostgreSQL 全文检索(Full Text Search)能力,同样可以通过类似的函数将其暴露出来;同时不妨了解一下 PG11 引入的 websearch_to_tsquery。

Extending schema in JS在 JS 中扩展 schema​

If you prefer adding to your schema on the JavaScript side, you can use extendSchema.

如果你更偏好在 JavaScript 侧向 schema 添加字段,可以使用 extendSchema。

Advice使用建议​

Though it may be tempting to expose huge collections via a function, it’s important to be aware that, when paginating across a function, only LIMIT/OFFSET pagination can be used. (For convenience and consistency we expose cursor pagination over functions, but internally this is just mapped to LIMIT/OFFSET pagination.) Because of this, and because functions are seen as a “black box” by PostgreSQL, if you try and paginate to, say, the 100,000th record then PostgreSQL will literally have to execute the function until all 100,000 records have been generated, and this is often expensive.

虽然通过函数暴露大型数据集很有诱惑力,但务必注意:对函数结果进行分页时,底层只能使用 LIMIT/OFFSET 分页。(为了便利性和接口一致性,PostGraphile 对外仍然暴露为 cursor 分页,但内部实际上会映射为 LIMIT/OFFSET。)正因如此,再加上 PostgreSQL 通常把函数当作“黑盒”来执行,如果你想翻页到第 100,000 条记录,PostgreSQL 就必须实际执行函数并一路生成前 100,000 条记录——这种开销往往非常高昂。

One way to solve this is to have your function apply its own internal limits and filters which can be exposed as GraphQL field arguments — if you reduce the amount of data that the function can produce (e.g. to 100 rows) then it reduces the potential cost of having this function in your schema.

一种解决思路是在函数内部自行加上条数上限和过滤条件,并将这些参数暴露为 GraphQL 字段参数——只要限制函数最多只能产出少量数据(例如 100 行),就能有效控制将其暴露在 schema 中带来的潜在性能开销。

Disclaimer免责声明

The information in this advice section is not 100% true, for example PostgreSQL can “see through” some SQL functions and has a highly intelligent query planner. If you’re an expert on PostgreSQL then you should ignore this advice and go with your own understanding, it’s only intended to help beginners from shooting themselves in the foot performance-wise.

本节建议并非 100% 绝对——例如 PostgreSQL 能够“看穿(内联展开)”部分纯 SQL 函数,而且它的查询规划器也非常智能。如果你本身就是 PostgreSQL 专家,大可忽略这条建议、按自己的判断行事;这里主要是为了提醒初学者避免不小心踩进性能陷阱。