Computed Columns
"Computed columns" add what appears to be an extra column (field) to the GraphQL
table type, but, unlike an actual column, the value for this field is the result
of calling a function defined in the PostgreSQL schema. This function will
automatically be exposed to the resultant GraphQL schema as a field on the type;
it can accept arguments that influence its result, and may return either a
scalar, record, list or a set. Sets (denoted by RETURNS SETOF ...) are exposed
as connections or lists, depending on the behavior configuration.
“计算列(Computed Columns)”会为 GraphQL 中的表类型添加一个看似额外的列(字段),但与真实的数据表列不同,该字段的值是通过调用定义在 PostgreSQL schema 中的函数计算得出的。PostGraphile 会自动将此函数暴露为对应 GraphQL 类型上的一个字段;它可以接收影响计算结果的参数,并可返回标量、复合记录(record)、列表或集合。其中,集合类型(由 RETURNS SETOF ... 声明)会根据 behavior 配置暴露为 Connection 或列表。
We inline these function calls into the original SELECT statement for
efficiency, so no additional SQL queries need to be issued to the database.
That said, SQL function calls can have a performance overhead, which can build
up if you’re doing this on thousands of rows. PostgreSQL can sometimes inline
your SQL functions
for great performance, but if this fails and you’re seeing performance issues
you might want to investigate using
extendSchema instead - see the related note in
Understanding function performance.
为提高效率,PostGraphile 会将这些函数调用直接内联到原始的 SELECT 语句中,因此无需向数据库发起额外的 SQL 查询。不过,SQL 函数调用本身存在一定开销,若一次性处理数千行数据,累积开销可能较为显著。PostgreSQL 有时能够内联 SQL 函数以获得极佳性能;但如果无法内联且遇到了性能瓶颈,建议考虑改用 extendSchema 来实现——详情请参阅理解函数性能中的相关说明。
To create a function that PostGraphile will recognize as a computed column, it must obey the following rules:
要让 PostGraphile 将函数识别为计算列,该函数必须满足以下规则:
-
name must begin with the name of the table it applies to, followed by an underscore (
_)函数名必须以目标表名及下划线(
_)开头 -
first parameter must be the table type
第一个参数必须是该表对应的行类型
-
must NOT return
VOID返回值不能为
VOID -
must be marked as
STABLE(orIMMUTABLE, though that tends to be less common)必须标记为
STABLE(或IMMUTABLE,不过后者较为少见) -
must be defined in the same PostgreSQL schema as the table
必须与目标表定义在同一个 PostgreSQL schema 中
For example, assuming a table called person exists, the function:
例如,假设已存在一张名为 person 的表,下面的函数:
create function person_full_name(person person) returns text as $$
select person.given_name || ' ' || person.family_name
$$ language sql stable;
Will create a computed column for your table named person, which can be
queried like this:
就会为 person 表创建一个计算列,可以像这样进行查询:
{
personById(id: …) {
# nodeId, id, ...
fullName # A computed column, but the client doesn’t even know!
}
}
PostgreSQL's documentation on function calls notes:
PostgreSQL 关于函数调用的官方文档指出:
A function that takes a single argument of composite type can optionally be called using field-selection syntax, and conversely field selection can be written in functional style. That is, the notations
col(table)andtable.colare interchangeable. This behavior is not SQL-standard but is provided in PostgreSQL because it allows use of functions to emulate “computed fields”.接收单个复合类型参数的函数,既可以使用字段选择语法来调用,反之,字段选择也可以写成函数调用的形式。也就是说,
col(table)与table.col这两种写法可以互换。这一特性并非 SQL 标准,而是 PostgreSQL 特意提供的,用于通过函数模拟“计算字段(computed fields)”。-- https://www.postgresql.org/docs/17/sql-expressions.html, emphasis added.
-- https://www.postgresql.org/docs/17/sql-expressions.html,重点部分为引者所加。
Thus to query this computed column yourself outside of PostGraphile you could execute:
因此,如果你想在 PostGraphile 之外手动查询这个计算列,可以执行如下 SQL:
select
person.id,
person.person_full_name as full_name
-- or, equivalently:
-- person_full_name(person) as full_name
from person
where id = $1;
This person.person_full_name syntax makes the function person_full_name
appear as if it were a column of person even though no such column exists,
hence our name for it: computed column function.
这种 person.person_full_name 写法让 person_full_name 函数看起来就像是 person 表的一个列(尽管表里实际上并不存在该列),我们将其称为“计算列函数”。
If your function, for example person_favorite_posts(person), returns a set
then PostGraphile will automatically wrap this selection in a subquery
aggregation for you to prevent the parent query from yielding more rows than
expected; if you wanted to query it yourself it might look something like:
如果你的函数(例如 person_favorite_posts(person))返回一个集合,PostGraphile 会自动将这次查询包裹在子查询聚合中,以免父查询返回超出预期的行数。如果你想自己编写等效的 SQL,大致如下:
select
person.id,
array(
select posts.*
from person_favorite_posts(person) posts
) as favorite_posts
from person
where id = $1;
Example示例
This example creates two computed columns, one returning a simple varchar and
the other a connection. Note that || in PostgreSQL is string concatenation.
本示例创建了两个计算列,一个返回简单的 varchar 字符串,另一个返回 Connection。注意,在 PostgreSQL 中,|| 是字符串拼接运算符。
create table my_schema.users (
id serial not null primary key,
first_name varchar not null,
last_name varchar not null
);
create table my_schema.friendships (
user_id integer not null,
target_id integer not null,
primary key (user_id, target_id)
);
-- Creates `User.name` string field
create function my_schema.users_name(u my_schema.users)
returns varchar as $$
select u.first_name || ' ' || u.last_name;
$$ language sql stable;
-- Creates `User.friends` connection
create function my_schema.users_friends(u my_schema.users)
returns setof my_schema.users as $$
select users.*
from my_schema.users
inner join my_schema.friendships
on (friendships.target_id = users.id)
where friendships.user_id = u.id;
$$ language sql stable;
You can add parameters to your computed column field by declaring additional parameters in your PostgreSQL function:
只需在 PostgreSQL 函数中声明额外的参数,即可为对应的计算列字段添加参数:
-- Creates `User.greet(greeting: String)` string field
create function my_schema.users_greet(
u my_schema.users, --- required table type parameter, unexposed
greeting text --- additional parameter, will be exposed
) returns text as $$
select greeting || ', ' || u.first_name || ' ' || u.last_name || '!';
$$ language sql stable strict;
which can be queried like:
随后便可以这样查询:
{
userById(id: …) {
greet(greeting: "Greetings and salutations")
}
}
Advice使用建议
See the advice in the Custom Queries article.
请参阅自定义查询文档中的建议。
Debugging computed columns调试计算列
If a computed column is missing or behaving unexpectedly, check the following:
如果某个计算列未出现在 schema 中或行为不符合预期,请检查以下几点:
-
the function name starts with the table name and an underscore (for example
person_full_namefor apersontable, orperson_full_namefor apersonstable)函数名是否以表名及下划线开头(例如
person表对应person_full_name,或persons表对应person_full_name) -
the first argument is the table type (for example
person_full_name(p person))第一个参数是否为该表的行类型(例如
person_full_name(p person)) -
the function is marked
stableorimmutable(volatileis the default, which implies a mutation)函数是否标记为
stable或immutable(默认值为volatile,表示会修改数据,即 mutation) -
the function is defined in the same schema as the table (
create function same_schema_here.person_full_name(p same_schema_here.person))函数是否与表定义在同一个 schema 中(
create function same_schema_here.person_full_name(p same_schema_here.person)) -
the function runs correctly in SQL using
table.functionorfunction(table)syntax (select person_full_name(person) from person)函数在 SQL 中使用
table.function或function(table)语法能否正常执行(select person_full_name(person) from person)