CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION polar_stat_sql;
-- populate test data
create table company(
   id int primary key     not null,
   name           text    not null,
   age            int     not null,
   address        char(50),
   salary         real
);
insert into company (id,name,age,address,salary)
values (1, 'paul', 32, 'california', 20000.00 );
insert into company (id,name,age,address,salary)
values (2, 'allen', 25, 'texas', 15000.00 );
insert into company (id,name,age,address,salary)
values (3, 'teddy', 23, 'norway', 20000.00 );
insert into company (id,name,age,address,salary)
values (4, 'mark', 25, 'rich-mond ', 65000.00 );
insert into company (id,name,age,address,salary)
values (5, 'david', 27, 'texas', 85000.00 );
insert into company (id,name,age,address,salary)
values (6, 'kim', 22, 'south-hall', 45000.00 );
insert into company values (7, 'james', 24, 'houston', 10000.00 );
create table department(
   id int primary key      not null,
   dept           char(50) not null,
   emp_id         int      not null
);
insert into department (id, dept, emp_id) values (1, 'it billing', 1 );
insert into department (id, dept, emp_id) values (2, 'engineering', 2 );
insert into department (id, dept, emp_id) values (3, 'finance', 7 );
insert into department (id, dept, emp_id) values (4, 'hr', 15 );
select emp_id, name, dept from company inner join department on company.id = department.emp_id ;
 emp_id | name  |                        dept                        
--------+-------+----------------------------------------------------
      1 | paul  | it billing                                        
      2 | allen | engineering                                       
      7 | james | finance                                           
(3 rows)

select emp_id,sum(emp_id) from department group by emp_id limit 1 offset 1;
 emp_id | sum 
--------+-----
      2 |   2
(1 row)

with depInfo as (select emp_id, name, dept from company inner join department on company.id = department.emp_id) select * from depInfo where emp_id >= 2 order by name;
 emp_id | name  |                        dept                        
--------+-------+----------------------------------------------------
      2 | allen | engineering                                       
      7 | james | finance                                           
(2 rows)

with depInfo as (select emp_id, name, dept from company inner join department on company.id = department.emp_id) select name,sum(emp_id) from (select emp_id, name, dept from depInfo where emp_id >= 2 order by name) as temp group by name limit 10;
 name  | sum 
-------+-----
 allen |   2
 james |   7
(2 rows)

-- dummy query
SELECT 1 AS dummy;
 dummy 
-------
     1
(1 row)

SELECT scan_rows,scan_count,join_rows,join_count,sort_rows,sort_count,group_rows,group_count,hash_rows,hash_memory,hash_count FROM polar_stat_sql;
 scan_rows | scan_count | join_rows | join_count | sort_rows | sort_count | group_rows | group_count | hash_rows | hash_memory | hash_count 
-----------+------------+-----------+------------+-----------+------------+------------+-------------+-----------+-------------+------------
        13 |          3 |         3 |          1 |         2 |          1 |          0 |           0 |         7 |           9 |          1
        11 |          2 |         3 |          1 |         0 |          0 |          0 |           0 |         7 |           9 |          1
         0 |          0 |         0 |          0 |         0 |          0 |          0 |           0 |         0 |           0 |          0
         0 |          0 |         0 |          0 |         0 |          0 |          0 |           0 |         0 |           0 |          0
         0 |          0 |         0 |          0 |         0 |          0 |          0 |           0 |         0 |           0 |          0
        13 |          3 |         3 |          1 |         2 |          1 |          2 |           1 |         7 |           9 |          1
         4 |          1 |         0 |          0 |         0 |          0 |          2 |           1 |         0 |           0 |          0
(7 rows)

SELECT count(*) FROM polar_stat_sql WHERE datname = current_database();
 count 
-------
     8
(1 row)

SELECT count(*) FROM polar_stat_sql WHERE datname = current_database() AND (query = 'SELECT $1 AS dummy' OR query = 'SELECT ? AS dummy;');
 count 
-------
     1
(1 row)

SELECT reads, reads_blks, writes, writes_blks FROM polar_stat_sql WHERE datname = current_database() AND (query = 'SELECT $1 AS dummy' OR query = 'SELECT ? AS dummy;');
 reads | reads_blks | writes | writes_blks 
-------+------------+--------+-------------
     0 |          0 |      0 |           0
(1 row)

-- dummy table
CREATE TABLE test AS SELECT i FROM generate_series(1, 1000) i;
-- dummy query again
SELECT count(*) FROM test;
 count 
-------
  1000
(1 row)

SELECT user_time + system_time > 0 AS cpu_time_ok FROM polar_stat_sql WHERE datname = current_database() AND query LIKE 'SELECT count(*) FROM test%';
 cpu_time_ok 
-------------
 t
(1 row)

-- test sql time 
SELECT SUM(parse_time) >0 as res from polar_stat_sql;
 res 
-----
 f
(1 row)

SELECT SUM(analyze_time) >0 as res from polar_stat_sql;
 res 
-----
 f
(1 row)

SELECT SUM(plan_time) >0 as res from polar_stat_sql;
 res 
-----
 f
(1 row)

SELECT SUM(execute_time) >0 as res from polar_stat_sql;
 res 
-----
 f
(1 row)

-- qps monitor test
create table deltb(id int primary key not null, v int not null);
alter table deltb drop column v;
drop table deltb;
create table tb(id int primary key not null, v int not null);
comment on table tb is 'a test table';
grant all on tb to postgres;
revoke all on tb from postgres;
insert into tb (id, v) values (0, 1), (1, 3);
explain (costs false) select * from tb;
   QUERY PLAN   
----------------
 Seq Scan on tb
(1 row)

update tb set v = 2 where id = 1;
delete from tb where id = 1;
alter table tb rename to newtb;
begin;
lock table newtb in access exclusive mode;
end;
truncate table newtb;
select count(*) from polar_stat_query_count;
 count 
-------
    13
(1 row)