Preparers summarize text operation examples Innovation Release

-- Create the model. It must support the decode_text and decode_text_batch operations.
SELECT aidb.create_model('model__1952', 't5_local');

-- Keyword arguments
SELECT * FROM aidb.summarize_text(
    input => 'I don''t want a babysitter. I am eleven years old. My babysitter is only three years older than I am," she loudly yelled to her Mom. Now, she really wished she had somebody with her as she heard the clicking, scratching noises outside of the living room window. "This is silly. It''s probably the storm," the girl said. She regretted watching the horror show she had been tuned into for the last half hour. As she searched for the remote to turn off the vampire movie, the front door blew open with a thunderous noise. Carla whirled around to see a dark image.',
    options => '{"model": "model__1952"}'
);

                                                                                                                     summarize_text
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 the girl yelled to her Mom as she heard the clicking, scratching noises outside of the living room window . she regretted watching the horror show she had been tuned into for the last half hour . the front door blew open with a thunderous noise .
(1 row)

-- Positional arguments
SELECT * FROM aidb.summarize_text(
    'There are times when the night sky glows with bands of color. The bands may begin as cloud shapes and then spread into a great arc across the entire sky. They may fall in folds like a curtain drawn across the heavens. The lights usually grow brighter, then suddenly dim. During this time the sky glows with pale yellow, pink, green, violet, blue, and red. These lights are called the Aurora Borealis. Some people call them the Northern Lights. Scientists have been watching them for hundreds of years. They are not quite sure what causes them. In ancient times Long Beach City College WRSC Page 2 of 2 people were afraid of the Lights. They imagined that they saw fiery dragons in the sky. Some even concluded that the heavens were on fire.',
    '{"model": "model__1952"}'
);

                                                                                     summarize_text
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 the night sky glows with bands of color . they may begin as cloud shapes and then spread into a great arc across the entire sky . the lights usually grow brighter, then suddenly dim .
(1 row)

-- Create source test table
CREATE TABLE source_table__1952
(
    id      INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    content TEXT NOT NULL
);
INSERT INTO source_table__1952
VALUES (1, 'I don''t want a babysitter. I am eleven years old. My babysitter is only three years older than I am," she loudly yelled to her Mom. Now, she really wished she had somebody with her as she heard the clicking, scratching noises outside of the living room window. "This is silly. It''s probably the storm," the girl said. She regretted watching the horror show she had been tuned into for the last half hour. As she searched for the remote to turn off the vampire movie, the front door blew open with a thunderous noise. Carla whirled around to see a dark image.'),
       (2, 'There are times when the night sky glows with bands of color. The bands may begin as cloud shapes and then spread into a great arc across the entire sky. They may fall in folds like a curtain drawn across the heavens. The lights usually grow brighter, then suddenly dim. During this time the sky glows with pale yellow, pink, green, violet, blue, and red. These lights are called the Aurora Borealis. Some people call them the Northern Lights. Scientists have been watching them for hundreds of years. They are not quite sure what causes them. In ancient times Long Beach City College WRSC Page 2 of 2 people were afraid of the Lights. They imagined that they saw fiery dragons in the sky. Some even concluded that the heavens were on fire.');

SELECT aidb.create_table_preparer(
    name => 'preparer__1952',
    operation => 'SummarizeText',
    source_table => 'source_table__1952',
    source_data_column => 'content',
    destination_table => 'summarized_data__1952',
    destination_data_column => 'summary',
    source_key_column => 'id',
    destination_key_column => 'id',
    options => '{"model": "model__1952"}'::JSONB  -- Configuration for the SummarizeText operation
);

SELECT aidb.bulk_data_preparation('preparer__1952');

SELECT * FROM summarized_data__1952;

 id |                                                                                                                      summary
----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 1  | the girl yelled to her Mom as she heard the clicking, scratching noises outside of the living room window . she regretted watching the horror show she had been tuned into for the last half hour . the front door blew open with a thunderous noise .
 2  | the night sky glows with bands of color . they may begin as cloud shapes and then spread into a great arc across the entire sky . the lights usually grow brighter, then suddenly dim .
(2 rows)

-- This model does not support the language adapter
SELECT aidb.create_model('bert_model', 'bert_local');

-- Single execution fails
SELECT * FROM aidb.summarize_text(
    input => 'Hello world',
    options => '{"model": "bert_model"}'
);

ERROR:  The requested adapter is not supported by the model provider: bert_local

-- Preparer creation fails
SELECT aidb.create_table_preparer(
    name => 'preparer_name',
    operation => 'SummarizeText',
    source_table => 'source_table__1952',
    source_data_column => 'content',
    destination_table => 'summarized_data__1952',
    destination_data_column => 'summary',
    options => '{"model": "bert_model"}'::JSONB  -- Incompatible model
);

ERROR:  Failed to create preparer: The requested adapter is not supported by the model provider: bert_local

-- Create source test table with multiple rows per category
CREATE TABLE source_table__1952_agg
(
    id       INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    category TEXT NOT NULL,
    content  TEXT NOT NULL
);
INSERT INTO source_table__1952_agg (category, content)
VALUES ('weather', 'The sun is shining brightly today. It is a beautiful day with clear blue skies.'),
       ('weather', 'The temperature is warm and comfortable. There is a gentle breeze from the south.'),
       ('weather', 'No rain is expected today. Perfect conditions for outdoor activities.'),
       ('sports', 'The home team won their game yesterday with a score of 3-1.'),
       ('sports', 'The star player scored two goals and was named player of the match.'),
       ('sports', 'The coach expressed satisfaction with the team''s performance.');

-- Aggregate and summarize multiple rows grouped by category
SELECT
    category,
    aidb.summarize_text_aggregate(
        content,
        '{"model": "model__1952"}'::json ORDER BY id
    ) AS summary
FROM source_table__1952_agg
GROUP BY category
ORDER BY category;

 category |                                                              summary
----------+-----------------------------------------------------------------------------------------------------------------------------------
 sports   | the home team won their game yesterday with a score of 3-1 . the star player scored two goals and was named player of the match .
 weather  | the sun is shining brightly today . there is a gentle breeze from the south .
(2 rows)

-- Use chunking strategy with the aggregate function
SELECT
    category,
    aidb.summarize_text_aggregate(
        content,
        aidb.summarize_text_config(
            'model__1952',
            aidb.chunk_text_config(80, 80, 10, 'words')
        )::json ORDER BY id
    ) AS summary
FROM source_table__1952_agg
WHERE category = 'weather'
GROUP BY category;

 category |                                    summary
----------+-------------------------------------------------------------------------------
 weather  | the sun is shining brightly today . there is a gentle breeze from the south .
(1 row)

-- Use a custom prompt for summarization
SELECT
    category,
    aidb.summarize_text_aggregate(
        content,
        aidb.summarize_text_config(
            'model__1952',
            prompt => 'translate to German'
        )::json ORDER BY id
    ) AS summary
FROM source_table__1952_agg
WHERE category = 'sports'
GROUP BY category;

 category |                                                                  summary
----------+--------------------------------------------------------------------------------------------------------------------------------------------
 sports   | Das Heimteam hat gestern mit einem Score von 3-1 gewonnen, der Starspieler hat zwei Tore erzielt und wurde zum Spieler des Spiels ernannt.
(1 row)

-- Use reduce strategy with reduction factor
SELECT
    category,
    aidb.summarize_text_aggregate(
        content,
        aidb.summarize_text_config(
            'model__1952',
            aidb.chunk_text_config(60, 60, 5, 'words'),
            'translate to German',
            'reduce',
            5
        )::json ORDER BY id
    ) AS summary
FROM source_table__1952_agg
GROUP BY category
ORDER BY category;

 category |                                                                                                       summary
----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 sports   | Das Heimteam hat gestern mit einem Score von 3-1 gewonnen, der Starspieler hat zwei Tore erzielt und wurde zum Spieler des Spiels ernannt.
 weather  | Die Sonne scheint heute hell, es ist ein schöner Tag mit klarem blauen Himmel, die Temperatur ist warm und komfortabel, die Luft ist sanft und südlich, es gibt keinen Regen, es ist heute keinen Regen zu erwarten.
(2 rows)