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;
细分：
目的：
该函数定义了一个名为 my_function 的简单 SQL 函数，它返回整数值 42。这是一个演示 PostgreSQL 中函数定义的结构和语法的基本示例。
要记住的要点：
$$ 分隔符用于以与语言无关的方式包围函数体。
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 函数在数据库中执行某些任务。
示例的简短列表包括
-- 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 中查看其中一些功能，您可以在数据库 > 下查看功能
在用户注册时创建 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('*')
我们需要2块
-- 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;
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?
以上是SUPABASE 函数（非边缘）的详细内容。更多信息请关注PHP中文网其他相关文章！
