相信每一个接触Postgres的小伙伴,对psql这个命令行工具都不陌生,它和Oracle的sqlplus命令行工具有着相似的功能,不过它的功能要远远强于sqlplus,我们使用该工具可以对数据库进行任意的操作。
除却常规的对数据库进行增删改查操作之外,相信小伙伴们经常使用的肯定会有类似 /l 来获取当前Postgres实例中包含有哪些数据库,\d 来获取当前模式下包含有哪些表,诸如此类的命令还有很多,这里就步一一列举了,但是,当我们每次执行该命令的时候,小伙伴们可曾想过,该命令是如何工作的?
思考过后,我们来揭晓答案,是通过运行sql查询,就像我们日常运行的sql一样,从数据库中得到相对应信息。
那么,我们能否看到这些sql呢?
接下来就是见证奇迹的时刻了。
psql有一个ECHO_HIDDEN的变量,默认下情况是关闭的,我们可以将该参数打开,就可以看到这些执行的sql了。
该参数有三个可选项,off,noexec,on
off,为默认值,表示当我们运行类似 \l 命令行的时候不显示相对应的sql,只会返回结果;
noexec,当我们设置为该值的时候,只会显示相对应的sql,不会返回结果;
on,当我们设置为该值的时候,会显示相对应的sql,并且会返回结果。
举例
默认设置
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
lxdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =T/postgres +
| | | | | postgres=CTc/postgres+
| | | | | lxdb01=c/postgres +
| | | | | lxdb02=c/postgres
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
testdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres +
| | | | | postgres=CTc/postgres+
| | | | | testdb01=c/postgres
(5 rows)
我们仅仅可以得到查询结果
设置为noexec
postgres=# \set ECHO_HIDDEN noexec
postgres=# \l
********* QUERY **********
SELECT d.datname as "Name",
pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
d.datcollate as "Collate",
d.datctype as "Ctype",
pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;
**************************
我们仅仅得到了相对应的sql,当然,我们直接去运行这个sql也是可以得到对应的结果
postgres=# SELECT d.datname as "Name",
postgres-# pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
postgres-# pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
postgres-# d.datcollate as "Collate",
postgres-# d.datctype as "Ctype",
postgres-# pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
postgres-# FROM pg_catalog.pg_database d
postgres-# ORDER BY 1;
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
lxdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =T/postgres +
| | | | | postgres=CTc/postgres+
| | | | | lxdb01=c/postgres +
| | | | | lxdb02=c/postgres
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
| | | | | rgscust02=c/postgres
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
testdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres +
| | | | | postgres=CTc/postgres+
| | | | | testdb01=c/postgres
(5 rows)
设置为on
postgres=# \set ECHO_HIDDEN on
postgres=# \l
********* QUERY **********
SELECT d.datname as "Name",
pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
d.datcollate as "Collate",
d.datctype as "Ctype",
pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;
**************************
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
lxdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =T/postgres +
| | | | | postgres=CTc/postgres+
| | | | | lxdb01=c/postgres +
| | | | | lxdb02=c/postgres
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
testdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres +
| | | | | postgres=CTc/postgres+
| | | | | testdb01=c/postgres
(5 rows)
我们既得到了相对应的sql,也得到了查询结果
我这里仅仅列出了查看 \l 的实际sql,psql的其它命令行请小伙伴们自行验证。
人生尚且不易,请善待身边每一个人。