Postgresql with clause

1. View the current database version

Mydb=# select version () Version -- PostgreSQL 12.0 on x86_64-pc-linux-gnu Compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit

2. With clause to view the relationship between role and group

With temp as

(select a.rolame.rolsuper.m.member from pg_authid a join pg_auth_members m

On a.oid=m.roleid)

Select temp.rolname,temp.rolsuper,s.rolname as groupname from temp join pg_authid s

On temp.member = s.oid

Mydb=# with temp as mydb-# (select a.rolname.rolsuperm.member from pg_authid a join pg_auth_members m mydb (# on a.oid=m.roleid) mydb-# select temp.rolname,temp.rolsuper,s.rolname as groupname from temp join pg_authid s mydb-# on temp.member = s.oid Rolname | rolsuper | groupname-- +-- pg_read_all_settings | f | pg_monitor pg_read_all_stats | f | pg_monitor pg_stat_scan_tables | f | pg_monitor