--Setup extension
CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION btree_gist;
CREATE EXTENSION powa;
-- Aggregate data every 5 snapshots
SET powa.coalesce = 5;
-- Test created ojects
SELECT * FROM powa_functions ORDER BY module, operation;
 srvid |          module          | operation |         function_name         |      query_source       | query_cleanup | added_manually | enabled | priority 
-------+--------------------------+-----------+-------------------------------+-------------------------+---------------+----------------+---------+----------
     0 | pg_stat_bgwriter         | aggregate | powa_stat_bgwriter_aggregate  |                         |               | f              | t       |       10
     0 | pg_stat_bgwriter         | purge     | powa_stat_bgwriter_purge      |                         |               | f              | t       |       10
     0 | pg_stat_bgwriter         | reset     | powa_stat_bgwriter_reset      |                         |               | f              | t       |       10
     0 | pg_stat_bgwriter         | snapshot  | powa_stat_bgwriter_snapshot   | powa_stat_bgwriter_src  |               | f              | t       |       10
     0 | pg_stat_statements       | aggregate | powa_statements_aggregate     |                         |               | f              | t       |       10
     0 | pg_stat_statements       | purge     | powa_statements_purge         |                         |               | f              | t       |       10
     0 | pg_stat_statements       | purge     | powa_databases_purge          |                         |               | f              | t       |       10
     0 | pg_stat_statements       | reset     | powa_statements_reset         |                         |               | f              | t       |       10
     0 | pg_stat_statements       | snapshot  | powa_databases_snapshot       | powa_databases_src      |               | f              | t       |       -3
     0 | pg_stat_statements       | snapshot  | powa_statements_snapshot      | powa_statements_src     |               | f              | t       |       -2
     0 | powa_stat_all_relations  | aggregate | powa_all_relations_aggregate  |                         |               | f              | t       |       10
     0 | powa_stat_all_relations  | purge     | powa_all_relations_purge      |                         |               | f              | t       |       10
     0 | powa_stat_all_relations  | reset     | powa_all_relations_reset      |                         |               | f              | t       |       10
     0 | powa_stat_all_relations  | snapshot  | powa_all_relations_snapshot   | powa_all_relations_src  |               | f              | t       |       10
     0 | powa_stat_user_functions | aggregate | powa_user_functions_aggregate |                         |               | f              | t       |       10
     0 | powa_stat_user_functions | purge     | powa_user_functions_purge     |                         |               | f              | t       |       10
     0 | powa_stat_user_functions | reset     | powa_user_functions_reset     |                         |               | f              | t       |       10
     0 | powa_stat_user_functions | snapshot  | powa_user_functions_snapshot  | powa_user_functions_src |               | f              | t       |       10
(18 rows)

-- test C SRFs
SELECT COUNT(*) = 0
FROM pg_database,
LATERAL powa_stat_user_functions(oid) f
WHERE datname = current_database();
 ?column? 
----------
 t
(1 row)

SELECT COUNT(*) > 10
FROM pg_database,
LATERAL powa_stat_all_rel(oid)
WHERE datname = current_database();
 ?column? 
----------
 t
(1 row)

-- Test snapshot
SELECT 1, COUNT(*) = 0 FROM powa_user_functions_history_current;
 ?column? | ?column? 
----------+----------
        1 | t
(1 row)

SELECT 1, COUNT(*) = 0 FROM powa_all_relations_history_current;
 ?column? | ?column? 
----------+----------
        1 | t
(1 row)

SELECT 1, COUNT(*) = 0 FROM powa_statements_history_current;
 ?column? | ?column? 
----------+----------
        1 | t
(1 row)

SELECT 1, COUNT(*) = 0 FROM powa_statements_history_current_db;
 ?column? | ?column? 
----------+----------
        1 | t
(1 row)

SELECT 1, COUNT(*) = 0 FROM powa_user_functions_history;
 ?column? | ?column? 
----------+----------
        1 | t
(1 row)

SELECT 1, COUNT(*) = 0 FROM powa_all_relations_history;
 ?column? | ?column? 
----------+----------
        1 | t
(1 row)

SELECT 1, COUNT(*) = 0 FROM powa_statements_history;
 ?column? | ?column? 
----------+----------
        1 | t
(1 row)

SELECT 1, COUNT(*) = 0 FROM powa_statements_history;
 ?column? | ?column? 
----------+----------
        1 | t
(1 row)

SELECT powa_take_snapshot();
 powa_take_snapshot 
--------------------
                  0
(1 row)

SELECT 2, COUNT(*) >= 0 FROM powa_user_functions_history_current;
 ?column? | ?column? 
----------+----------
        2 | t
(1 row)

SELECT 2, COUNT(*) > 0 FROM powa_all_relations_history_current;
 ?column? | ?column? 
----------+----------
        2 | t
(1 row)

SELECT 2, COUNT(*) > 0 FROM powa_statements_history_current;
 ?column? | ?column? 
----------+----------
        2 | t
(1 row)

SELECT 2, COUNT(*) > 0 FROM powa_statements_history_current_db;
 ?column? | ?column? 
----------+----------
        2 | t
(1 row)

SELECT 2, COUNT(*) >= 0 FROM powa_user_functions_history;
 ?column? | ?column? 
----------+----------
        2 | t
(1 row)

SELECT 2, COUNT(*) = 0 FROM powa_all_relations_history;
 ?column? | ?column? 
----------+----------
        2 | t
(1 row)

SELECT 2, COUNT(*) = 0 FROM powa_statements_history;
 ?column? | ?column? 
----------+----------
        2 | t
(1 row)

SELECT 2, COUNT(*) = 0 FROM powa_statements_history;
 ?column? | ?column? 
----------+----------
        2 | t
(1 row)

SELECT powa_take_snapshot();
 powa_take_snapshot 
--------------------
                  0
(1 row)

SELECT powa_take_snapshot();
 powa_take_snapshot 
--------------------
                  0
(1 row)

SELECT powa_take_snapshot();
 powa_take_snapshot 
--------------------
                  0
(1 row)

-- This snapshot will trigger the aggregate
SELECT powa_take_snapshot();
 powa_take_snapshot 
--------------------
                  0
(1 row)

SELECT 3, COUNT(*) >= 0 FROM powa_user_functions_history_current;
 ?column? | ?column? 
----------+----------
        3 | t
(1 row)

SELECT 3, COUNT(*) > 0 FROM powa_all_relations_history_current;
 ?column? | ?column? 
----------+----------
        3 | t
(1 row)

SELECT 3, COUNT(*) > 0 FROM powa_statements_history_current;
 ?column? | ?column? 
----------+----------
        3 | t
(1 row)

SELECT 3, COUNT(*) > 0 FROM powa_statements_history_current_db;
 ?column? | ?column? 
----------+----------
        3 | t
(1 row)

SELECT 3, COUNT(*) >= 0 FROM powa_user_functions_history;
 ?column? | ?column? 
----------+----------
        3 | t
(1 row)

SELECT 3, COUNT(*) > 0 FROM powa_all_relations_history;
 ?column? | ?column? 
----------+----------
        3 | t
(1 row)

SELECT 3, COUNT(*) > 0 FROM powa_statements_history;
 ?column? | ?column? 
----------+----------
        3 | t
(1 row)

SELECT 3, COUNT(*) > 0 FROM powa_statements_history;
 ?column? | ?column? 
----------+----------
        3 | t
(1 row)

-- Test reset function
SELECT * from powa_reset(0);
 powa_reset 
------------
 t
(1 row)

SELECT 4, COUNT(*) = 0 FROM powa_user_functions_history_current;
 ?column? | ?column? 
----------+----------
        4 | t
(1 row)

SELECT 4, COUNT(*) = 0 FROM powa_all_relations_history_current;
 ?column? | ?column? 
----------+----------
        4 | t
(1 row)

SELECT 4, COUNT(*) = 0 FROM powa_statements_history_current;
 ?column? | ?column? 
----------+----------
        4 | t
(1 row)

SELECT 4, COUNT(*) = 0 FROM powa_statements_history_current_db;
 ?column? | ?column? 
----------+----------
        4 | t
(1 row)

SELECT 4, COUNT(*) = 0 FROM powa_user_functions_history;
 ?column? | ?column? 
----------+----------
        4 | t
(1 row)

SELECT 4, COUNT(*) = 0 FROM powa_all_relations_history;
 ?column? | ?column? 
----------+----------
        4 | t
(1 row)

SELECT 4, COUNT(*) = 0 FROM powa_statements_history;
 ?column? | ?column? 
----------+----------
        4 | t
(1 row)

SELECT 4, COUNT(*) = 0 FROM powa_statements_history;
 ?column? | ?column? 
----------+----------
        4 | t
(1 row)