openai_chat_complete()

Generate text completions and have conversations with OpenAI’s chat models

Generate text completions using OpenAI’s chat models like GPT-4 and GPT-3.5. This function enables you to have multi-turn conversations, generate text from prompts, and leverage advanced language model capabilities directly from PostgreSQL.

Samples

Generate a simple completion

Ask a question and get a response:

SELECT ai.openai_chat_complete(
    'gpt-4o-mini',
    jsonb_build_array(
        jsonb_build_object('role', 'user', 'content', 'What is PostgreSQL?')
    )
)->'choices'->0->'message'->>'content';

Use system prompts

Set the behavior and context with a system message:

SELECT ai.openai_chat_complete(
    'gpt-4o',
    jsonb_build_array(
        jsonb_build_object('role', 'system', 'content', 'You are a helpful database expert'),
        jsonb_build_object('role', 'user', 'content', 'Explain hypertables in simple terms')
    )
)->'choices'->0->'message'->>'content';

Multi-turn conversation

Continue a conversation with message history:

SELECT ai.openai_chat_complete(
    'gpt-4o-mini',
    jsonb_build_array(
        jsonb_build_object('role', 'system', 'content', 'You are a SQL expert'),
        jsonb_build_object('role', 'user', 'content', 'How do I create a table?'),
        jsonb_build_object('role', 'assistant', 'content', 'Use CREATE TABLE...'),
        jsonb_build_object('role', 'user', 'content', 'Now show me how to add an index')
    )
)->'choices'->0->'message'->>'content';

Control response format

Specify max tokens and temperature:

SELECT ai.openai_chat_complete(
    'gpt-4o-mini',
    jsonb_build_array(
        jsonb_build_object('role', 'user', 'content', 'Write a haiku about databases')
    ),
    max_tokens => 50,
    temperature => 0.7
);

Get the full response

Access all response metadata:

SELECT jsonb_pretty(
    ai.openai_chat_complete(
        'gpt-4o-mini',
        jsonb_build_array(
            jsonb_build_object('role', 'user', 'content', 'Hello!')
        )
    )
);

Arguments

Name	Type	Default	Required	Description
model	TEXT	-	✔	The OpenAI model to use (e.g., gpt-4o, gpt-4o-mini, gpt-3.5-turbo)
messages	JSONB	-	✔	Array of message objects with role and content fields
api_key	TEXT	NULL	✖	OpenAI API key. If not provided, uses ai.openai_api_key setting
api_key_name	TEXT	NULL	✖	Name of the secret containing the API key
frequency_penalty	FLOAT	NULL	✖	Penalize new tokens based on their frequency (-2.0 to 2.0)
logit_bias	JSONB	NULL	✖	Modify the likelihood of specified tokens appearing
logprobs	BOOLEAN	NULL	✖	Return log probabilities of output tokens
top_logprobs	INT	NULL	✖	Number of most likely tokens to return at each position
max_tokens	INT	NULL	✖	Maximum number of tokens to generate
n	INT	NULL	✖	Number of chat completion choices to generate
presence_penalty	FLOAT	NULL	✖	Penalize new tokens based on their presence (-2.0 to 2.0)
response_format	JSONB	NULL	✖	Format of the response (e.g., {"type": "json_object"})
seed	INT	NULL	✖	Random seed for deterministic sampling
stop	TEXT	NULL	✖	Up to 4 sequences where the API will stop generating
temperature	FLOAT	NULL	✖	Sampling temperature (0 to 2). Higher = more random
top_p	FLOAT	NULL	✖	Nucleus sampling parameter (0 to 1)
tools	JSONB	NULL	✖	List of tools the model may call
tool_choice	JSONB	NULL	✖	Controls which tool is called
openai_user	TEXT	NULL	✖	Unique identifier for the end-user
extra_headers	JSONB	NULL	✖	Additional HTTP headers
extra_query	JSONB	NULL	✖	Additional query parameters
verbose	BOOLEAN	FALSE	✖	Enable verbose logging
client_config	JSONB	NULL	✖	Advanced client configuration

Returns

JSONB: A JSON object containing the completion response with the following structure:

• id: Unique identifier for the completion
• object: Always "chat.completion"
• created: Unix timestamp of when the completion was created
• model: The model used for completion
• choices: Array of completion choices
  • index: Choice index
  • message: The generated message with role and content
  • finish_reason: Why the model stopped generating
• usage: Token usage information
  • prompt_tokens: Tokens in the prompt
  • completion_tokens: Tokens in the completion
  • total_tokens: Total tokens used

Related functions

• openai_chat_complete_simple(): simplified interface for quick queries
• openai_chat_complete_with_raw_response(): get raw HTTP response
• openai_tokenize(): count tokens before making API calls