如何使用OpenAI自动分类PostgreSQL中的数据

PGOPTIONS="-c ai.openai_api_key=$OPENAI_API_KEY" psql -d $PG_CONNECTION_STRING

CREATE EXTENSION IF NOT EXISTS ai CASCADE;
CREATE EXTENSION IF NOT EXISTS vector CASCADE;

tsdb=> \dx
                                                    List of installed extensions
        Name         | Version |   Schema   |                                      Description
---------------------+---------+------------+---------------------------------------------------------------------------------------
 ai                  | 0.4.0   | ai         | helper functions for ai workflows
 pg_stat_statements  | 1.10    | public     | track planning and execution statistics of all SQL statements executed
 plpgsql             | 1.0     | pg_catalog | PL/pgSQL procedural language
 plpython3u          | 1.0     | pg_catalog | PL/Python3U untrusted procedural language
 timescaledb         | 2.17.2  | public     | Enables scalable inserts and complex queries for time-series data (Community Edition)
 timescaledb_toolkit | 1.18.0  | public     | Library of analytical hyperfunctions, time-series pipelining, and other SQL utilities
 vector              | 0.8.0   | public     | vector data type and ivfflat and hnsw access methods
(7 rows)

SELECT *
FROM ai.openai_list_models()
ORDER BY created DESC;

-- Create the `product_reviews` table
CREATE TABLE product_reviews (
    -- Unique identifier for each customer
    customer_id INT NOT NULL PRIMARY KEY,
    -- Timestamp with timezone for the review date
    date TIMESTAMPTZ,
    -- Name of the product being reviewed                    
    product TEXT,
    -- A brief summary of the review                        
    short_review TEXT,
    -- The detailed review text                   
    review TEXT                          
);

\dt

-- Insert sample data into `product_reviews` table
INSERT INTO product_reviews (customer_id, date, product, short_review, review) VALUES
(1, '2022-01-01', 'laptop', 'Great!', 'Great laptop, very fast and reliable.'),
(2, '2022-01-02', 'laptop', 'Good', 'Good laptop, but the battery life could be better.'),
(3, '2022-01-03', 'laptop', 'Worst ever', 'This is the worst laptop I have ever used.'),
(4, '2022-01-04', 'laptop', 'Not bad', 'Not bad, but the screen is a bit small.'),
(5, '2022-01-05', 'phone', 'Excellent', 'Excellent phone, great camera and battery life.'),
(6, '2022-01-06', 'phone', 'Decent', 'Decent phone, but the screen is not as good as I expected.'),
(7, '2022-01-07', 'phone', 'Poor', 'Poor phone, battery life is terrible and camera quality is not good.'),
(8, '2022-01-08', 'tablet', 'Awesome', 'Awesome tablet, very fast and responsive.'),
(9, '2022-01-09', 'tablet', 'Satisfactory', 'Satisfactory tablet, but the screen resolution could be better.'),
(10, '2022-01-10', 'tablet', 'Disappointing', 'Disappointing tablet, slow performance and battery drains quickly.');

-- Create the `product_reviews_classification` table
CREATE TABLE product_reviews_classification (
    -- Unique identifier for each customer
    customer_id INT NOT NULL PRIMARY KEY,
    -- The classification type of the review (e.g., Positive, Negative, Neutral)
    review_type TEXT                     
);

-- Step 1: Format reviews with a structured template
WITH formatted_reviews AS (
    SELECT
        customer_id,
        format(
            E'%s %s\nshort_review: %s\nreview: %s\n\t',
            short_review, review, short_review, review
        ) AS product_final_review
    FROM product_reviews
),

-- Step 2: Classify reviews using OpenAI and categorize the results
classified_reviews AS (
    SELECT
        customer_id,
        CASE
            WHEN result IN ('positive', 'negative', 'neutral') THEN result
            ELSE 'neutral'
        END AS review_type
    FROM (
        SELECT
            customer_id,
            ai.openai_chat_complete(
                'gpt-4o',
                jsonb_build_array(
                    jsonb_build_object(
                        'role', 'system',
                        'content', 'You are an assistant that classifies product reviews into positive, negative, or neutral categories. You can only output one of these three categories: positive, negative, or neutral.'
                    ),
                    jsonb_build_object(
                        'role', 'user',
                        'content',
                        concat(
                            E'Classify the following product review into positive, negative, or neutral categories. You cannot output anything except "positive", "negative", "neutral":\n\n',
                            string_agg(x.product_final_review, E'\n\n')
                        )
                    )
                )
            )->'choices'->0->'message'->>'content' AS result
        FROM formatted_reviews x
        GROUP BY customer_id
    ) subquery
)

-- Step 3: Insert classified reviews into the `product_reviews_classification` table
INSERT INTO product_reviews_classification (customer_id, review_type)
SELECT customer_id, review_type
FROM classified_reviews
;

SELECT * FROM product_reviews_classification;

-- Join the `product_reviews` and `product_reviews_classification` tables
SELECT
    pr.customer_id,
    pr.review,
    prc.review_type
FROM
    product_reviews pr
JOIN
    product_reviews_classification prc
ON
    pr.customer_id = prc.customer_id;

CREATE OR REPLACE FUNCTION classify_and_insert_review() RETURNS TRIGGER AS $$
BEGIN
    -- Step 1: Format the new review with a structured template
    WITH formatted_reviews AS (
        SELECT
            NEW.customer_id AS customer_id,
            format(
                E'%s %s\nshort_review: %s\nreview: %s\n\t',
                NEW.short_review, NEW.review, NEW.short_review, NEW.review
            ) AS product_final_review
    ),

    -- Step 2: Classify the review using OpenAI and categorize the results
    classified_reviews AS (
        SELECT
            customer_id,
            CASE
                WHEN result IN ('positive', 'negative', 'neutral') THEN result
                ELSE 'neutral'
            END AS review_type
        FROM (
            SELECT
                customer_id,
                ai.openai_chat_complete(
                    'gpt-4o',
                    jsonb_build_array(
                        jsonb_build_object(
                            'role', 'system',
                            'content', 'You are an assistant that classifies product reviews into positive, negative, or neutral categories. You can only output one of these three categories: positive, negative, or neutral.'
                        ),
                        jsonb_build_object(
                            'role', 'user',
                            'content',
                            format(
                                E'Classify the following product review into positive, negative, or neutral categories. You cannot output anything except "positive", "negative", "neutral":\n\n%s',
                                product_final_review
                            )
                        )
                    )
                )->'choices'->0->'message'->>'content' AS result
            FROM formatted_reviews
        ) subquery
    )

    -- Step 3: Insert classified review into the product_reviews_classification table
    INSERT INTO product_reviews_classification (customer_id, review_type)
    SELECT customer_id, review_type FROM classified_reviews;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER classify_review_trigger
AFTER INSERT ON product_reviews
FOR EACH ROW
EXECUTE FUNCTION classify_and_insert_review();