如何在Supabase中用PL/pgSQL动态创建向量存储表与函数？

一、修复现有函数的错误 #

你遇到的语法错误来自两个核心问题：

1. #variable_conflict use_column 并非PostgreSQL支持的标准语法，会被解析为无效符号导致报错，直接移除即可（你的场景中不存在变量名冲突，无需该指令）。
2. 动态生成搜索函数时，表名引用方式错误：你用了 ''%I''.embedding，这会把表名处理成字符串而非数据库标识符，正确做法是直接使用 %I.embedding，format 函数的 %I 会自动为标识符添加合适的引号。

修复后的函数代码：

create or replace function setup_vector_store(table_name text)
returns void
language plpgsql
as $$
begin
  -- 启用pgvector扩展（只需执行一次，可移出函数提升效率）
  execute 'create extension if not exists vector';

  -- 删除同名搜索函数（如果存在）
  execute 'drop function if exists match_documents(vector(1024), int, jsonb)';

  -- 创建向量存储表
  execute format('
  create table %I (
    id bigint primary key generated always as identity,
    content text,
    metadata jsonb,
    embedding vector(1024)
  )', table_name);

  -- 创建动态搜索函数
  execute format('
  create function match_documents (
    query_embedding vector(1024),
    match_count int default null,
    filter jsonb default ''{}''
  ) returns table (
    id bigint,
    content text,
    metadata jsonb,
    similarity float
  ) language plpgsql as $$
  begin
    return query
    select
      id,
      content,
      metadata,
      1 - (%I.embedding <=> query_embedding) as similarity
    from %I
    where metadata @> filter
    order by %I.embedding <=> query_embedding
    limit match_count;
  end;
  $$;', table_name, table_name, table_name);
end;
$$;

二、其他动态创建向量存储表的方法 #

方法1：通过Supabase Python SDK直接执行动态SQL #

无需在数据库中创建函数，直接在Python层拼接SQL并执行，灵活性更高，还能避免函数名冲突：

from supabase import create_client, Client

supabase: Client = create_client("你的Supabase URL", "你的API密钥")

def setup_vector_store_python(table_name: str):
    # 启用pgvector扩展（仅需执行一次）
    supabase.sql("create extension if not exists vector").execute()

    # 创建向量存储表
    create_table_sql = f'''
    create table "{table_name}" (
      id bigint primary key generated always as identity,
      content text,
      metadata jsonb,
      embedding vector(1024)
    )
    '''
    supabase.sql(create_table_sql).execute()

    # 创建专属搜索函数
    create_function_sql = f'''
    create or replace function match_{table_name}(
      query_embedding vector(1024),
      match_count int default null,
      filter jsonb default '{}'
    ) returns table (
      id bigint,
      content text,
      metadata jsonb,
      similarity float
    ) language plpgsql as $$
    begin
      return query
      select
        id,
        content,
        metadata,
        1 - ("{table_name}".embedding <=> query_embedding) as similarity
      from "{table_name}"
      where metadata @> filter
      order by "{table_name}".embedding <=> query_embedding
      limit match_count;
    end;
    $$;
    '''
    supabase.sql(create_function_sql).execute()

# 调用示例
setup_vector_store_python("my_custom_docs")

方法2：使用SQL模板文件 #

将创建逻辑保存为模板文件，通过Python替换占位符后执行，适合复杂SQL场景：

1. 新建vector_store_template.sql模板：

-- 创建表
create table {{TABLE_NAME}} (
  id bigint primary key generated always as identity,
  content text,
  metadata jsonb,
  embedding vector(1024)
);

-- 创建专属搜索函数
create or replace function match_{{TABLE_NAME}}(
  query_embedding vector(1024),
  match_count int default null,
  filter jsonb default '{}'
) returns table (
  id bigint,
  content text,
  metadata jsonb,
  similarity float
) language plpgsql as $$
begin
  return query
  select
    id,
    content,
    metadata,
    1 - ({{TABLE_NAME}}.embedding <=> query_embedding) as similarity
  from {{TABLE_NAME}}
  where metadata @> filter
  order by {{TABLE_NAME}}.embedding <=> query_embedding
  limit match_count;
end;
$$;

2. Python代码读取并替换执行：

def setup_from_template(table_name: str):
    with open("vector_store_template.sql", "r") as f:
        template = f.read()
    sql = template.replace("{{TABLE_NAME}}", f'"{table_name}"')
    supabase.sql(sql).execute()

方法3：支持动态向量维度的数据库函数 #

如果需要适配不同向量维度（比如OpenAI GPT-4的1536维度），可扩展函数参数：

create or replace function setup_vector_store_with_dim(table_name text, vector_dim int)
returns void
language plpgsql
as $$
begin
  execute 'create extension if not exists vector';

  -- 删除旧的专属函数（如果存在）
  execute format('drop function if exists match_%I(vector(%s), int, jsonb)', table_name, vector_dim);

  -- 创建带自定义维度的表
  execute format('
  create table %I (
    id bigint primary key generated always as identity,
    content text,
    metadata jsonb,
    embedding vector(%s)
  )', table_name, vector_dim);

  -- 创建对应维度的搜索函数
  execute format('
  create function match_%I (
    query_embedding vector(%s),
    match_count int default null,
    filter jsonb default ''{}''
  ) returns table (
    id bigint,
    content text,
    metadata jsonb,
    similarity float
  ) language plpgsql as $$
  begin
    return query
    select
      id,
      content,
      metadata,
      1 - (%I.embedding <=> query_embedding) as similarity
    from %I
    where metadata @> filter
    order by %I.embedding <=> query_embedding
    limit match_count;
  end;
  $$;', table_name, vector_dim, table_name, table_name, table_name);
end;
$$;

调用示例：select setup_vector_store_with_dim('gpt4_docs', 1536);

内容的提问来源于stack exchange，提问作者Rauhan Ahmed