SUPABASE 函数（非边缘）

苏帕贝斯

Firebase 产品的开源替代品

• 数据库
• 实时
• 授权
• 功能
• 边缘函数

但是等等，如果他们已经有了函数为什么还需要边缘函数？

Supabase 函数：您的 PostgreSQL 工具箱

Supabase 函数，也称为数据库函数，本质上是 PostgreSQL 存储过程。它们是可执行的 SQL 代码块，可以从 SQL 查询中调用。

边缘函数：超越数据库

相比之下，Edge 函数是在 Deno 运行时运行的服务器端 TypeScript 函数。它们与 Firebase Cloud Functions 类似，但提供了更灵活和开源的替代方案。

Supabase：PostgreSQL 平台

除了作为 Firebase 的开源替代品之外，Supabase 已发展成为一个全面的 PostgreSQL 平台。它为 PostgreSQL 函数提供一流的支持，将它们无缝集成到其内置实用程序中，并允许您直接从 Supabase 仪表板创建和管理自定义函数。

基本 postgres 函数的结构

CREATE FUNCTION my_function() RETURNS int AS $$
BEGIN
    RETURN 42;
END;
$$ LANGUAGE sql;

细分：

1. CREATE FUNCTION: 该关键字表示我们正在定义一个新函数。
2. my_function(): 这是函数的名称。您可以选择任何您喜欢的有意义的名称。
3. RETURNS int: 这指定函数的返回类型。在这种情况下，该函数将返回一个整数值。
4. AS $$: 这是函数体的开始，用双美元符号 ($$) 括起来来分隔它。
5. BEGIN: 这标志着函数可执行代码的开始。
6. RETURN 42;: 该语句指定函数将返回的值。在本例中，它是整数 42。
7. END;: 这标志着函数可执行代码的结束。
8. $$ LANGUAGE sql;: 这指定了编写函数所用的语言。在本例中，它是 SQL。

目的：

该函数定义了一个名为 my_function 的简单 SQL 函数，它返回整数值 42。这是一个演示 PostgreSQL 中函数定义的结构和语法的基本示例。

要记住的要点：

• 您可以将 my_function 替换为任何所需的函数名称。
• 返回类型可以是任何有效的数据类型，例如文本、布尔值、日期或用户定义的类型。
• 函数体可以包含复杂的逻辑，包括条件语句、循环和对其他函数的调用。

• $$ 分隔符用于以与语言无关的方式包围函数体。

• Postgres 函数也可以由 postgres TRIGGERS 调用，它们类似于函数，但对特定事件做出反应，例如表上的插入、更新或删除

• 执行此函数
  

SELECT my_function();

• 列出此功能

SELECT
    proname AS function_name,
    prokind AS function_type
FROM pg_proc
WHERE proname = 'my_function';

• 删除此功能

DROP FUNCTION my_function();

Supabase postgres 函数

内置函数

Supabase 使用 postgres 函数在数据库中执行某些任务。

示例的简短列表包括

--  list all the supabase functions
SELECT
    proname AS function_name,
    prokind AS function_type
FROM pg_proc;

--  filter for the session supabase functions function
SELECT
    proname AS function_name,
    prokind AS function_type
FROM pg_proc
WHERE proname ILIKE '%session%';

--  selects the curremt jwt
select auth.jwt()

-- select what role is callig the function (anon or authenticated)
select auth.role();

-- select the session user
select session_use;

仪表板上的 Supabase 功能视图
要在 Supabase 中查看其中一些功能，您可以在数据库 > 下查看功能

有用的 Supabase PostgreSQL 函数

在用户注册时创建 user_profile 表

Supabase 将用户数据存储在 auth.users 表中，该表是私有的，不应直接访问或修改。推荐的方法是创建一个公共 users 或 user_profiles 表并将其链接到 auth.users 表。

虽然这可以使用客户端 SDK 通过将创建用户请求与成功的注册请求链接起来来完成，但在 Supabase 端处理它会更可靠、更高效。这可以使用触发器和函数的组合来实现。

--   create the user_profiles table
CREATE TABLE user_profiles (
  id uuid PRIMARY KEY,
  FOREIGN KEY (id) REFERENCES auth.users(id),
  name text,
  email text
);

-- create a function that returns a trigger on auth.users
CREATE 
OR REPLACE FUNCTION public.create_public_user_profile_table() 
RETURNS TRIGGER AS $$ 
BEGIN INSERT INTO public.user_profiles (id,name,email) 
VALUES 
  (
    NEW.id,
    NEW.raw_user_meta_data ->> 'name',
    NEW.raw_user_meta_data ->> 'email'
    -- other fields accessible here 
--     NEW.raw_user_meta_data ->> 'name',
-- NEW.raw_user_meta_data ->> 'picture',

);
RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- create the trigger that executes the function on every new user rowcteation(signup)
CREATE TRIGGER create_public_user_profiles_trigger 
AFTER INSERT ON auth.users FOR EACH ROW WHEN (
        NEW.raw_user_meta_data IS NOT NULL
    )
EXECUTE FUNCTION public.create_public_user_profile_table ();

let { data: user_profiles, error } = await supabase
  .from('user_profiles')
  .select('*')

• 添加关于 jwt 创建的自定义声明 (RBAC) supabse 有关于此的详细文章和视频。

我们需要2块

• public.roles 和 public.role_permissions

-- Custom types
create type public.app_permission as enum ('channels.delete', 'channels.update', 'messages.update', 'messages.delete');
create type public.app_role as enum ('admin', 'moderator');

-- USER ROLES
create table public.user_roles (
  id        bigint generated by default as identity primary key,
  user_id   uuid references public.users on delete cascade not null,
  role      app_role not null,
  unique (user_id, role)
);
comment on table public.user_roles is 'Application roles for each user.';

-- ROLE PERMISSIONS
create table public.role_permissions (
  id           bigint generated by default as identity primary key,
  role         app_role not null,
  permission   app_permission not null,
  unique (role, permission)
);
comment on table public.role_permissions is 'Application permissions for each role.';

用户角色示例

id	user_id	role
1	user-1	admin
2	user-2	moderator

example of a role permission table

id	role	permission
1	admin	channels.update
2	admin	messages.update
3	admin	messages.delete
4	admin	messages.delete
5	moderator	channels.update
6	moderator	messages.update

user with user_id = user-1 will have admin and moderator roles and can delete channels and messages

users with user_id = user-2 can only update channels and messages with the moderator role

-- Create the auth hook function
create or replace function public.custom_access_token_hook(event jsonb)
returns jsonb
language plpgsql
stable
as $$
  declare
    claims jsonb;
    user_role public.app_role;
  begin
    -- Fetch the user role in the user_roles table
    select role into user_role from public.user_roles where user_id = (event->>'user_id')::uuid;

    claims := event->'claims';

    if user_role is not null then
      -- Set the claim
      claims := jsonb_set(claims, '{user_role}', to_jsonb(user_role));
    else
      claims := jsonb_set(claims, '{user_role}', 'null');
    end if;

    -- Update the 'claims' object in the original event
    event := jsonb_set(event, '{claims}', claims);

    -- Return the modified or original event
    return event;
  end;
$$;

grant usage on schema public to supabase_auth_admin;

grant execute
  on function public.custom_access_token_hook
  to supabase_auth_admin;

revoke execute
  on function public.custom_access_token_hook
  from authenticated, anon, public;

grant all
  on table public.user_roles
to supabase_auth_admin;

revoke all
  on table public.user_roles
  from authenticated, anon, public;

create policy "Allow auth admin to read user roles" ON public.user_roles
as permissive for select
to supabase_auth_admin
using (true)

then create a function that will be called to authorize on RLS policies

create or replace function public.authorize(
  requested_permission app_permission
)
returns boolean as $$
declare
  bind_permissions int;
  user_role public.app_role;
begin
  -- Fetch user role once and store it to reduce number of calls
  select (auth.jwt() ->> 'user_role')::public.app_role into user_role;

  select count(*)
  into bind_permissions
  from public.role_permissions
  where role_permissions.permission = requested_permission
    and role_permissions.role = user_role;

  return bind_permissions > 0;
end;
$$ language plpgsql stable security definer set search_path = '';

--  example RLS policies
create policy "Allow authorized delete access" on public.channels for delete using ( (SELECT authorize('channels.delete')) );
create policy "Allow authorized delete access" on public.messages for delete using ( (SELECT authorize('messages.delete')) );

Improved Text:

Creating RPC Endpoints

Supabase functions can be invoked using the rpc function. This is especially useful for writing custom SQL queries when the built-in PostgreSQL APIs are insufficient, such as calculating vector cosine similarity using pg_vector.

create or replace function match_documents (
  query_embedding vector(384),
  match_threshold float,
  match_count int
)
returns table (
  id bigint,
  title text,
  body text,
  similarity float
)
language sql stable
as $$
  select
    documents.id,
    documents.title,
    documents.body,
    1 - (documents.embedding <=> query_embedding) as similarity
  from documents
  where 1 - (documents.embedding <=> query_embedding) > match_threshold
  order by (documents.embedding <=> query_embedding) asc
  limit match_count;
$$;

and call it client side

const { data: documents } = await supabaseClient.rpc('match_documents', {
  query_embedding: embedding, // Pass the embedding you want to compare
  match_threshold: 0.78, // Choose an appropriate threshold for your data
  match_count: 10, // Choose the number of matches
})

Improved Text:

Filtering Out Columns

To prevent certain columns from being modified on the client, create a simple function that triggers on every insert. This function can omit any extra fields the user might send in the request.

-- check if user with roles authenticated or anon submitted an updatedat column and replace it with the current time , if not (thta is an admin) allow it
CREATE
or REPLACE function public.omit_updated__at () returns trigger as 
$$ BEGIN 
IF auth.role() IS NOT NULL AND auth.role() IN ('anon', 'authenticated') 
THEN NEW.updated_at = now();
END IF; 
RETURN NEW; 
END; $$ language plpgsql;

Summary

With a little experimentation, you can unlock the power of Supabase functions and their AI-powered SQL editor. This lowers the barrier to entry for the niche knowledge required to get this working.

Why choose Supabase functions?

• Extend Supabase's API: Supabase can only expose so much through its API. Postgres, however, is a powerful database. Any action you can perform with SQL statements can be wrapped in a function and called from the client or by a trigger.
• Reduce the need for dedicated backends: Supabase functions can fill the simple gaps left by the client SDKs, allowing you to focus on shipping.
• Avoid vendor lock-in: Supabase functions are just Postgres. If you ever need to move to another hosting provider, these functionalities will continue to work.

以上是SUPABASE 函数（非边缘）的详细内容。更多信息请关注PHP中文网其他相关文章！