用PolarDB|PostgreSQL提升通用ai机器人在专业领域的精准度

docker run -d -it --cap-add=SYS_PTRACE --cap-add SYS_ADMIN --privileged=true --name pg registry.cn-hangzhou.aliyuncs.com/digoal/opensource_database:pg14_with_exts

docker exec -ti pg bash

psql

# apt install -y python3-pip  
# pip3 install openai  
root@689ed216de12:/tmp# psql  
psql (14.8 (Debian 14.8-1.pgdg110+1))  
Type "help" for help.  
postgres=# create extension plpython3u ;  
CREATE EXTENSION  
postgres=# create extension vector ;  
CREATE EXTENSION

create table tbl_faq (  
  id serial8 primary key,  
  f text,  -- 问题  
  q text,  -- 标准答案  
  v vector(1536)  -- faq (textcat('title: '||f, ' --- '||q)) 文本向量  
);

create or replace function get_v (faq text) returns vector as $$  
  import openai  
  import os  
  text = faq  
  openai.api_key = os.getenv("OPENAI_API_KEY")  
  response = openai.Embedding.create(  
      model = "text-embedding-ada-002",  
      input = text.replace("\n", " ")  
    )  
  embedding = response['data'][0]['embedding']  
  return embedding  
$$ language plpython3u;

insert into tbl_faq(f,q,v) select f,q,get_v(textcat('title: '||f, ' --- '||q)) from 外部表;

create index on tbl_faq using ivfflat (v vector_cosine_ops);  
analyze tbl_faq;

select get_v('用户输入的问题1');

create or replace function get_faq(  
  v vector(1536),   -- 用户抛出问题向量  
  th float,  -- 相似度阈值  
  cnt int    -- 返回多少条  
)  
returns table (  
  id int8,   -- 辅助表ID  
  faq text,   -- 辅助表问题+答案  
  similarity float   -- 相似度  
)  
as $$  
  select   
    tbl_faq.id,   
    textcat('title: '||tbl_faq.f, ' --- '||tbl_faq.q) as faq,  
    1 - (tbl_faq.v <=> v) as similarity  
  from tbl_faq  
  where 1 - (tbl_faq.v <=> v) > th  
  order by similarity desc  
  limit cnt;  
$$ language sql strict stable;

select t.id, t.faq, t.similarity  
from get_faq(  
  (select get_v('用户输入的问题1')),  
  0.8,   -- 相似度阈值  
  1      -- 返回最相似的1条  
) as t;

create or replace function ask_openai(  
  user_input text,  -- 用户输入问题  
  faq text   -- get_faq()得到的参考问题和答案  
)  
returns text as  
$$  
  import openai  
  import os  
  openai.api_key = os.getenv("OPENAI_API_KEY")  
  search_string = user_input  
  docs_text = faq  
  messages = [{"role": "system", "content": "You concisely answer questions based on text provided to you."}]  
  prompt = """Answer the user's prompt or question:   
  {search_string}  
  by summarising the following text:  
  {docs_text}  
  Keep your answer direct and concise. Provide code snippets where applicable.  
  The question is about a Greenplum/PostgreSQL/PolarDB database. You can enrich the answer with other   
  Greenplum or PostgreSQ-relevant details if applicable.""".format(search_string=search_string, docs_text=docs_text)  
  messages.append({"role": "user", "content": prompt})  
  response = openai.ChatCompletion.create(model="gpt-3.5-turbo", messages=messages)  
  return response.choices[0]["message"]["content"]  
$$ language plpython3u;

create or replace function good_ai_assistant(  
  user_input text  -- 只需要用户输入  
)  
returns table (  
  content text  
)  
language sql stable  
as $$  
  select ask_openai(user_input,  
    (select t.faq from get_faq( (select get_v('用户输入的问题1')), 0.8, 1 ) as t)  
  );  
$$;

select content from good_ai_assistant('用户输入的问题');