SUPABASE 函数(非边缘)
Aug 29, 2024 pm 02:00 PM苏帕贝斯
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中文网其他相关文章!

热门文章

热门文章

热门文章标签

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

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

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

Dreamweaver CS6
视觉化网页开发工具

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