Skip site navigation (1) Skip section navigation (2)

Re: psql \l to accept patterns

From: Fabrízio de Royes Mello <fabriziomello(at)gmail(dot)com>
To: Peter Eisentraut <peter_e(at)gmx(dot)net>
Cc: Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: psql \l to accept patterns
Date: 2013-01-07 19:19:19
Message-ID: CAFcNs+qz=_xV16LezcpwbufxgeeEP1rf=qiQOOFnqxpH7whL8A@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

pgsql-hackers by date

Next:From: Alvaro HerreraDate: 2013-01-07 19:23:36
Subject: Re: psql \l to accept patterns
Previous:From: Alvaro HerreraDate: 2013-01-07 19:14:29
Subject: Re: recent ALTER whatever .. SET SCHEMA refactoring

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group