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;
细分:
- CREATE FUNCTION: 该关键字表示我们正在定义一个新函数。
- my_function(): 这是函数的名称。您可以选择任何您喜欢的有意义的名称。
- RETURNS int: 这指定函数的返回类型。在这种情况下,该函数将返回一个整数值。
- AS $$: 这是函数体的开始,用双美元符号 ($$) 括起来来分隔它。
- BEGIN: 这标志着函数可执行代码的开始。
- RETURN 42;: 该语句指定函数将返回的值。在本例中,它是整数 42。
- END;: 这标志着函数可执行代码的结束。
- $$ 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中文网其他相关文章!

热AI工具

Undresser.AI Undress
人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool
免费脱衣服图片

Clothoff.io
AI脱衣机

AI Hentai Generator
免费生成ai无尽的。

热门文章

热工具

记事本++7.3.1
好用且免费的代码编辑器

SublimeText3汉化版
中文版,非常好用

禅工作室 13.0.1
功能强大的PHP集成开发环境

Dreamweaver CS6
视觉化网页开发工具

SublimeText3 Mac版
神级代码编辑软件(SublimeText3)