BEGIN;

DROP FUNCTION IF EXISTS create_statements();

CREATE FUNCTION create_statements() RETURNS VOID AS
$create_statements$
BEGIN
    DROP TABLE IF EXISTS statements;
    
    CREATE UNLOGGED TABLE statements AS
        SELECT session_id,
               session_line_num,
               log_time,
               command_tag,
               message_data[1]::Float as duration
            FROM (
                 SELECT session_id,
                       session_line_num,
                       log_time,
                       command_tag,
                       regexp_matches(message, $x$duration\: (\d+\.\d+) ms$x$) AS message_data
                 FROM postgres_log
               ) log
            WHERE command_tag IN ( 'INSERT', 'UPDATE', 'DELETE', 'SELECT' );
    
    CREATE INDEX ON statements(log_time);
    CREATE INDEX ON statements(session_id, session_line_num);
        
END;
$create_statements$
    LANGUAGE 'plpgsql';

DROP FUNCTION IF EXISTS create_statements_per_minute();

CREATE FUNCTION create_statements_per_minute() RETURNS VOID AS
$create_statements_per_minute$
BEGIN

    DROP TABLE IF EXISTS statements_per_minute;
    
    CREATE UNLOGGED TABLE statements_per_minute AS
        SELECT date_trunc('minute', log_time) as measurement_time,
               SUM(CASE WHEN command_tag = 'INSERT' THEN 1 ELSE 0 END) AS insert_count,
               SUM(CASE WHEN command_tag = 'UPDATE' THEN 1 ELSE 0 END) AS update_count,
               SUM(CASE WHEN command_tag = 'DELETE' THEN 1 ELSE 0 END) AS delete_count,
               SUM(CASE WHEN command_tag = 'SELECT' THEN 1 ELSE 0 END) AS select_count,
               AVG(duration) AS avg_duration,
               stddev_pop(duration) AS sd_duration,
               MAX(duration) AS max_duration
            FROM statements
            GROUP BY 1;

    CREATE INDEX ON statements_per_minute(measurement_time);

END;
$create_statements_per_minute$
    LANGUAGE 'plpgsql';

COMMIT;