Skip to main content

Database Functions

One of the easiest ways to add more capabilities to your PostGraphile schema is with PostgreSQL functions. The three main methods are:

为 PostGraphile schema 扩展功能最简单的方式之一,就是使用 PostgreSQL 函数。主要有以下三种方式:

  • Computed Columns enable you to add a computed field to a table type

    计算列(Computed Columns):用于向表类型添加计算字段

  • Custom Queries enable you to add a root level Query field which can return a scalar, list, custom type, table row or even a table connection

    自定义 Query(Custom Queries):用于添加根级 Query 字段,支持返回标量、列表、自定义类型、单行表记录,甚至表的 Connection

  • Custom Mutations enable you to add a root level Mutation field which can cause modifications to your database and return nothing (void), a scalar, list, custom type, table row or list of table rows (but not a connection, since you cannot paginate over a mutation)

    自定义 Mutation(Custom Mutations):用于添加根级 Mutation 字段,可对数据库执行变更操作,并返回空值(void)、标量、列表、自定义类型、单行表记录或表记录列表(不支持 Connection,因为无法对 mutation 进行分页)

If you want to leverage database functionality rather than PostGraphile plugins to enhance your API then it’s very important to understand PostgreSQL functions. Functions allow you to define business logic in the database using SQL or one of many other scripting languages.

如果你想借助数据库自身的能力(而非 PostGraphile 插件)来增强 API,深入理解 PostgreSQL 函数就显得尤为重要。借助函数,你可以直接使用 SQL 或多种其他脚本语言在数据库中编写业务逻辑。

For some function examples, you can refer to the forum example SQL schema.

如需查看更多函数示例,可以参考论坛示例项目的 SQL schema。

Understanding function performance理解函数性能​

Well written business logic in the database will often be significantly more performant then writing the business logic in the application layer. This is because PostgreSQL is finely tuned for data intensive uses (with statistics and indexes to guide it) and because the round-trip time is minimized. However, it's very easy to write poorly performing code in any environment if you're not familiar with how that environment will execute your logic, and the database is no exception to this.

编写得当的数据库业务逻辑,其性能往往远胜于应用层实现的同等逻辑。这得益于 PostgreSQL 针对数据密集型场景所做的深度调优(借助统计信息与索引指引执行计划),同时也最大限度减少了网络往返耗时。然而,在任何运行环境中,只要不熟悉其底层执行机制,都很容易写出低效代码——数据库也不例外。

Take care to adapt your coding pattern to the new paradigm-shift注意转变编码习惯,适应数据库编程范式

It’s very easy to have very poor performance in the database if you try and move your procedural programming language knowledge over without making accommodations for the new paradigm you’re in. In PostgreSQL, every function call and every statement executed has a performance overhead, so if you don’t adapt your coding patterns to be optimal for a database, you will likely get poor performance. This poor performance from poorly written database functions is one of the reasons some critics claim that business logic in the database should not be used.

如果直接套用过程式编程语言的经验而不针对数据库范式做出调整,在数据库中极易写出性能极其糟糕的代码。在 PostgreSQL 中,每次函数调用和每条语句的执行都会带来固定开销;若不按数据库最优方式调整写法,性能往往会大打折扣。许多批评者主张“不应把业务逻辑放进数据库”,其主要原因之一正是由于拙劣的数据库函数导致了性能问题。

Avoid looping避免使用循环​

Procedural language programmers (people who write JS/TS, C, Java, Ruby, Python, PHP, ...) will likely be tempted to use the FOR, FOREACH and LOOP constructs to manipulate rows, since that’s what they would do in their procedural code. But doing so might result in PostgreSQL having to perform hundreds, thousands, or even millions of individual SQL queries and function calls under the hood of your function.

习惯使用过程式语言(如 JS/TS、C、Java、Ruby、Python、PHP 等)的开发者,往往会忍不住用 FOR、FOREACH 和 LOOP 等循环结构来逐行处理数据,因为这正是过程式代码的常规写法。但在数据库函数中这样做,可能会导致 PostgreSQL 在底层执行成百上千、甚至数百万次单独的 SQL 查询和函数调用。

In PostgreSQL you should almost never use these looping constructs, instead you should perform set operations where possible.

在 PostgreSQL 中,你几乎永远不应该使用这类循环结构,而应尽量改用集合操作(set operations)。

Example: archiving forums示例:归档论坛​

Imagine you want a function capable of archiving a number of forums, and thus set the is_archived for each post in that forum. You might do it like this:

假设你需要编写一个函数来归档多个论坛,同时更新这些论坛下每篇帖子的 is_archived 字段。你可能会这样写:

BAD! Don't do this!
create function archive_forums(forum_ids int[]) returns void as $$
declare
target_forum_id int;
begin
/* BAD: looping should be avoided! */
foreach target_forum_id in array forum_ids loop
update forums set is_archived = true where id = target_forum_id;
update posts set is_archived = true where forum_id = target_forum_id;
end loop;
end;
$$ language plpgsql volatile;

If you feed in 100 values, then this would turn into executing 200 SQL queries.

如果传入 100 个 ID,这个函数在执行时就会变成发起 200 条独立的 SQL 查询。

A better approach would be to do bulk updates:

更合理的做法是改用批量更新:

More efficient
create function archive_forums(forum_ids int[]) returns void as $$
update forums set is_archived = true where id = ANY(forum_ids);
update posts set is_archived = true where forum_id = ANY(forum_ids);
$$ language sql volatile;

If the mutation you made to a later table is dependent on values in an earlier table then you could do it all in a single statement using a common table expression:

如果后续表的变更依赖前一张表的更新结果,还可以使用公用表表达式(CTE),将所有操作合并到一条语句中完成:

Single query example
create function archive_forums(forum_ids int[]) returns void as $$
with updated_forums as (
update forums
set is_archived = true
where id = ANY(forum_ids)
returning id
)
update posts
set is_archived = true
from updated_forums
where posts.forum_id = updated_forums.id;
$$ language sql volatile;

Function inlining (or lack thereof!)函数内联(以及无法内联的情况)​

From the PostgreSQL planner point of view, most functions (see exceptions below) are a "black box" in terms of optimization: Postgres is forced to call each function for each set of parameters and execute them separately from the parent query — it can't turn the function body into a subquery or rewrite it as a join, it can't push "order by" or "where" clauses down into the function body, etc. If you were to apply an "order by" to such a "black box" function, Postgres must first compute the entire results of the function, store them into temporary storage (which may or may not result in writing them to disk) and then apply ordering to this result set.

从 PostgreSQL 查询规划器的视角来看,在优化层面,大多数函数(例外情况见下文)都相当于一个“黑盒”:Postgres 不得不针对每组参数分别调用函数,并将其与外层父查询分开执行——无法把函数体改写为子查询或 join,也无法将 ORDER BY 或 WHERE 子句下推到函数体内部等。如果你对这样一个“黑盒”函数应用 ORDER BY 排序,Postgres 必须先完整计算出函数的全部结果,将其存入临时存储(可能会落盘写入磁盘),然后再对这个结果集进行排序。

When functions are written by developers unfamiliar with how PostgreSQL function execution works, this can result in incredibly poor performance, and often leads to the mantra of "don't put business logic in the database" — but this mantra is misguided. If you embrace the declarative nature of the database you can typically create business logic that is much more performant than the equivalent in the application layer, and enables you to reach a much larger scale before you have to worry about managing your own caches (and cache invalidation!) manually.

如果开发者不了解 PostgreSQL 函数的执行机制,写出的函数性能就会惨不忍睹,这也常常让人得出“不要把业务逻辑放进数据库”的教条结论——但这种观点其实有失偏颇。只要顺应数据库的声明式特性,你在数据库中实现的业务逻辑通常能比应用层的同等实现高效得多,并且能够支撑大得多的业务规模,让你不必过早操心手动维护缓存(以及缓存失效)的问题。

Not all functions are a "black box" from a PostgreSQL optimization point of view — if you're careful with how you write a function, you can make it "inlineable" such that PostgreSQL will insert the body of the function into the query and forget that it even came from a function in the first place. To see a rough set of rules that must hold for your function to be inlined, please see Inlining of SQL functions in the PostgreSQL wiki; but the most important part is it must be LANGUAGE sql; functions written in other languages, including plpgsql, can never be inlined.

不过,在 PostgreSQL 优化器眼中并非所有函数都是“黑盒”——只要编写方式得当,就可以让函数变为“可内联的(inlineable)”。此时 PostgreSQL 会直接把函数体嵌入外层查询,仿佛它原本就是主查询的一部分。有关函数实现内联所需满足的大致条件,请参阅 PostgreSQL Wiki 上的 Inlining of SQL functions;其中最关键的一条是必须声明为 LANGUAGE sql——使用其他任何语言(包括 plpgsql)编写的函数都绝对无法内联。

Move expensive "presentational" functions to plugins将高开销的“展示型”函数迁移至插件

Functions in the database can be generally split into two categories: business logic (typically security-critical functionality e.g. RLS policies, custom mutations, etc) and presentational (e.g. exposing interfaces for search, summarizing data, and other concerns that aren't security-critical).

数据库中的函数通常可分为两类:业务逻辑类(一般涉及关键的安全性控制,如 RLS 策略、自定义 mutation 等)和展示型(如提供搜索接口、数据汇总以及其他非安全敏感的逻辑)。

Presentational logic that is expensive to build in the database can typically be moved to an extendSchema() plugin (or similar) in order to massively improve performance. Plugins inject SQL directly into the query, and since the SQL is explicitly in the query issued to the database, PostgreSQL can do its normal query rewriting and plan optimization against it. As such, from the PostgreSQL's planner perspective, SQL added by plugins is an open book, ripe for optimization.

对于在数据库中构建开销较高的展示型逻辑,通常可以迁移到 extendSchema() 插件(或同类插件)中,从而大幅提升性能。插件会将 SQL 直接注入最终发送给数据库的查询语句中;由于这些 SQL 明明白白地出现在主查询里,PostgreSQL 就能照常对其执行查询重写与执行计划优化。也就是说,在 PostgreSQL 规划器看来,插件注入的 SQL 完全是透明的,可以充分施展各种优化手段。

When you're hitting performance issues with SQL functions, and in particular with "computed column" SQL functions, your best bet is typically to move them from database functions into schema plugins — see the example below.

当你在使用 SQL 函数(尤其是“计算列” SQL 函数)时遇到性能瓶颈,最稳妥的做法通常是把它们从数据库函数迁移到 schema 插件中——具体请看下面的示例。

Example: row-level security function示例:行级安全(RLS)函数​

DO NOT PASS ROW DATA TO RLS FUNCTIONS!

切勿向 RLS 函数传入行数据!

A common mistake that we see people make is passing data from rows as input to functions called in RLS policies. For example:

我们常见的一个错误,是在 RLS 策略中调用函数时,把当前行的数据作为参数传给该函数。例如:

BAD! Uses row data in function call
create function current_user_is_member_of(target_organization_id int) returns boolean as $$
select exists(
select 1
from members
where organization_id = target_organization_id
and user_id = current_user_id()
);
$$ language sql stable security definer;

create policy select_members
for select on members
using (
-- DO NOT DO THIS!!
current_user_is_member_of(members.organization_id)
);

This function is security definer so it cannot be inlined; thus each new value for members.organization_id requires the current_user_is_member_of function to be executed independently, meaning that (depending on the other filters used in the query) we may be calling the current_user_is_member_of function for every unique organization_id in the database. And that’s the best case, at worst PostgreSQL may invoke the overhead of the function call for every row. Ouch!

由于该函数声明了 security definer,它无法被内联;因此每遇到一个不同的 members.organization_id 值,都必须独立执行一次 current_user_is_member_of 函数。这意味着(取决于查询中的其他过滤条件)数据库里有多少个不同的 organization_id,该函数就可能被调用多少次。而这还只是最理想的情况——在最坏情况下,PostgreSQL 甚至可能对表中的每一行都触发一次函数调用开销,性能将急剧下降!

Instead, we should ensure that functions called in RLS policies never accept a row value as argument. We can do this by restructuring the logic:

正确的做法是确保 RLS 策略中调用的函数绝不接收行字段值作为参数。我们可以这样重构上述逻辑:

Much better - only a single function call
create function current_user_organization_ids() returns setof int as $$
select organization_id
from members
where user_id = current_user_id();
$$ language sql stable security definer;

create policy select_members
for select on members
using (
organization_id in (select current_user_organization_ids())
);

Since the function accepts no arguments (or only constant arguments), PostgreSQL can call the function once at the start and reuse the result without recomputing. When applying the RLS policy, the organization_id restriction can be accomplished with excellent performance using a database index. This can be literally thousands of times faster than the previous example.

由于新函数不接收任何参数(或者只接收常量参数),PostgreSQL 可以在查询开始时只调用一次函数,并在后续直接复用结果而无需重复计算。应用 RLS 策略时,organization_id 的过滤还能充分利用数据库索引实现极高的检索效率。这种写法往往比前一个示例快上成百上千倍。

You might think that this is likely to be less performant when you’re fetching individual rows, but as a general rule of thumb PostgreSQL can read millions of rows per second (and even more if it just needs index values), so determining the list of organization_ids a user is a member of is so trivial you can almost ignore it.

你可能会觉得在只读取单行记录时这样做反而会变慢,但根据经验,PostgreSQL 每秒可以读取数百万行数据(如果只需读取索引值则更快),因此查出用户所属的 organization_id 列表几乎不费吹灰之力,这点开销完全可以忽略不计。

Example: computed column function示例:计算列函数​

If your SQL function can be inlined as discussed above, it's typically fine to use it as a computed column function.

如前所述,如果你的 SQL 函数可以被内联,将其用作计算列函数通常没有问题。

However, for functions that cannot be inlined, the "computed column function" pattern can result in very poor performance. If you notice such a function is problematic in your queries, you can move it into the application layer such that the SQL is injected directly into the query, allowing PostgreSQL to optimize it. This is particularly useful if you need to dynamically construct the SQL to be executed. This can be done without a breaking change to the GraphQL schema.

但对于无法内联的函数,采用“计算列函数”的写法可能会造成严重的性能问题。如果发现某个函数拖慢了查询速度,你可以将它的逻辑上移到应用层,使 SQL 直接注入主查询中,从而让 PostgreSQL 能够进行优化。当你需要动态拼接待执行的 SQL 时,这一做法尤其有用,而且完全不会对 GraphQL schema 造成破坏性变更。

Here's a very simple example:

下面来看一个非常简单的例子:

BAD! Cannot be inlined!
create function users_filtered_things(u users, include_archived boolean)
returns setof things as $$
declare
sql text;
begin
-- Dynamically construct our SQL
sql := format(
'select t.* from things t where t.user_id = $1%s',
case when include_archived then '' else ' and archived_at is null' end
);

-- Execute the SQL
return query execute sql using u.id;
end;
$$ language plpgsql stable;
comment on function users_filtered_things is E'@behavior +filter';

PostgreSQL can't inline the above (since it uses plpgsql) so it can end up very expensive.

由于上面的函数使用了 plpgsql,PostgreSQL 无法将其内联,因此实际执行开销可能会非常高。

However, we can do the same work in an extendSchema() plugin, and the resulting SQL will be fully inlined and optimized with identical GraphQL schema produced:

而如果改用 extendSchema() 插件来实现相同的逻辑,不仅生成的 GraphQL schema 完全一致,最终生成的 SQL 也能被彻底内联并得到充分优化:

Plugins inline even dynamically generated SQL
export const MyPlugin = extendSchema((build) => {
const {
grafast: { lambda },
pgResources: { things },
} = build;
return {
typeDefs: /* GraphQL */ `
extend type User {
filteredThings(includeArchived: Boolean): ThingsConnection!
}
`,
objects: {
User: {
plans: {
filteredThings($user, { $includeArchived }) {
// Create a pgSelect step representing all things owned by this user:
const $things = things.find({ user_id: $user.get("id") });

// Apply dynamic changes to the query based on runtime values:
$things.apply(
lambda(
$includeArchived /* ← plan-time step */,
(includeArchived /* ← runtime value */) =>
// At runtime, this callback will be called to manipulate the
// SQL query before it is executed
(queryBuilder) => {
if (!includeArchived) {
queryBuilder.where((sql) => sql`archived_at is null`);
}
},
),
);

// Wrap this collection in a connection
return connection($things);
},
},
},
},
};
});
PostGraphile will add the connection arguments automaticallyPostGraphile 会自动添加 Connection 参数

Since this example adds a field returning ThingsConnection, PostGraphile will automatically add the first/last/before/after/etc arguments. You can disable this through use of scope:

由于此示例添加的字段返回 ThingsConnection,PostGraphile 会自动为其补充 first/last/before/after 等分页参数。你可以通过配置 scope 来禁用这一行为:

plans: {
filteredThings: {
scope: {
// Disable the connection-related arguments being auto-added
isPgFieldConnection: false,
},
plan($user, { $includeArchived }) {
// ...

Procedural Languages过程语言​

Functions in PostgreSQL require you to use either SQL or a procedural language. The most common procedural language in PostgreSQL is PL/pgSQL.

在 PostgreSQL 中编写函数,必须使用 SQL 或某种过程语言。其中最常用的过程语言是 PL/pgSQL。

SQL is probably the easiest to use language for functions as you are most likely already familiar with it. Because it lacks the looping constructs, it also encourages you to write your functions in a performant way. We advise that if your function can be written in SQL, it probably should be.

SQL 很可能是最容易上手的函数语言,因为大多数开发者早已对它十分熟悉。同时,由于 SQL 本身缺乏循环结构,它也会促使你以高性能的方式编写函数。我们的建议是:只要某个函数可以用 SQL 实现,通常就应该优先用 SQL 来写。

PL/pgSQL is PostgreSQL’s custom procedural language, it’s fairly easy to pick up and there are plenty of StackOverflow and other resources for this language. You’ll need to learn PL/pgSQL (or one of the other procedural languages) if you want to write any triggers, because SQL can’t be used for triggers. Don’t worry — you can definitely make awesome applications without a deep knowledge of PL/pgSQL.

PL/pgSQL 是 PostgreSQL 专属的过程语言,相当容易上手,StackOverflow 等平台上也有丰富的学习资源。如果你想编写触发器,就需要掌握 PL/pgSQL(或其他过程语言),因为纯 SQL 无法用于编写触发器。不过不用担心——即使对 PL/pgSQL 了解不深,你也完全可以做出非常优秀的应用。

A simple function written with LANGUAGE sql looks like this:

下面是一个使用 LANGUAGE sql 编写的简单函数示例:

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

The same function with LANGUAGE plpgsql could look like this:

同样的功能如果改用 LANGUAGE plpgsql 编写,则是下面这样:

create function add(a int, b int) returns int as $$
begin
return a + b;
end;
$$ language plpgsql immutable strict;

If you don’t want to use PL/pgSQL or SQL, many popular scripting languages can be used inside PostgreSQL to write your functions! You can see a few of these projects here:

如果你不想使用 PL/pgSQL 或 SQL,也可以直接在 PostgreSQL 内部使用许多主流脚本语言来编写函数!以下是其中几个常见项目:

For example, a function defined using JavaScript could look like:

例如,使用 JavaScript 定义的函数可以写成这样:

-- This does look the exact same as the PL/pgSQL example…
create function add(a int, b int) returns int as $$
return a + b;
$$ language plv8 immutable strict;

-- Here’s a better example from the plv8 repo…
create function plv8_test(keys text[], vals text[]) returns text as $$
var object = {}
for (var i = 0; i < keys.length; i++) {
object[keys[i]] = vals[i]
}
return JSON.stringify(object)
$$ language plv8 immutable strict;

Named Arguments命名参数​

PostgreSQL allows you to mix named and positional (unnamed) arguments in your functions. However, GraphQL will only allow named arguments. So if you don’t name an argument, PostGraphile will give it a name like arg1, arg2, arg3, and so on. An example of a function with unnamed arguments is as follows:

PostgreSQL 允许在函数中混用命名参数和位置(未命名)参数。但 GraphQL 仅支持命名参数;因此,如果你没有为参数指定名称,PostGraphile 会自动为其分配 arg1、arg2、arg3 等默认名称。下面是一个使用未命名参数的函数示例:

create function add(int, int) returns int as $$
select $1 + $2;
$$ language sql immutable strict;

Whereas named arguments look like:

而使用命名参数的写法如下:

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

For the sake of your schema and your coworkers, you should always use named arguments.

为了保持 schema 清晰易读、方便团队协作,请务必始终使用命名参数。

Solving naming conflicts解决命名冲突​

Sometimes the names you have chosen for your function parameters will conflict with names of columns or other identifiers accessible within your function. You can solve this in a number of ways.

有时,你为函数参数起的名字可能会与函数内部可访问的列名或其他标识符重名。可以通过以下几种方式来解决这一冲突。

Use function name使用函数名限定​

You can use the function’s name to disambiguate a function argument from a column value:

你可以使用函数名作为前缀,以区分函数参数和表列值:

create function get_user(id int) returns users as $$
select * from users where users.id = get_user.id;
$$ language sql stable;

(If you happen to give your function the same name as your table, then you can give the table an alias in the function body query to help disambiguate.)

(如果函数名恰好与表名相同,可以在函数体的查询语句中为表指定别名来消除歧义。)

Numeric arguments数字参数​

An alternative approach is to use numeric arguments such as $1 for the first argument, $2 for the second, and so on, the represent the argument values, and use the table name to disambiguate column values:

另一种方式是使用数字参数(例如用 $1 表示第一个参数、$2 表示第二个参数,依此类推)来引用参数值,同时用表名限定来区分表列:

create function get_user(id int) returns users as $$
select * from users where users.id = $1;
$$ language sql stable;

Variable substitution变量替换​

The two approaches above work well in general, but there are some cases where it is not enough. For example when you have an upsert (INSERT...ON CONFLICT) statement in a plpgsql language function, such as:

上述两种方式在大多数情况下都行之有效,但在某些场景下仍不够用。例如,在 plpgsql 函数中编写 upsert(INSERT...ON CONFLICT)语句时:

create function upsert_value(id int, value text) returns void as $$
begin
insert into my_table (id, value)
values(id, value)
on conflict (id) -- This will error
do update set value = excluded.value;
end;
$$ language plpgsql volatile;

In this case the on conflict (id) causes an issue because PL/pgSQL does not know if id refers to the table column or the function argument, and adding the table name inside the parenthesis is a syntax error.

在这个例子中,on conflict (id) 会引发冲突,因为 PL/pgSQL 无法判断括号内的 id 指的是表列还是函数参数,而在括号内加上表名限定又会导致语法错误。

To solve this, you can change language to sql which will treat columns preferentially. Alternatively you can tell the function to solve conflicts by using the column:

要解决这个问题,你可以将函数语言改为 sql(它会优先解析为表列);或者显式指示函数在遇到冲突时优先使用表列:

create function upsert_value(id int, value text) returns void as $$
#variable_conflict use_column
begin
insert into my_table (id, value)
values(id, value)
on conflict (id)
do update set value = excluded.value;
end;
$$ language plpgsql volatile;

To better understand these conflicts and solutions, refer to the PostgreSQL docs for variable substitution.

若想深入了解此类冲突及解决方案,请参阅 PostgreSQL 官方文档中关于变量替换的说明。

VOLATILE (Mutation) FunctionsVOLATILE(Mutation)函数​

By default, a function is “volatile”. For example, a function defined as:

PostgreSQL 函数的默认易变性级别为“volatile”。例如,下面这样定义的函数:

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

is equivalent to a function defined as:

完全等价于显式声明如下的函数:

create function my_function(a int, b int) returns int as $$
…
$$ language sql volatile;

From the PostgreSQL docs:

引自 PostgreSQL 官方文档:

VOLATILE indicates that the function value can change even within a single table scan, so no optimizations can be made… But note that any function that has side-effects must be classified volatile, even if its result is quite predictable, to prevent calls from being optimized away; an example is setval().

VOLATILE 表示函数值即使在单次表扫描期间也可能发生变化,因此不能对其做任何优化……但请注意,凡是带有副作用的函数,即便其返回值完全可预测,也必须归类为 volatile,以防函数调用被优化器省略;setval() 就是一个典型例子。

In simpler terms VOLATILE basically means you are changing data or state.

简而言之,VOLATILE 基本意味着该函数会修改数据或改变状态。

Anyone familiar with HTTP could compare a VOLATILE function to “unsafe” HTTP methods like POST, PUT, PATCH, and DELETE.

熟悉 HTTP 协议的开发者,可以将 VOLATILE 函数类比为 POST、PUT、PATCH、DELETE 这类“非安全(unsafe)”HTTP 方法。

Certain VOLATILE functions will be exposed by PostGraphile as custom mutations.

符合条件的 VOLATILE 函数会被 PostGraphile 暴露为自定义 mutation。

STABLE/IMMUTABLE (Query) FunctionsSTABLE/IMMUTABLE(Query)函数​

If your function does not modify any data or state, you should declare it as STABLE.

如果你的函数不会修改任何数据或状态,应当将其声明为 STABLE。

When to use IMMUTABLE何时使用 IMMUTABLE

If your function depends only on its arguments and does not fetch data from other sources, nor is it dependent on configuration variables, then you can declare it as IMMUTABLE which is a stricter form of STABLE.

如果函数仅依赖其传入参数,既不从其他数据源读取数据,也不依赖任何配置变量,那么你可以将其声明为 IMMUTABLE——它是比 STABLE 更严格的级别。

We advise that you don’t use IMMUTABLE until you become expert — stick to STABLE.

在成为专家之前,我们建议不要轻易使用 IMMUTABLE——坚持用 STABLE 即可。

By marking your function as STABLE or IMMUTABLE, PostgreSQL knows that it can apply a number of optimizations, including memoization to avoiding calling it multiple times for the same inputs during the same statement. Memoization is a possible choice PostgreSQL may make, it is not guaranteed.

将函数标记为 STABLE 或 IMMUTABLE 后,PostgreSQL 就知道可以对其应用多种优化策略,包括通过记忆化(memoization)缓存结果,避免在同一条语句执行期间针对相同输入重复调用函数。不过,记忆化只是 PostgreSQL 可能采取的优化手段之一,并非绝对保证发生。

Here’s examples of defining functions as STABLE/IMMUTABLE:

以下是将函数定义为 STABLE 或 IMMUTABLE 的几个示例:

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

-- or…

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

-- or if you wanted to return a row from a table…

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

From the PostgreSQL docs:

引自 PostgreSQL 官方文档:

IMMUTABLE indicates that the function cannot modify the database and always returns the same result when given the same argument values; that is, it does not do database lookups or otherwise use information not directly present in its argument list. If this option is given, any call of the function with all-constant arguments can be immediately replaced with the function value.

IMMUTABLE 表示函数无法修改数据库,且在给定相同参数值时总是返回相同结果;也就是说,它不会执行数据库查询,也不会使用未直接出现在参数列表中的任何信息。一旦指定该选项,任何全常量参数的函数调用都可以立即被替换为计算后的函数值。

and…

以及……

STABLE indicates that the function cannot modify the database, and that within a single table scan it will consistently return the same result for the same argument values, but that its result could change across SQL statements. This is the appropriate selection for functions whose results depend on database lookups, parameter variables (such as the current time zone), etc. (It is inappropriate for AFTER triggers that wish to query rows modified by the current command).

STABLE 表示函数无法修改数据库,且在单次表扫描内对于相同的参数值始终返回相同结果,但跨不同 SQL 语句时其结果可能会发生变化。对于依赖数据库查询或参数变量(如当前时区)等信息的函数,这正是恰当的选择(但不适用于希望查询当前命令所修改行的 AFTER 触发器)。

To use the HTTP analogy again, IMMUTABLE and STABLE are comparable to “safe” HTTP methods like GET and HEAD.

再次借用 HTTP 类比,IMMUTABLE 和 STABLE 相当于 GET 和 HEAD 这类“安全(safe)”HTTP 方法。

Certain STABLE/IMMUTABLE functions will be exposed by PostGraphile as custom queries or computed columns.

符合条件的 STABLE/IMMUTABLE 函数会被 PostGraphile 暴露为自定义 Query或计算列。

SETOF Functions — ConnectionsSETOF 函数 —— Connection​

As well as scalars, compound types, and arrays of these, PostgreSQL functions can also return sets. Sets emulate tables, and so it’s natural for PostGraphile to expose these to GraphQL using connections (or, if your behaviors are configured to prefer lists, as lists).

除了标量、复合类型及其数组之外,PostgreSQL 函数还可以返回集合(set)。由于集合在行为上相当于数据表,PostGraphile 自然会将其作为 Connection 暴露到 GraphQL 中(如果 behavior 配置为优先使用列表,则会暴露为列表)。

SETOF functions can be a powerful way of exposing data to the user that may be too much for them to handle all at once and require pagination.

当需要返回的数据量过大、用户难以一次性处理而需要分页加载时,SETOF 函数是一种非常强大的数据暴露方式。

To create a function that returns a connection, you could use SQL such as this:

要创建一个返回 Connection 的函数,可以使用如下 SQL:

-- Assuming we already have a table named `person`…
create function my_function(a int, b int) returns setof person as $$
…
$$ language sql stable;

This function would be recognized as a custom query, and could be queried like this:

该函数会被识别为自定义 Query,在 GraphQL 中可以这样查询:

{
myFunction(a: 1, b: 2, first: 2) {
pageInfo {
hasNextPage
hasPrevPage
}
edges {
cursor
node {
id
}
}
}
}

More information on constructing advanced queries can be found here.

关于构建高级查询的更多信息,可参阅自定义 Query 文档。


The original (pre-2018?) version of this article was written by Caleb Meredith; thanks Caleb! It has been heavily edited and expanded since.

本文最早的(2018 年前?)版本由 Caleb Meredith 撰写,感谢 Caleb!此后经过了大幅修订与扩充。