数据库功能

Postgres 内置支持 SQL 函数。这些函数存在于你的数据库中，并且可以通过 API 使用。

🌐 Postgres has built-in support for SQL functions. These functions live inside your database, and they can be used with the API.

快速演示 #

🌐 Quick demo

入门 #

🌐 Getting started

Supabase 提供多种创建数据库函数的选项。你可以使用仪表板，也可以直接使用 SQL 创建它们。我们在仪表板中提供了一个 SQL 编辑器，或者你也可以连接到你的数据库，自行运行 SQL 查询。

🌐 Supabase provides several options for creating database functions. You can use the Dashboard or create them directly using SQL. We provide a SQL editor within the Dashboard, or you can connect to your database and run the SQL queries yourself.

去“SQL 编辑器”部分。
点击“新建查询”。
输入 SQL 来创建或替换你的数据库函数。
点击“运行”或按 cmd+回车（ctrl+回车）。

基本功能 #

🌐 Basic functions [#simple-functions]

创建一个基本的数据库函数，返回字符串“hello world”。

🌐 Create a basic database function that returns the string "hello world".

1
create or replace function hello_world() -- 1
2
returns text -- 2
3
language sql -- 3
4
as $$  -- 4
5
  select 'hello world';  -- 5
6
$$; --6

显示/隐藏详情

从最基本的角度来看，一个函数有以下几个部分：

🌐 At it's most basic a function has the following parts:

create or replace function hello_world()：函数声明，其中 hello_world 是函数的名称。创建新函数时可以使用 create，替换现有函数时可以使用 replace。或者你也可以一起使用 create or replace 来处理任意一种情况。
returns text：函数返回的数据类型。如果它什么都不返回，你可以 returns void。
language sql：函数体内部使用的语言。这也可以是过程型语言：plpgsql、plpython 等。
as $$：函数封装。任何被 $$ 符号包围的内容都会成为函数体的一部分。
select 'hello world';：一个基本的函数体。如果函数体内的最后一条 select 语句后面没有其他语句，它就会被返回。
$$;：函数封装器的结束符号。

给你的函数命名时，要确保函数名字独一无二，因为不支持函数重载。

🌐 When naming your functions, make the name of the function unique as overloaded functions are not supported.

在函数创建之后，我们有几种“执行”函数的方法——可以直接在数据库中使用 SQL，或者使用某个客户端库。

🌐 After the Function is created, we have several ways of "executing" the function - either directly inside the database using SQL, or with one of the client libraries.

1
select hello_world();

返回数据集 #

🌐 Returning data sets

数据库函数也可以从表或视图返回数据集。

🌐 Database Functions can also return data sets from Tables or Views.

例如，如果我们有一个包含一些《星球大战》数据的数据库：

🌐 For example, if we had a database with some Star Wars data inside:

行星 #

🌐 Planets

1
| id  | name     |
2
| --- | -------- |
3
| 1   | Tatooine |
4
| 2   | Alderaan |
5
| 3   | Kashyyyk |

人们 #

🌐 People

1
| id  | name             | planet_id |
2
| --- | ---------------- | --------- |
3
| 1   | Anakin Skywalker | 1         |
4
| 2   | Luke Skywalker   | 1         |
5
| 3   | Princess Leia    | 2         |
6
| 4   | Chewbacca        | 3         |

我们可以创建一个返回所有行星的函数：

🌐 We could create a function which returns all the planets:

1
create or replace function get_planets()
2
returns setof planets
3
language sql
4
as $$
5
  select * from planets;
6
$$;

因为这个函数返回一个表集合，我们也可以应用过滤器和选择器。例如，如果我们只想要第一个行星：

🌐 Because this function returns a table set, we can also apply filters and selectors. For example, if we only wanted the first planet:

1
select *
2
from get_planets()
3
where id = 1;

传递参数 #

🌐 Passing parameters

创建一个函数，将一个新行星插入到 planets 表中，并返回新的 ID。注意这次我们使用的是 plpgsql 语言。

🌐 Create a function to insert a new planet into the planets table and return the new ID. Note that this time we're using the plpgsql language.

1
create or replace function add_planet(name text)
2
returns bigint
3
language plpgsql
4
as $$
5
declare
6
  new_row bigint;
7
begin
8
  insert into planets(name)
9
  values (add_planet.name)
10
  returning id into new_row;
11
12
  return new_row;
13
end;
14
$$;

你可以再次选择在数据库内部使用 select 查询执行这个函数，或者使用客户端库来执行：

🌐 Once again, you can execute this function either inside your database using a select query, or with the client libraries:

1
select * from add_planet('Jakku');

建议 #

🌐 Suggestions

数据库函数 vs 边缘函数 #

🌐 Database Functions vs Edge Functions

对于数据密集型操作，使用数据库函数，这些函数在你的数据库中执行，并且可以通过 REST 和 GraphQL API 远程调用。

🌐 For data-intensive operations, use Database Functions, which are executed within your database and can be called remotely using the REST and GraphQL API.

对于需要低延迟的使用场景，可以使用 Edge Functions，它们是全球分布的，并且可以使用 Typescript 编写。

🌐 For use-cases which require low-latency, use Edge Functions, which are globally-distributed and can be written in Typescript.

证券 `definer` 对比 `invoker`#

🌐 Security definer vs invoker

Postgres 允许你指定是希望函数以调用该函数的用户（invoker）身份执行，还是以函数的创建者（definer）身份执行。例如：

🌐 Postgres allows you to specify whether you want the function to be executed as the user calling the function (invoker), or as the creator of the function (definer). For example:

1
create function hello_world()
2
returns text
3
language plpgsql
4
security definer set search_path = ''
5
as $$
6
begin
7
  return 'hello world';
8
end;
9
$$;

最佳做法是使用 security invoker（它也是默认值）。如果你使用 security definer，你必须设置 search_path。如果你使用空搜索路径（search_path = ''），你必须在函数体中的每个关系上明确指定模式（例如 from public.table）。如果你允许访问执行函数的用户不应该有权限的模式，这可以限制潜在的损害。

🌐 It is best practice to use security invoker (which is also the default). If you ever use security definer, you must set the search_path. If you use an empty search path (search_path = ''), you must explicitly state the schema for every relation in the function body (e.g. from public.table). This limits the potential damage if you allow access to schemas which the user executing the function should not have.

功能权限 #

🌐 Function privileges

默认情况下，任何角色都可以执行数据库函数。限制这一点主要有两种方法：

🌐 By default, database functions can be executed by any role. There are two main ways to restrict this:

根据具体情况而定。特别撤销你想保护的功能的权限。执行权限需要同时撤销 public 和你要限制的角色：
```
1
revoke execute on function public.hello_world from public;
2
revoke execute on function public.hello_world from anon;
```

默认限制函数执行。只有当你希望某个函数能被特定角色执行时，才特别授予访问权限。

要限制所有现有功能，请撤销 public 和你想限制的角色的执行权限：

1
revoke execute on all functions in schema public from public;
2
revoke execute on all functions in schema public from anon, authenticated;

要限制所有新功能，请更改 public 和你想限制的角色的默认权限：

1
alter default privileges in schema public revoke execute on functions from public;
2
alter default privileges in schema public revoke execute on functions from anon, authenticated;

然后你可以重新授予某个特定功能给某个特定角色的权限：

1
grant execute on function public.hello_world to authenticated;

调试函数 #

🌐 Debugging functions

你可以添加日志来帮助调试函数。这对于复杂的函数尤其推荐。

🌐 You can add logs to help you debug functions. This is especially recommended for complex functions.

值得记录的好目标包括：

🌐 Good targets to log include:

（非敏感）变量的数值
查询返回的结果

常规日志 #

🌐 General logging

要在仪表板的 Postgres 日志中创建自定义日志，你可以使用 raise 关键字。默认情况下，有 3 个观察到的严重性等级：

🌐 To create custom logs in the Dashboard's Postgres Logs, you can use the raise keyword. By default, there are 3 observed severity levels:

log
warning
exception（错误级别）

1
create function logging_example(
2
  log_message text,
3
  warning_message text,
4
  error_message text
5
)
6
returns void
7
language plpgsql
8
as $$
9
begin
10
  raise log 'logging message: %', log_message;
11
  raise warning 'logging warning: %', warning_message;
12
13
  -- immediately ends function and reverts transaction
14
  raise exception 'logging error: %', error_message;
15
end;
16
$$;
17
18
select logging_example('LOGGED MESSAGE', 'WARNING MESSAGE', 'ERROR MESSAGE');

错误处理 #

🌐 Error handling

你可以用 raise exception 关键字创建自定义错误。

🌐 You can create custom errors with the raise exception keywords.

一个常见的模式是在变量不满足条件时抛出错误：

🌐 A common pattern is to throw an error when a variable doesn't meet a condition:

1
create or replace function error_if_null(some_val text)
2
returns text
3
language plpgsql
4
as $$
5
begin
6
  -- error if some_val is null
7
  if some_val is null then
8
    raise exception 'some_val should not be NULL';
9
  end if;
10
  -- return some_val if it is not null
11
  return some_val;
12
end;
13
$$;
14
15
select error_if_null(null);

数值检查很常见，所以 Postgres 提供了一个简写：assert 关键字。它的格式如下：

🌐 Value checking is common, so Postgres provides a shorthand: the assert keyword. It uses the following format:

1
-- throw error when condition is false
2
assert <some condition>, 'message';

下面是一个例子

🌐 Below is an example

1
create function assert_example(name text)
2
returns uuid
3
language plpgsql
4
as $$
5
declare
6
  student_id uuid;
7
begin
8
  -- save a user's id into the user_id variable
9
  select
10
    id into student_id
11
  from attendance_table
12
  where student = name;
13
14
  -- throw an error if the student_id is null
15
  assert student_id is not null, 'assert_example() ERROR: student not found';
16
17
  -- otherwise, return the user's id
18
  return student_id;
19
end;
20
$$;
21
22
select assert_example('Harry Potter');

错误信息也可以使用 exception 关键字来捕获和修改：

🌐 Error messages can also be captured and modified with the exception keyword:

1
create function error_example()
2
returns void
3
language plpgsql
4
as $$
5
begin
6
  -- fails: cannot read from nonexistent table
7
  select * from table_that_does_not_exist;
8
9
  exception
10
      when others then
11
          raise exception 'An error occurred in function <function name>: %', sqlerrm;
12
end;
13
$$;

高级日志记录 #

🌐 Advanced logging

对于更复杂的函数或复杂的调试，试试记录日志：

🌐 For more complex functions or complicated debugging, try logging:

格式化变量
单独的行
函数调用的开始和结束

1
create or replace function advanced_example(num int default 10)
2
returns text
3
language plpgsql
4
as $$
5
declare
6
    var1 int := 20;
7
    var2 text;
8
begin
9
    -- Logging start of function
10
    raise log 'logging start of function call: (%)', (select now());
11
12
    -- Logging a variable from a SELECT query
13
    select
14
      col_1 into var1
15
    from some_table
16
    limit 1;
17
    raise log 'logging a variable (%)', var1;
18
19
    -- It is also possible to avoid using variables, by returning the values of your query to the log
20
    raise log 'logging a query with a single return value(%)', (select col_1 from some_table limit 1);
21
22
    -- If necessary, you can even log an entire row as JSON
23
    raise log 'logging an entire row as JSON (%)', (select to_jsonb(some_table.*) from some_table limit 1);
24
25
    -- When using INSERT or UPDATE, the new value(s) can be returned
26
    -- into a variable.
27
    -- When using DELETE, the deleted value(s) can be returned.
28
    -- All three operations use "RETURNING value(s) INTO variable(s)" syntax
29
    insert into some_table (col_2)
30
    values ('new val')
31
    returning col_2 into var2;
32
33
    raise log 'logging a value from an INSERT (%)', var2;
34
35
    return var1 || ',' || var2;
36
exception
37
    -- Handle exceptions here if needed
38
    when others then
39
        raise exception 'An error occurred in function <advanced_example>: %', sqlerrm;
40
end;
41
$$;
42
43
select advanced_example();

资源 #

🌐 Resources

官方客户端库：JavaScript 和 Flutter
社区客户端库：github.com/supabase-community
Postgres 官方文档：第9章函数和操作符
Postgres 参考：CREATE FUNCTION

深入探讨 #

🌐 Deep dive

创建数据库功能 #

🌐 Create Database Functions

使用 JavaScript 调用数据库函数 #

🌐 Call Database Functions using JavaScript

使用数据库函数调用外部 API #

🌐 Using Database Functions to call an external API