数据库函数

Postgres内置了对SQL函数的支持。 这些函数存在于你的数据库中，它们可以与API一起使用。

快速演示 link

开始使用 link

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

1. 进入 SQL编辑器栏。
2. 点击 新查询。
3. 输入创建或替换数据库功函数的SQL。
4. 点击 运行或cmd+enter (ctrl+enter)。

简单的函数 link

让我们创建一个基本的数据库函数，返回一个字符串 hello world.

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

函数创建后，我们有几种 “执行 “函数的方法–可以直接在数据库中使用SQL，也可以使用其中一个客户端库。

  select hello_world();
  

  const { data, error } = await supabase.rpc('hello_world')
  

  final res = await supabase
  .rpc('hello_world')
  .execute();
  

返回数据集 link

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

例如，如果我们有一个数据库，里面有一些星球大战的数据。

  create table planets (
  id serial primary key,
  name text
);

insert into planets (id, name)
values
  (1, 'Tattoine'),
  (2, 'Alderaan'),
  (3, 'Kashyyyk');

create table people (
  id serial primary key,
  name text,
  planet_id bigint references planets
);

insert into people (id, name, planet_id)
values
  (1, 'Anakin Skywalker', 1),
  (2, 'Luke Skywalker', 1),
  (3, 'Princess Leia', 2),
  (4, 'Chewbacca', 3);
  

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

  create or replace function get_planets()
returns setof planets
language sql
as $$
  select * from planets;
$$;
  

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

  select *
from get_planets()
where id = 1;
  

  const { data, error } = supabase.rpc('get_planets').eq('id', 1)
  

  final res = await supabase
  .rpc('get_planets')
  .eq('id', 1)
  .execute();
  

传递参数 link

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

  create or replace function add_planet(name text)
returns bigint
language plpgsql
as $$
declare
  new_row bigint;
begin
  insert into planets(name)
  values (add_planet.name)
  returning id into new_row;

  return new_row;
end;
$$;
  

再一次，你可以在数据库中使用select查询来执行这个函数，或者使用客户端库。

  select * from add_planet('Jakku');
  

  const { data, error } = await supabase.rpc('add_planet', { name: 'Jakku' })
  

  final res = await supabase
  .rpc('add_planet', params: { 'name': 'Jakku' })
  .execute();
  

建议 link

数据库函数vs边缘函数 link

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

对于需要低延迟的使用情况，使用边缘函数，它是全局分布的，可以用Typescript编写。

安全性 definervs invoker link

Postgres允许你指定是作为调用函数的用户（invoker），还是作为函数的创建者（definer）来执行函数。比如说:

  create function hello_world()
returns text
language plpgsql
security definer set search_path = public
as $$
begin
  select 'hello world';
end;
$$;
  

最好的做法是使用security invoker（这也是默认的）。如果你使用security definer，你必须设置search_path。 这限制了潜在的损害，如果你允许访问执行该函数的用户不应该有的模式。

函数的权限 link

默认情况下，数据库函数可以由任何角色执行。你可以通过改变默认权限来限制这一点，然后选择哪些角色可以执行功能。

  ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;

-- Choose which roles can execute functions
GRANT EXECUTE ON FUNCTION hello_world TO authenticated;
GRANT EXECUTE ON FUNCTION hello_world TO service_role;
  

资源 link

• 官方客户端库: JavaScript and Flutter
• 社区客户端库: github.com/supabase-community
• PostgreSQL官方文档: 第9章 函数和运算符
• PostgreSQL参考资料: 创建函数

深度挖掘 link

创建数据库函数 link

使用JavaScript调用数据库函数 link

使用数据库函数来调用外部API link