Add support for openrouter

[Wang-Yong2018]

I noticed that the current pgai is only support chatgpt. But there are gemini, claude and llama and even more large language model existed. which provide same level service to GPT4 and cost efficient.

Is is possible to provide openrouter service as well? Here is the document link, https://openrouter.ai/docs/quick-start From the quick-start note, we can find it can use openai python library and compatible with openai chat.completion. The main changes will be == connection

1. support base_url parameter , by set it to openrouter address
2. Add another api_keys By support base_url and additonal api_keys, the pgai will support connection to openrouter even ollama model ==model name use the model name from openrouter return api list I think let's all.

I will try to do it in my local computer first.

[ubeytd]

support for base_url would be awesome!

[Wang-Yong2018]

I tried to worked it out by create a simple chat function as below. Now it can support any openai api compatible Large language mode. Tested with Openrouter, Ollama already.
just provide 4 extra parameter:

1. base_url
2. api_key
3. model_name

CREATE OR REPLACE FUNCTION public.simple_chat_complete(_base_url text, _api_key text, _model text, _message text, _is_stream boolean, _frequency_penalty double precision DEFAULT NULL::double precision, _logit_bias jsonb DEFAULT NULL::jsonb, _logprobs boolean DEFAULT NULL::boolean, _top_logprobs integer DEFAULT NULL::integer, _max_tokens integer DEFAULT NULL::integer, _n integer DEFAULT NULL::integer, _presence_penalty double precision DEFAULT NULL::double precision, _response_format jsonb DEFAULT NULL::jsonb, _seed integer DEFAULT NULL::integer, _stop text DEFAULT NULL::text, _temperature double precision DEFAULT NULL::double precision, _top_p double precision DEFAULT NULL::double precision, _tools jsonb DEFAULT NULL::jsonb, _tool_choice jsonb DEFAULT NULL::jsonb, _user text DEFAULT NULL::text) RETURNS jsonb LANGUAGE plpython3u PARALLEL SAFE SET search_path TO 'pg_catalog', 'pg_temp' AS $function$ import openai import json

client = openai.OpenAI(base_url=_base_url, api_key=_api_key)

_message_1 = [ { "role": "user", "content": _message } ]

if not isinstance(_message_1, list): plpy.error("_message is not an array")

_logit_bias_1 = None if _logit_bias is not None: _logit_bias_1 = json.loads(_logit_bias)

_response_format_1 = None if _response_format is not None: _response_format_1 = json.loads(_response_format)

_tools_1 = None if _tools is not None: _tools_1 = json.loads(_tools)

_tool_choice_1 = None if _tool_choice is not None: _tool_choice_1 = json.loads(_tool_choice)

response = client.chat.completions.create( model=_model , messages=_message_1 , frequency_penalty=_frequency_penalty , logit_bias=_logit_bias_1 , logprobs=_logprobs , top_logprobs=_top_logprobs , max_tokens=_max_tokens , n=_n , presence_penalty=_presence_penalty , response_format=_response_format_1 , seed=_seed , stop=_stop , stream=_is_stream , temperature=_temperature , top_p=_top_p , tools=_tools_1 , tool_choice=_tool_choice_1 , user=_user )

return response.model_dump_json() $function$ ;

That's all.

[ubeytd]

Great! Thanks for the workaround @Wang-Yong2018 ! :)

[jgpruitt]

AFAIK, it's impossible to "add" an argument to an existing function.

Let's take openai_embed as an example.

openai_embed
( _model text
, _input text
, _api_key text default null
, _dimensions int default null
, _user text default null
) returns vector

No ALTER FUNCTION command would allow me to add a _base_url parameter. If I do a CREATE OR REPLACE command to add the parameter, it actually creates a NEW function. At that point, we have two functions with the same signature except for _base_url, which would be defaulted to null. If you were to call openai_embed without specifying the _base_url parameter, then postgres doesn't have enough information to go on to decide which version of the function to call. You'll get an error:

ERROR:  function openai_embed(unknown, unknown) is not unique
LINE 1: select openai_embed
               ^
HINT:  Could not choose a best candidate function. You might need to add explicit type casts.

We could drop the old version of the function. However, this would be a breaking change. If a user has created database objects that depend on the old version of the function, we would have to DROP FUNCTION ... CASCADE which would drop the user object.

For example, if I am at version 0.2.0 of the ai extension, and I define a view (it's more likely a user would create a function or procedure, but for the sake of an example, it doesn't matter).

create view bob as
select
  1 as id
, 'hello world' as content
, openai_embed('text-embedding-ada-002', 'hello world') as vec
;

If the pgai extension drops the openai_embed function and recreates it with the _base_url parameter in version 0.3.0, then we'll get this error:

alter extension ai update;
ERROR:  cannot drop function openai_embed(text,text,text,integer,text) because other objects depend on it
DETAIL:  view bob depends on function openai_embed(text,text,text,integer,text)
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

Adding CASCADE would "work," but it would drop the user's view, which is probably a "bad thing."

Not sure what the right way forward is. I'm just capturing my thoughts at the moment.

[jgpruitt]

I guess one option would be to have _base_url NOT have a default in the new function, and keep the old function. Then, if you specify _base_url, postgres would know to use the new function. I could replace the body of the old function to call the new function with NULL for _base_url....

I don't like it, but I think it would work.

I'm gonna think on it a bit more...

[Wang-Yong2018]

I guess one option would be to have _base_url NOT have a default in the new function, and keep the old function. Then, if you specify _base_url, postgres would know to use the new function. I could replace the body of the old function to call the new function with NULL for _base_url....

I don't like it, but I think it would work.

I'm gonna think on it a bit more...

Thanks for spend time and consider the solution from both feature and backward compatible reason. use a new function name should be a nice solution. That's why I just put simple_chat_completion as a name.

previously, when I tried to connection more large language model to my small shiny_llm_map app, I encountered similar case. So, Just create new function with more abstract name. such as llm_connect be the internal function for openai_connect, ollam_connect and openrouter_connect.

PG + AI is very attractive idea. It should be a very fun way to bake AI and data.

Best regards, WY