Add smart completion for USERS and EXTENSIONS

[amjith]

Users of a database can be suggested after the OWNER keyword in CREATE DATABASE.

eg:

CREATE DATABASE discourse_development OWNER <user>

Extensions in a database can be suggested for CREATE EXTENSION.

eg:

CREATE EXTENSION <extension_name>

[bersace]

There is a function for this to feed the completer.

postgres> select name, comment from pg_available_extensions() order by name;
+--------------------+----------------------------------------------------------------------+
| name               | comment                                                              |
|--------------------+----------------------------------------------------------------------|
| adminpack          | administrative functions for PostgreSQL                              |
| autoinc            | functions for autoincrementing fields                                |
| bloom              | bloom access method - signature file based index                     |
| btree_gin          | support for indexing common datatypes in GIN                         |
| btree_gist         | support for indexing common datatypes in GiST                        |
| chkpass            | data type for auto-encrypted passwords                               |
| citext             | data type for case-insensitive character strings                     |
| cube               | data type for multidimensional cubes                                 |
| dblink             | connect to other PostgreSQL databases from within a database         |
| dict_int           | text search dictionary template for integers                         |
| dict_xsyn          | text search dictionary template for extended synonym processing      |
| earthdistance      | calculate great-circle distances on the surface of the Earth         |
| file_fdw           | foreign-data wrapper for flat file access                            |
| fuzzystrmatch      | determine similarities and distance between strings                  |
| hstore             | data type for storing sets of (key, value) pairs                     |
| insert_username    | functions for tracking who changed a table                           |
| intagg             | integer aggregator and enumerator (obsolete)                         |
| intarray           | functions, operators, and index support for 1-D arrays of integers   |
| isn                | data types for international product numbering standards             |
| lo                 | Large Object maintenance                                             |
| ltree              | data type for hierarchical tree-like structures                      |
| moddatetime        | functions for tracking last modification time                        |
| pageinspect        | inspect the contents of database pages at a low level                |
| pg_buffercache     | examine the shared buffer cache                                      |
| pg_freespacemap    | examine the free space map (FSM)                                     |
| pg_prewarm         | prewarm relation data                                                |
| pg_stat_statements | track execution statistics of all SQL statements executed            |
| pg_trgm            | text similarity measurement and index searching based on trigrams    |
| pg_visibility      | examine the visibility map (VM) and page-level visibility info       |
| pgcrypto           | cryptographic functions                                              |
| pgrowlocks         | show row-level locking information                                   |
| pgstattuple        | show tuple-level statistics                                          |
| plpgsql            | PL/pgSQL procedural language                                         |
| postgres_fdw       | foreign-data wrapper for remote PostgreSQL servers                   |
| refint             | functions for implementing referential integrity (obsolete)          |
| seg                | data type for representing line segments or floating-point intervals |
| sslinfo            | information about SSL certificates                                   |
| tablefunc          | functions that manipulate whole tables, including crosstab           |
| tcn                | Triggered change notifications                                       |
| timetravel         | functions for implementing time travel                               |
| tsearch2           | compatibility package for pre-8.3 text search functions              |
| tsm_system_rows    | TABLESAMPLE method which accepts number of rows as a limit           |
| tsm_system_time    | TABLESAMPLE method which accepts time in milliseconds as a limit     |
| unaccent           | text search dictionary that removes accents                          |
| uuid-ossp          | generate universally unique identifiers (UUIDs)                      |
| xml2               | XPath querying and XSLT                                              |
+--------------------+----------------------------------------------------------------------+
SELECT 46
Time: 0.030s
postgres>

Note, i suggest to split the issues.

[j-bennet]

@amjith Why would you suggest already existing extensions in CREATE EXTENSION?

[amjith]

CREATE EXTENSION was chosen for simplicity, it'll make more sense to suggest the extension after the word EXTENSION for commands like DROP EXTENSION and ALTER EXTENSION.

[saper]

@amjith Why would you suggest already existing extensions in CREATE EXTENSION?

I think this is okay, because already installed extensions are available in the pg_extensions view.

select name from pg_available_extensions except select extname from pg_extension

could work for CREATE EXTENSION I think.

Those views are available starting with 9.1, what is the lowest PostgreSQL version pgcli works with? I've seen it being used successfully against PostgreSQL 8.4

[yrashk]

These (and especially CREATE EXTENSION) would be really great to have!