Re: psql \l to accept patterns

On Mon, Jan 7, 2013 at 10:14 AM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:

> Here is a patch for psql's \l command to accept patterns, like \d
> commands do. While at it, I also added an "S" option to show system
> objects and removed system objects from the default display. This might
> be a bit controversial, but it's how it was decided some time ago that
> the \d commands should act.
>
>
I applied the attached patch to the current master branch and everything is
ok.

When build all works fine too... and I do some tests:

1) Now '\l' list only regular databases

postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
------+-------+----------+---------+-------+-------------------
(0 rows)

postgres=# CREATE DATABASE fabrizio;
CREATE DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access
privileges
----------+----------+----------+-------------+-------------+-------------------
fabrizio | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(1 row)

postgres=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access
privileges | Size | Tablespace | Description
----------+----------+----------+-------------+-------------+-------------------+---------+------------+-------------
fabrizio | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
| 5945 kB | pg_default |
(1 row)

postgres=# DROP DATABASE fabrizio;
DROP DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
------+-------+----------+---------+-------+-------------------
(0 rows)

2) The new sub-command '\lS' list regular and systems databases

postgres=# \lS
List of databases
Name | Owner | Encoding | Collate | Ctype | Access
privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/fabrizio
+
| | | | |
fabrizio=CTc/fabrizio
template1 | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/fabrizio
+
| | | | |
fabrizio=CTc/fabrizio
(3 rows)

postgres=# CREATE DATABASE fabrizio;
CREATE DATABASE
postgres=# \lS
List of databases
Name | Owner | Encoding | Collate | Ctype | Access
privileges
-----------+----------+----------+-------------+-------------+-----------------------
fabrizio | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/fabrizio
+
| | | | |
fabrizio=CTc/fabrizio
template1 | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/fabrizio
+
| | | | |
fabrizio=CTc/fabrizio
(4 rows)

postgres=# \lS+
List of
databases
Name | Owner | Encoding | Collate | Ctype | Access
privileges | Size | Tablespace | Description

-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------
------------
fabrizio | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
| 5945 kB | pg_default |
postgres | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
| 6041 kB | pg_default | default administrative connecti
on database
template0 | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/fabrizio
+| 5945 kB | pg_default | unmodifiable empty database
| | | | |
fabrizio=CTc/fabrizio | | |
template1 | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/fabrizio
+| 5945 kB | pg_default | default template for new databa
ses
| | | | |
fabrizio=CTc/fabrizio | | |
(4 rows)

postgres=# DROP DATABASE fabrizio ;
DROP DATABASE
postgres=# \lS
List of databases
Name | Owner | Encoding | Collate | Ctype | Access
privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/fabrizio
+
| | | | |
fabrizio=CTc/fabrizio
template1 | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/fabrizio
+
| | | | |
fabrizio=CTc/fabrizio
(3 rows)

3) Now '\l[S+] [pattern]' works:

postgres=# CREATE DATABASE fabrizio;
CRECREATE DATABASE
postgres=# CREATE DATABASE postgis;
CREATE DATABASE
postgres=# CREATE DATABASE mytemplate;
CREATE DATABASE
postgres=# \l fab*
List of databases
Name | Owner | Encoding | Collate | Ctype | Access
privileges
----------+----------+----------+-------------+-------------+-------------------
fabrizio | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(1 row)

postgres=# \l post*
List of databases
Name | Owner | Encoding | Collate | Ctype | Access
privileges
----------+----------+----------+-------------+-------------+-------------------
postgis | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(2 rows)

postgres=# \l *template*
List of databases
Name | Owner | Encoding | Collate | Ctype | Access
privileges
------------+----------+----------+-------------+-------------+-----------------------
mytemplate | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/fabrizio
+
| | | | |
fabrizio=CTc/fabrizio
template1 | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/fabrizio
+
| | | | |
fabrizio=CTc/fabrizio
(3 rows)

postgres=# \l *template
List of databases
Name | Owner | Encoding | Collate | Ctype | Access
privileges
------------+----------+----------+-------------+-------------+-------------------
mytemplate | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(1 row)

4) By command line all works ok too...

$ ./bin/psql -c "\l"
List of databases
Name | Owner | Encoding | Collate | Ctype | Access
privileges
------------+----------+----------+-------------+-------------+-------------------
fabrizio | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
mytemplate | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgis | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(3 rows)

$ ./bin/psql -c "\lS"
List of databases
Name | Owner | Encoding | Collate | Ctype | Access
privileges
------------+----------+----------+-------------+-------------+-----------------------
fabrizio | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
mytemplate | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgis | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/fabrizio
+
| | | | |
fabrizio=CTc/fabrizio
template1 | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/fabrizio
+
| | | | |
fabrizio=CTc/fabrizio
(6 rows)

$ ./bin/psql -c "\l post*"
List of databases
Name | Owner | Encoding | Collate | Ctype | Access
privileges
----------+----------+----------+-------------+-------------+-------------------
postgis | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(2 rows)

5) Docs and psql help was updated correctly.

The attached patch is ok for me and ready for commit.

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello