PostgreSQL system functions information as follows::
1. getpgusername -- Retrieves the name of the user associated with the current thread
Syntax: select getpgusername();
Ex:-
postgres=# select getpgusername();
getpgusername
---------------
postgres
2. pg_client_encoding -- Retrieves the client encoding of the server
Syntax: select pg_client_encoding();
Ex:-
postgres=# select pg_client_encoding();
pg_client_encoding
--------------------
UTF8
3. pg_reload_conf -- pg_reload_conf sends a SIGHUP signal to the postmaster, causing the configuration files to be reloaded by all server processes.
Syntax: select pg_reload_conf();
Ex:-
Postgres=# Select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
4. pg_rotate_logfile -- pg_rotate_logfile signals the log-file manager to switch to a new output file immediately and it works only when logtype is stderr.
Syntax: select pg_ls_dir('<log directory>');
Ex:-
postgres=# select pg_ls_dir('pg_log');
pg_ls_dir
------------------------------------------
postgresql-2010-10-22_221941.log
postgresql-2010-10-23_000000.log
postgresql-2010-10-23_011320.log
postgresql-2010-10-24_000000.log
postgres=# select pg_rotate_logfile();
pg_rotate_logfile
-------------------
t
(1 row)
postgres=# select pg_ls_dir('pg_log');
pg_ls_dir
------------------------------------------
postgresql-2010-10-22_221941.log
postgresql-2010-10-23_000000.log
postgresql-2010-10-23_011320.log
postgresql-2010-10-24_000000.log
postgresql-2010-10-24_135627.log
5. pg_ls_dir -- It shows content of given directory except the special entries “.” and “..”.(it is treated as relative to $PGDATA). It is not possible (or i don’t know how to) to navigate to another directories using this command other than $PGDATA.
Syntax: select pg_ls_dir('<directory name>');
postgres=# select pg_ls_dir('pg_xlog');
pg_ls_dir
--------------------------
000000010000000000000003
000000010000000000000004
000000010000000000000005
archive_status
(4 rows)
6. pg_sleep --It makes the current session’s process sleep until the specified time interval have elapsed.
Syntax: select pg_sleep();
Ex:
postgres=# SELECT CURRENT_TIMESTAMP; SELECT pg_sleep(60); SELECT CURRENT_TIMESTAMP;
now
----------------------------------
2010-10-24 13:51:54.158547+05:30
(1 row)
pg_sleep
----------
(1 row)
now
----------------------------------
2010-10-24 13:52:54.186517+05:30
(1 row)
7. pg_char_to_encoding -- Convert current encoding to another
Ex:
Convert a database in the ASCII format into one of the UTF-8 format
template1=# create database test with encoding='SQL_ASCII';
CREATE DATABASE
template1=# \connect test
You are now connected to database "test".
test=# create table a (x text);
CREATE TABLE
test=# insert into a values ('');
INSERT 33304378 1
test=# select * from a;
x
--
(1 row)
test=# update pg_database set encoding =
pg_catalog.pg_char_to_encoding('UTF8') where datname='test';
UPDATE 1
test=# select * from a;
x
--
(1 row)
test=# \connect template1
You are now connected to database "template1".
template1=# \connect test
You are now connected to database "test".
test=# select * from a;
x
--
(1 row)
8. pg_postmaster_start_time -- Retrieves the postmaster start time
postgres=# select pg_postmaster_start_time();
pg_postmaster_start_time
-------------------------------
2010-09-01 11:39:36.472834-04
(1 row)
9. pg_switch_xlog -- Generates manual switch on the current transaction log.
postgres=# select pg_xlogfile_name(pg_current_xlog_location());
pg_xlogfile_name
--------------------------
000000010000000000000009
(1 row)
postgres=# select pg_switch_xlog();
pg_switch_xlog
----------------
0/A000000
(1 row)
postgres=# select pg_xlogfile_name(pg_current_xlog_insert_location());
pg_xlogfile_name
--------------------------
00000001000000000000000A
(1 row)
10. pg_current_xlog_location -- displays the current transaction log write location
postgres=# select pg_current_xlog_location();
pg_current_xlog_location
--------------------------
0/8000064
(1 row)
11. pg_xlogfile_name -- extract the transaction log file name.
postgres=# select pg_xlogfile_name('0/8000064');
pg_xlogfile_name
--------------------------
000000010000000000000008
(1 row)
12. pg_current_xlog_insert_location -- Retrieves the current transaction log insertion file name.
postgres=# select pg_current_xlog_insert_location();
pg_current_xlog_insert_location
---------------------------------
0/A000064
13. pg_stat_get_numscans -- To view the number of sequential scans occurred on a table
Syntax: select pg_stat_get_numscans(oid);
Ex:
postgres=# select relfilenode,relname from pg_class where relname='test';
relfilenode | relname
-------------+---------
18328 | test
(1 row)
postgres=# select pg_stat_get_numscans(18328);
pg_stat_get_numscans
----------------------
4
(or)
postgres=# select relname,seq_scan from pg_Stat_user_tables;
relname | seq_scan
--------------------+----------
sl_table | 0
sl_setsync | 0
test | 4
14. pg_stat_get_live_tuples -- To view the number of live rows in a table
Syntax: select pg_stat_get_live_tuples(oid);
Ex:
postgres=# select relfilenode,relname from pg_class where relname='test';
relfilenode | relname
-------------+---------
18328 | test
(1 row)
postgres=# select pg_stat_get_live_tuples(18328);
pg_stat_get_live_tuples
-------------------------
10
(1 row)
15. pg_stat_get_dead_tuples -- To view the dead rows in a table.
Syntax: select pg_stat_get_dead_tuples(oid);
postgres=# select pg_stat_get_dead_tuples(18328);
pg_stat_get_dead_tuples
-------------------------
0
(1 row)
16.pg_get_triggerdef -- View the definition of the trigger in the Database.
Syntax: select pg_get_triggerdef(oid);
Ex:-
select pg_get_triggerdef('test.test_trigger'::regtrigger);
17. pg_get_indexdef -- Retrieves the definition of the index.
Syntax: select pg_get_indexdef(oid);
Ex:-
cluster__va_tn=# select pg_get_indexdef('topics_supertaggrouprow_group_id'::regclass);
(or)
cluster__va_tn=# select schemaname,tablename,indexname,indexdef from pg_indexes limit 2;
schemaname | tablename | indexname | indexdef
------------+---------------+--------------------------+--------------------------------------------------------------------------------------
pg_catalog | pg_pltemplate | pg_pltemplate_name_index | CREATE UNIQUE INDEX pg_pltemplate_name_index ON pg_pltemplate USING btree (tmplname)
pg_catalog | pg_tablespace | pg_tablespace_oid_index | CREATE UNIQUE INDEX pg_tablespace_oid_index ON pg_tablespace USING btree (oid)
18. pg_tablespace_database -- List set of database oids that have objects in the namespace
Syntax:select pg_tablespace_database(oid);
19. pg_conf_load_time -- Find the exact time when the PostgreSQL configuration files were last loaded.
Syntax: select pg_conf_load_time();
Ex:-
postgres=# select pg_conf_load_time();
pg_conf_load_time
----------------------------------
2010-10-26 19:40:14.119542+05:30
20. pg_get_keywords -- List parser keywords in the Database.
Syntax: select pg_get_keywords();
postgres=# select pg_get_keywords();
pg_get_keywords
------------------------------------------------------------------
(abort,U,unreserved)
(absolute,U,unreserved)
(access,U,unreserved)
(action,U,unreserved)
21. pg_relation_size -- Size of relation without index.
Syntax: select pg_relation_size(oid);
postgres=# select pg_relation_size('test_slon'::regclass);
pg_relation_size
------------------
8192
22. pg_total_relation_size -- Find the size of relation with indexes
Syntax: select pg_total_relation_size(oid);
postgres=# select pg_total_relation_size('test_slon'::regclass);
pg_total_relation_size
------------------------
49152
23. pg_lock_status -- View the lock status on the database
Syntax: select pg_lock_status();
Ex:
postgres=# select pg_lock_status();
pg_lock_status
----------------------------------------------------------
(virtualxid,,,,,1/2832,,,,,1/2832,17996,ExclusiveLock,t)
24. pg_prepared_xact -- View the prepared transaction in the Database
Syntax: select pg_prepared_xact();
Ex:
postgres=# select pg_prepared_xact();
pg_prepared_xact
------------------
(0 rows)
25. pg_prepared_statement -- View the prepared statements in the Database
Syntax: select pg_prepared_statement();
Ex:-
postgres=# select pg_prepared_statement();
pg_prepared_statement
-----------------------
(0 rows)
26. pg_cursor -- List the cursors in the Database
Syntax: select pg_cursor();
Ex:-
postgres=# select pg_cursor();
pg_cursor
-----------
(0 rows)
27. pg_show_all_settings -- List the configuration parameter values in postgresql.conf file in Database.
Syntax: select pg_show_all_settings();
28. pg_database_size -- Find the size of the database ( value returns in bytes)
Syntax: select pg_database_size(datname);
Ex:-
postgres=# select pg_database_size('dellstore');
pg_database_size
------------------
24707076
To get human readable format use the below system function
postgres=# select pg_size_pretty(pg_database_size('dellstore'));
pg_size_pretty
----------------
24 MB
29. current_database --View the current database
Syntax: select current_database();
postgres=# select current_database();
current_database
------------------
postgres
30. current_schema -- View the current schema
Syntax: select current_schema();
postgres=# select current_schema();
current_schema
----------------
public
31. pg_stat_get_tuples_deleted -- View the deleted rows in a Table
Syntax: select pg_stat_get_tuples_deleted(oid);
Ex:-
postgres=# select pg_stat_get_tuples_deleted('test1'::regclass);
pg_stat_get_tuples_deleted
----------------------------
4
32. pg_stat_get_tuples_updated -- Find the number of rows updated on the Table
Syntax: select pg_stat_get_tuples_updated(oid)
postgres=# select pg_stat_get_tuples_updated('test1'::regclass);
pg_stat_get_tuples_updated
----------------------------
1
33. pg_stat_get_tuples_inserted -- Find the number of rows inserted on the Table
Syntax: select pg_stat_get_tuples_inserted(oid);
postgres=# select pg_stat_get_tuples_inserted('test1'::regclass);
pg_stat_get_tuples_inserted
-----------------------------
8
34. pg_stat_get_last_analyze_time -- View the last analyze time on a table
Syntax: select pg_stat_get_last_analyze_time(oid);
Ex:-
postgres=# select pg_stat_get_last_analyze_time('test1'::regclass);
pg_stat_get_last_analyze_time
----------------------------------
2010-10-29 00:29:55.935538+05:30
35. pg_stat_get_last_vacuum_time -- View the last vacuum time on a table
Syntax: select pg_stat_get_last_vacuum_time(oid);
Ex:-
postgres=# select pg_stat_get_last_vacuum_time('test1'::regclass);
pg_stat_get_last_vacuum_time
----------------------------------
2010-10-29 00:35:42.563424+05:30
