#Database stats

# Function Name : GetDbStats
# Input Parameters : 
    # _hostname VARCHAR(100),
    # _DBName TEXT,
    # _BeginDateTime timestamptz DEFAULT now() - interval '1h',
    # _EndDateTime timestamptz DEFAULT now(),

# Note : _hostname & _DBName are mandatory parameters, _BeginDateTime & _EndDateTime are optional by default it takes last 1 hour interval 
EX : select * from GetDbStats('ip-172-31-17-89'::varchar,'testemp'::text, '2022-10-13 00:00', '2022-10-13 05:00');

#Table stats

#Function Name : GetTableStats
# Input Parameters : 
	# _HostName VARCHAR(100), 
	# _DbName TEXT,
	# _TableName TEXT,  /* Possible Values are 'inserts', 'deletes', 'updates' & 'sequential_scans' and 'traffictable'
	# _BeginDate timestamptz DEFAULT now() - INTERVAL '1h',
	# _EndDate timestamptz DEFAULT now(),
	# _Debug BOOL DEFAULT FALSE,
    
# Note : by default it gives last 1 hour interval data, we can specify time ranges if we want
EX : select * from GetTableStats('ip-172-31-17-89'::varchar,'testemp'::text,'inserts', '2022-10-13 00:00', '2022-10-13 05:00');

#Auto vaccumed tables by interval

# Function Name : GetAutoVacuumHistoryByInterval
# Input parameters : _p_interval INTERVAL /* possible values 'Xs','Xm','Xh', 'Xd'. We can replace X by any integer value */

Ex: select * from GetAutoVacuumHistoryByInterval('10m') 

#Auto analyzed tables by interval

# Function Name : GetAutoAnalyzedHistoryByInterval
# Input parameters : _p_interval INTERVAL /* possible values 'Xs','Xm','Xh', 'Xd'. We can replace X by any integer value */

Ex: select * from GetAutoAnalyzedHistoryByInterval('10m') 

#Not auto vaccumed tables by interval

# Function Name : GetNotAutoVacuumHistoryByInterval
# Input parameters : _p_interval INTERVAL /* possible values 'Xs','Xm','Xh', 'Xd'. We can replace X by any integer value */

Ex: select * from GetNotAutoVacuumHistoryByInterval('10m') 

#Not auto analyzed tables by interval

# Function Name : GetNotAutoAnalyzedHistoryByInterval
# Input parameters : _p_interval INTERVAL /* possible values 'Xs','Xm','Xh', 'Xd'. We can replace X by any integer value */

Ex: select * from GetNotAutoAnalyzedHistoryByInterval('10m') 

#Cluster level statistics

# Function Name : snapshots_delta 
# Input Parameters : 
    # _hostname text DEFAULT 'localhost',
    # _BeginDateTime timestamptz DEFAULT now() - INTERVAL '2 hours',
    # _EndDateTime timestamptz DEFAULT now(),

# Note : _hostname is manadatory paramters, _BeginDateTime & _EndDateTime are optional. By default it will generate last 2 hours interval data 
 EX : select * from snapshots_delta('ip-172-31-20-128', now() - intrval '1d', now());

#Unified function for query statistics

# Function Name : GetUnifiedTopNQueries
# Input Parameters : 
	# _HostName TEXT,
	# _DBName TEXT,
	# _OrderBy TEXT DEFAULT NULL, /* Possible Values are 'TotalTime', 'MeanTime' & 'Calls', if we pass NULL we will get all combined result */
	# _StartTime timestamptz DEFAULT now() - INTERVAL '2 hours',
	# _EndTime timestamptz DEFAULT now(),
	# _Limit INT DEFAULT 10,
	# _SortingOrder TEXT DEFAULT 'DESC', /* Possible Values are 'ASC' & 'DESC' */
	# _Debug BOOL DEFAULT FALSE, /* Possible values 'TRUE' & 'FALSE' */

# Top 20 Calls     
EX : SELECT * FROM GetUnifiedTopNQueries('ip-172-31-20-128', 'postgres','Calls', _starttime:=now()-interval '10m', now(), 20);

# Top 10 MeanTime Queries     
EX : SELECT * FROM GetUnifiedTopNQueries('ip-172-31-20-128', 'postgres','MeanTime', '2022-10-17 00:00:00', '2022-11-18 00:00:00');

# Top 10 TotalTime Queries     
EX : SELECT * FROM GetUnifiedTopNQueries('ip-172-31-20-128', 'postgres','TotalTime', '2022-10-17 00:00:00', '2022-10-18 00:00:00');

# Top 10 Calls, TotalTime & MeanTime Queries all together     
EX : SELECT * FROM GetUnifiedTopNQueries('ip-172-31-20-128', 'postgres',NULL, '2022-10-17 00:00:00', '2022-10-18 00:00:00');

#Specific Query

# Function Name : GetQueryByQueryId 
# Input Parameters : 
    #_HostName TEXT,
    #_DBName TEXT,
    #_QueryId BIGINT,
    #_StartTime timestamptz DEFAULT now() - interval '25 minutes',
    #_EndTime timestamptz DEFAULT now() + interval '25 minutes',

#Note : _HostName, _DBName & _QueryId parameters are mandatory, it will generate last 25m interval data 
EX : select * from GetQueryByQueryId('ip-172-31-20-128','postgres',4856400161806292488, now() - interval '1d', now())