列出所有函数名

snippet.sql
SELECT
  pg_proc.proname AS "函数名称",
  pg_type.typname AS "返回值数据类型",
  pg_proc.pronargs AS "参数个数"
FROM
  pg_proc
JOIN pg_type
ON (pg_proc.prorettype = pg_type.oid)
WHERE
pronamespace = (SELECT pg_namespace.oid FROM pg_namespace WHERE nspname = '模式(架构)名');

查看当前schema下所有的函数/存储过程

snippet.sql
SELECT
routine_name 
FROM information_schema.routines
WHERE routine_catalog = 'YourDBName'
AND routine_schema = 'YourSchemaName'
ORDER BY routine_name;

查询正在执行的SQL

snippet.sql
SELECT
    procpid,
    START,
    now() - START AS lap,
    current_query
FROM
    (
    SELECT
        backendid,
        pg_stat_get_backend_pid (S.backendid) AS procpid,
        pg_stat_get_backend_activity_start (S.backendid) AS START,
        pg_stat_get_backend_activity (S.backendid) AS current_query
    FROM
        (SELECT pg_stat_get_backend_idset() AS backendid) AS S
    ) AS S
WHERE current_query <> '<IDLE>'
ORDER BY lap DESC;

查看所有连接的用户

snippet.sql
SELECT * FROM pg_stat_activity;

获得Database的oid

snippet.sql
regression=# SELECT oid,* FROM pg_database;
  oid  |  oid  |  datname   | datdba | encoding | datcollate  |  datctype   | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | datminmxid | dattablespace |               datacl                
-------+-------+------------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+--------------+------------+---------------+-------------------------------------
 13593 | 13593 | postgres   |     10 |        6 | zh_CN.UTF-8 | zh_CN.UTF-8 | f             | t            |           -1 |         13592 |          479 |          1 |          1663 | 
 16384 | 16384 | tpch1s     |     10 |        6 | zh_CN.UTF-8 | zh_CN.UTF-8 | f             | t            |           -1 |         13592 |          479 |          1 |          1663 |

获得数据表的oid

方法1（系统表）：

snippet.sql
tpch1s=# SELECT oid FROM pg_class WHERE relname = 'lineitem';
  oid  
-------
 16434
(1 ROW)

方法2（别名特性）：

snippet.sql
tpch1s=# SELECT 'lineitem'::regclass::oid;
  oid  
-------
 16434
(1 ROW)