diff options
| -rw-r--r-- | DEVELOP.rst | 151 | ||||
| -rw-r--r-- | License.txt | 27 | ||||
| -rw-r--r-- | PKG-INFO | 110 | ||||
| -rw-r--r-- | README.rst | 83 | ||||
| -rw-r--r-- | changelog.rst | 342 | ||||
| -rw-r--r-- | pgspecial.egg-info/PKG-INFO | 110 | ||||
| -rw-r--r-- | pgspecial.egg-info/SOURCES.txt | 27 | ||||
| -rw-r--r-- | pgspecial.egg-info/dependency_links.txt | 1 | ||||
| -rw-r--r-- | pgspecial.egg-info/requires.txt | 3 | ||||
| -rw-r--r-- | pgspecial.egg-info/top_level.txt | 1 | ||||
| -rw-r--r-- | pgspecial/__init__.py | 12 | ||||
| -rw-r--r-- | pgspecial/dbcommands.py | 1999 | ||||
| -rw-r--r-- | pgspecial/help/__init__.py | 0 | ||||
| -rw-r--r-- | pgspecial/help/commands.py | 666 | ||||
| -rw-r--r-- | pgspecial/iocommands.py | 342 | ||||
| -rw-r--r-- | pgspecial/main.py | 349 | ||||
| -rw-r--r-- | pgspecial/namedqueries.py | 61 | ||||
| -rw-r--r-- | pyproject.toml | 32 | ||||
| -rw-r--r-- | requirements-dev.txt | 11 | ||||
| -rw-r--r-- | scripts/README.rst | 21 | ||||
| -rw-r--r-- | scripts/docparser.py | 51 | ||||
| -rw-r--r-- | setup.cfg | 4 | ||||
| -rw-r--r-- | setup.py | 47 | ||||
| -rw-r--r-- | tests/conftest.py | 40 | ||||
| -rw-r--r-- | tests/dbutils.py | 186 | ||||
| -rw-r--r-- | tests/test_internal.py | 120 | ||||
| -rw-r--r-- | tests/test_named_queries.py | 46 | ||||
| -rwxr-xr-x | tests/test_specials.py | 1143 | ||||
| -rw-r--r-- | tox.ini | 6 |
29 files changed, 5991 insertions, 0 deletions
diff --git a/DEVELOP.rst b/DEVELOP.rst new file mode 100644 index 0000000..f7ab4de --- /dev/null +++ b/DEVELOP.rst @@ -0,0 +1,151 @@ +Development Guide +----------------- +This is a guide for developers who would like to contribute to this project. + +GitHub Workflow +--------------- + +If you're interested in contributing to pgcli, first of all my heart felt +thanks. `Fork the project <https://github.com/dbcli/pgspecial>`_ in github. +Then clone your fork into your computer (``git clone <url-for-your-fork>``). +Make the changes and create the commits in your local machine. Then push those +changes to your fork. Then click on the pull request icon on github and create +a new pull request. Add a description about the change and send it along. I +promise to review the pull request in a reasonable window of time and get back +to you. + +In order to keep your fork up to date with any changes from mainline, add a new +git remote to your local copy called 'upstream' and point it to the main +``pgspecial`` repo. + +:: + + $ git remote add upstream git@github.com:dbcli/pgspecial.git + +Once the 'upstream' end point is added you can then periodically do a ``git +pull upstream master`` to update your local copy and then do a ``git push +origin master`` to keep your own fork up to date. + +Local Setup +----------- + +The installation instructions in the README file are intended for users of +``pgspecial``. If you're developing ``pgspecial``, you'll need to install it in +a slightly different way so you can see the effects of your changes right away +without having to go through the install cycle every time you change the code. + +It is highly recommended to use virtualenv for development. If you don't know +what a virtualenv is, this `guide +<http://docs.python-guide.org/en/latest/dev/virtualenvs/#virtual-environments>`_ +will help you get started. + +Create a virtualenv (let's call it ``pgspecial-dev``). Activate it: + +:: + + virtualenv ./pgspecial-dev + source ./pgspecial-dev/bin/activate + +Once the virtualenv is activated, `cd` into the local clone of pgspecial folder +and install pgspecial using pip as follows: + +:: + + $ pip install --editable . + + or + + $ pip install -e . + +This will install the necessary dependencies as well as install pgspecial from +the working folder into the virtualenv. By installing it using `pip install -e` +we've linked the pgspecial installation with the working copy. So any changes +made to the code is immediately available in the installed version of +pgspecial. + +Adding PostgreSQL Special (Meta) Commands +----------------------------------------- + +If you want to add a new meta-command, you'll write a function that accepts 3 +parameters. Then you'll mark it with a ``@special_command`` decorator. For +an example, look at ``list_roles`` in ``dbcommands.py``: + + :: + + @special_command('\\du', '\\du[+] [pattern]', 'List roles.') + def list_roles(cur, pattern, verbose): + # some code here + return [(None, cur, headers, cur.statusmessage)] + +Things to note: + +* Your function should return 4 items: ``title, cur, headers, status``. +* ``title`` is optional. It is something you can print out as first line of your + output. +* ``cur`` is cursor that contains records to iterate. +* ``headers`` is result table's list of column headers. +* ``status`` is optional. If provided, it will be printed out last. + +Now, take a closer look at the decorator. The first item in a tuple of +arguments is the command's name. It should be unique. The second item is this +command's syntax. The third item in the tuple is a string which is +the documentation for that special command. + +The example command here, ``\du``, is a meta-command that lists all roles in +the databases. The way you can see the SQL statement issued by PostgreSQL when +this command is executed is to launch `psql -E` and entering ``\du``. + +That will print the results and also print the sql statement that was executed +to produce that result. In most cases it's a single sql statement, but +sometimes it's a series of sql statements that feed the results to each other +to get to the final result. + +Running the tests +----------------- + +The tests are using default user ``postgres`` at ``localhost``, without +the password (authentication mode ``trust``). This can be changed in +``tests/db_utils.py``. + +First, install the requirements for testing: + +:: + + $ pip install -r requirements-dev.txt + +After that, tests can be run with: + +:: + + $ py.test + +Pytest configuration can be found in the ``tool.pytest.ini_options`` table of the ``pyproject.toml`` file. + +Enforcing the code style (linting) +------------------------------ + +When you submit a PR, the changeset is checked for pep8 compliance using +`black <https://github.com/psf/black>`_. If you see a build failing because +of these checks, install ``black`` and apply style fixes: + +:: + + $ pip install black + $ black . + +Then commit and push the fixes. + +To enforce ``black`` applied on every commit, we also suggest installing ``pre-commit`` and +using the ``pre-commit`` hooks available in this repo: + +:: + + $ pip install pre-commit + $ pre-commit install + +Git blame +--------- + +Use ``git blame my_file.py --ignore-revs-file .git-blame-ignore-revs`` to exclude irrelevant commits +(specifically Black) from ``git blame``. For more information, +see `here <https://github.com/psf/black#migrating-your-code-style-without-ruining-git-blame>`_. diff --git a/License.txt b/License.txt new file mode 100644 index 0000000..087af3a --- /dev/null +++ b/License.txt @@ -0,0 +1,27 @@ +Copyright (c) 2015, dbcli +All rights reserved. + +Redistribution and use in source and binary forms, with or without +modification, are permitted provided that the following conditions are met: + +* Redistributions of source code must retain the above copyright notice, this + list of conditions and the following disclaimer. + +* Redistributions in binary form must reproduce the above copyright notice, + this list of conditions and the following disclaimer in the documentation + and/or other materials provided with the distribution. + +* Neither the name of pgspecial nor the names of its + contributors may be used to endorse or promote products derived from + this software without specific prior written permission. + +THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" +AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE +IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE +DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE +FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL +DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR +SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER +CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, +OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE +OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. diff --git a/PKG-INFO b/PKG-INFO new file mode 100644 index 0000000..e4a0e1b --- /dev/null +++ b/PKG-INFO @@ -0,0 +1,110 @@ +Metadata-Version: 2.1 +Name: pgspecial +Version: 2.1.2 +Summary: Meta-commands handler for Postgres Database. +Home-page: https://www.dbcli.com +Author: Pgcli Core Team +Author-email: pgcli-dev@googlegroups.com +License: LICENSE.txt +Classifier: Intended Audience :: Developers +Classifier: License :: OSI Approved :: BSD License +Classifier: Operating System :: Unix +Classifier: Programming Language :: Python +Classifier: Programming Language :: Python :: 3 +Classifier: Programming Language :: Python :: 3.7 +Classifier: Programming Language :: Python :: 3.8 +Classifier: Programming Language :: Python :: 3.9 +Classifier: Programming Language :: Python :: 3.10 +Classifier: Programming Language :: Python :: 3.11 +Classifier: Programming Language :: SQL +Classifier: Topic :: Database +Classifier: Topic :: Database :: Front-Ends +Classifier: Topic :: Software Development +Classifier: Topic :: Software Development :: Libraries :: Python Modules +Requires-Dist: click>=4.1 +Requires-Dist: sqlparse>=0.1.19 +Requires-Dist: psycopg>=3.0.10 + +Meta-commands for Postgres +-------------------------- + +|Build Status| |PyPI| + +This package provides an API to execute meta-commands (AKA "special", or +"backslash commands") on PostgreSQL. + +Quick Start +----------- + +This is a python package. It can be installed with: + +:: + + $ pip install pgspecial + + +Usage +----- + +Once this library is included into your project, you will most likely use the +following imports: + +.. code-block:: python + + from pgspecial.main import PGSpecial + from pgspecial.namedqueries import NamedQueries + +Then you will create and use an instance of PGSpecial: + +.. code-block:: python + + pgspecial = PGSpecial() + for result in pgspecial.execute(cur, sql): + # Do something + +If you want to import named queries from an existing config file, it is +convenient to initialize and keep around the class variable in +``NamedQueries``: + +.. code-block:: python + + from configobj import ConfigObj + + NamedQueries.instance = NamedQueries.from_config( + ConfigObj('~/.config_file_name')) + +Contributions: +-------------- + +If you're interested in contributing to this project, first of all I would like +to extend my heartfelt gratitude. I've written a small doc to describe how to +get this running in a development setup. + +https://github.com/dbcli/pgspecial/blob/master/DEVELOP.rst + +Please feel free to file an issue if you need help. + +Projects using it: +------------------ + +This library is used by the following projects: + +pgcli_: A REPL for Postgres. + +`ipython-sql`_: %%sql magic for IPython + +OmniDB_: An web tool for database management + +If you find this module useful and include it in your project, I'll be happy +to know about it and list it here. + +.. |Build Status| image:: https://github.com/dbcli/pgspecial/workflows/pgspecial/badge.svg + :target: https://github.com/dbcli/pgspecial/actions?query=workflow%3Apgspecial + +.. |PyPI| image:: https://badge.fury.io/py/pgspecial.svg + :target: https://pypi.python.org/pypi/pgspecial/ + :alt: Latest Version + +.. _pgcli: https://github.com/dbcli/pgcli +.. _`ipython-sql`: https://github.com/catherinedevlin/ipython-sql +.. _OmniDB: https://github.com/OmniDB/OmniDB diff --git a/README.rst b/README.rst new file mode 100644 index 0000000..859410f --- /dev/null +++ b/README.rst @@ -0,0 +1,83 @@ +Meta-commands for Postgres +-------------------------- + +|Build Status| |PyPI| + +This package provides an API to execute meta-commands (AKA "special", or +"backslash commands") on PostgreSQL. + +Quick Start +----------- + +This is a python package. It can be installed with: + +:: + + $ pip install pgspecial + + +Usage +----- + +Once this library is included into your project, you will most likely use the +following imports: + +.. code-block:: python + + from pgspecial.main import PGSpecial + from pgspecial.namedqueries import NamedQueries + +Then you will create and use an instance of PGSpecial: + +.. code-block:: python + + pgspecial = PGSpecial() + for result in pgspecial.execute(cur, sql): + # Do something + +If you want to import named queries from an existing config file, it is +convenient to initialize and keep around the class variable in +``NamedQueries``: + +.. code-block:: python + + from configobj import ConfigObj + + NamedQueries.instance = NamedQueries.from_config( + ConfigObj('~/.config_file_name')) + +Contributions: +-------------- + +If you're interested in contributing to this project, first of all I would like +to extend my heartfelt gratitude. I've written a small doc to describe how to +get this running in a development setup. + +https://github.com/dbcli/pgspecial/blob/master/DEVELOP.rst + +Please feel free to file an issue if you need help. + +Projects using it: +------------------ + +This library is used by the following projects: + +pgcli_: A REPL for Postgres. + +`ipython-sql`_: %%sql magic for IPython + +OmniDB_: An web tool for database management + +If you find this module useful and include it in your project, I'll be happy +to know about it and list it here. + +.. |Build Status| image:: https://github.com/dbcli/pgspecial/workflows/pgspecial/badge.svg + :target: https://github.com/dbcli/pgspecial/actions?query=workflow%3Apgspecial + +.. |PyPI| image:: https://badge.fury.io/py/pgspecial.svg + :target: https://pypi.python.org/pypi/pgspecial/ + :alt: Latest Version + +.. _pgcli: https://github.com/dbcli/pgcli +.. _`ipython-sql`: https://github.com/catherinedevlin/ipython-sql +.. _OmniDB: https://github.com/OmniDB/OmniDB diff --git a/changelog.rst b/changelog.rst new file mode 100644 index 0000000..e48b9f2 --- /dev/null +++ b/changelog.rst @@ -0,0 +1,342 @@ +2.1.2 (2024-05-15) +================== + +Bug fixes: +---------- +* Fix `\d` when used with a pattern + +2.1.1 (2023-10-29) +================== + +* Added `build-system` section to `pyproject.toml` to use the modern setuptools backend. +* Fix SyntaxWarning with Python 3.12. +* Fix test_slash_l* to support non-en_US locales (https://github.com/dbcli/pgspecial/issues/140). +* Release script uses `build` module. + +2.1.0 (2023-03-31) +========= + +* Fixed displaying verbose view definitions when running `\d+`. +* Use psycopg.sql for constructing queries. + +2.0.1 (2022-06-17): +=================== + +Internal: +--------- +* Added argument to open_external_editor to specifiy desired editor. + +2.0.0 (2022-06-03): +=================== + +Bug fixes: +---------- + +* Add `pytest` mark `dbtest` to `test_slash_dp_pattern_schema` so it can be skipped when necessary. (Thanks: `Benjamin Beasley`_) + +Internal: +--------- + +* Switch to psycopg3 - breaking change. + +1.13.1 +====== + +Bug fixes: +---------- + +* Throw an exception if the TO/FROM keyword is missing in the `\copy` command. (Thanks: `Amjith`_) +* Allow multiline SQL and use default of 2s timing for the `\watch` command. (Thanks: `Saif Hakim`_) +* Fix for the `\dp` metacommand to properly work with the schema name as pattern (Thanks: `Roberto Dedoro`_) + +Internal: +--------- + +* Drop unused dev/test dependency on mock (Thanks: `Benjamin Beasley`_). + +1.13.0 +====== + +Features: +--------- + +* Add support for the `\ddp` metacommand that lists the default privileges of Postgres objects (Thanks: `Roberto Dedoro`_) +* Add support for named query positional parameters aggregation (Thanks: `Guilhem Charles`_) + +1.12.1 +====== + +* Fix for broken `MANIFEST.in` that prevented source installation (#101. Thanks: `Dave Hirschfeld`_). + +Bug fixes: +---------- + +* Fix deprecation warnings with stricter backslash escaping in strings. (Thanks: `Benjamin Beasley`_) + +1.12.0 +====== + +Features: +--------- + +* Add support for the `\dp` metacommand that lists the privileges of Postgres objects (Thanks: `Guru Devanla`_) +* Add support for the `\np <named_query_pattern>` metacommand that returns named queries matching the pattern (Thanks: `Guru Devanla`_) + +Internal: +--------- + +* Use Black to format the code and run CI style checks. + +1.11.10 +====== + +Bug fixes: +---------- + +* Allows `\d foo` to display information about `IDENTITY` columns. (Thanks: `Denis Gantsev`_) + + +1.11.9 +====== + +Bug fixes: +---------- + +* Fix for `\d foo` command crash in PostgreSQL 12. (Thanks: `Irina Truong`_) + +1.11.8 +====== + +Bug fixes: +---------- + +* ``\d some_schema.some_table`` will now work when ``some_schema`` is not in your ``search_path``. + +1.11.7 +====== + +Internal: +---------- + +* Allow usage of newer versions of psycopg2. (Thanks: `Aluísio Augusto Silva Gonçalves`_). + +1.11.6 +====== + +Bug fixes: +---------- + +* Scope the results of `\d foo` command to the current schema. (Thanks: `Amjith Ramanujam`_) + +Internal: +--------- + +* Add missing psycopg2 dependency. (Thanks: `Dick Marinus`_). +* Fix issues when using the ``\dx+`` command. (Thanks: `Ignacio Campabadal`_). + +1.11.5 +======= + +Features: +--------- + +* Add support for the ``\!`` command. (Thanks: `Ignacio Campabadal`_). +* Add support for describing text search configurations `\dF``. (Thanks: `Ignacio Campabadal`_). +* Add support for the ``\dE`` command. (Thanks: `Catherine Devlin`_). + +1.11.4 +======= + +Bug fixes: +---------- + +* Fixed broken completion for special commands with prompt-toolkit 2.0. (Thanks: `Amjith Ramanujam`_) + +1.11.3 +======= + +Bug fixes: +---------- + +* Fixed the IndexError caused by ``\ef`` without a function name. (Thanks: `Amjith Ramanujam`_) + +1.11.2 +====== + +Bug fixes: +---------- + +* Support for PG 10 partitioning and fix for https://github.com/OmniDB/OmniDB/issues/424. (Thanks: `William Ivanski`_). + +1.11.1 +====== + +Bug fixes: +---------- + +* Remove pycache files from release (Thanks: `Dick Marinus`_). +* Fix ``\df`` under PG11. (Thanks: `Lele Gaifax`_). + +1.11.0 +====== + +Features: +--------- + +* Add support for ``\ev``, ``\ef`` commands. (Thanks: `Catherine Devlin`_). + +Bug fixes: +---------- + +* Avoid the need to escape "%" in named queries (dbcli/pgcli#865). (Thanks: `Jason Ribeiro`_). + +1.10.0 +====== + +Features: +--------- + +* Add support for ``\dD`` command. (Thanks: `Lele Gaifax`_). +* Add support parameter $1...$n in query (Thanks: `Frederic Aoustin`_). + +Bug fixes: +---------- + +* Fix listing of table inheritance in ``\d`` command. (Thanks: `Lele Gaifax`_). + +1.9.0 +===== + +Features: +--------- + +* Change ``\l`` command behavior, and add ``\list`` alias. (Thanks: `François Pietka`_). + +Bug fixes: +---------- + +* Be less strict when searching for the ``\watch`` command. (Thanks: `Irina Truong`_). +* Fix glitch in ``EXCLUDE`` index description emitted by ``\d`` command. (Thanks: `Lele Gaifax`_). +* Fix ``\e`` command handling. (Thanks: `François Pietka`_). +* Fix UnicodeEncodeError when opening sql statement in editor (Thanks: `Klaus Wünschel`_). +* Fix listing of child tables in ``\d`` command. (Thanks: `Lele Gaifax`_). + +1.8.0 +===== + +Features: +--------- + +* Implement ``\sf+`` function_name. (Thanks: `Lele Gaifax`_). +* Separate check constraints with newlines. (Thanks: `Joakim Koljonen`_). +* Enabled ``\dm`` command, corrections to ``\d+``, extended tests. (Thanks: `rsc`_). +* Opening an external editor will edit default text. (Thanks: `Thomas Roten`_). + + +1.7.0 +===== + +Features: +--------- + +* Handling saved queries with parameters. (Thanks: `Marcin Sztolcman`_). + +Bug fixes: +---------- + +* Fix bug where ``\d`` called valid indices invalid & vice versa. (Thanks: `Joakim Koljonen`_). +* Fix typo in ``pset`` description. (Thanks: `Lele Gaifax`_). + +1.6.0 +===== + +Features: +--------- + +* Add a function to extract the sql from ``\watch`` command. (Thanks: `stuartquin`_). + +1.5.0 +===== + +Features: +--------- + +* Add support for ``\db`` command. (Thanks: `Irina Truong`_). + +1.4.0 +===== + +Features: +--------- + +* Add support for ``\copy`` command. (Thanks: `Catherine Devlin`_). +* Add support for ``\dx`` command. (Thanks: `Darik Gamble`_). + +1.3.0 +===== + +Features: +--------- + +* Add initial support for Postgres 8.4 and above.(Thanks: `Timothy Cleaver`_, darikg_). + This enables us to add support for Amazon Redshift. If things look broken please report. + +* Add ``\pset`` pager command. (Thanks: `pik`_). + +Bug fixes: +---------- + +* Fix 'ftoptions' not defined error with FDW. (Thanks: `François Pietka`_). + + +1.2.0 +===== + +Features: +--------- + +* Add support for ``\h``. (Thanks: `stuartquin`_). + Users can now run ``\h [keyword]`` to checkout the help for a keyboard. + +1.1.0 +===== + +Features: +--------- + +* Support for ``\x auto`` by `stuartquin`_ with `darikg`_ (ported over from `pgcli`_). + +1.0.0 +===== + +Features: +--------- + +* First release as an independent package. + +.. _`pgcli`: https://github.com/dbcli/pgcli +.. _`Amjith Ramanujam`: https://github.com/amjith +.. _`stuartquin`: https://github.com/stuartquin +.. _`darikg`: https://github.com/darikg +.. _`Timothy Cleaver`: Timothy Cleaver +.. _`François Pietka`: https://github.com/fpietka +.. _`pik`: https://github.com/pik +.. _`Darik Gamble`: https://github.com/darikg +.. _`Irina Truong`: https://github.com/j-bennet +.. _`Joakim Koljonen`: https://github.com/koljonen +.. _`Marcin Sztolcman`: https://github.com/msztolcman +.. _`Thomas Roten`: https://github.com/tsroten +.. _`Lele Gaifax`: https://github.com/lelit +.. _`rsc`: https://github.com/rafalcieslinski +.. _`Klaus Wünschel`: https://github.com/kwuenschel +.. _`Frederic Aoustin`: https://github.com/fraoustin +.. _`Catherine Devlin`: https://github.com/catherinedevlin +.. _`Jason Ribeiro`: https://github.com/jrib +.. _`Dick Marinus`: https://github.com/meeuw +.. _`William Ivanski`: https://github.com/wind39 +.. _`Aluísio Augusto Silva Gonçalves`: https://github.com/AluisioASG +.. _`Ignacio Campabadal`: https://github.com/igncampa +.. _`Dave Hirschfeld`: https://github.com/dhirschfeld +.. _`Roberto Dedoro`: https://github.com/mirobertod +.. _`Guilhem Charles`: https://github.com/chagui +.. _`Benjamin Beasley`: https://github.com/musicinmybrain diff --git a/pgspecial.egg-info/PKG-INFO b/pgspecial.egg-info/PKG-INFO new file mode 100644 index 0000000..e4a0e1b --- /dev/null +++ b/pgspecial.egg-info/PKG-INFO @@ -0,0 +1,110 @@ +Metadata-Version: 2.1 +Name: pgspecial +Version: 2.1.2 +Summary: Meta-commands handler for Postgres Database. +Home-page: https://www.dbcli.com +Author: Pgcli Core Team +Author-email: pgcli-dev@googlegroups.com +License: LICENSE.txt +Classifier: Intended Audience :: Developers +Classifier: License :: OSI Approved :: BSD License +Classifier: Operating System :: Unix +Classifier: Programming Language :: Python +Classifier: Programming Language :: Python :: 3 +Classifier: Programming Language :: Python :: 3.7 +Classifier: Programming Language :: Python :: 3.8 +Classifier: Programming Language :: Python :: 3.9 +Classifier: Programming Language :: Python :: 3.10 +Classifier: Programming Language :: Python :: 3.11 +Classifier: Programming Language :: SQL +Classifier: Topic :: Database +Classifier: Topic :: Database :: Front-Ends +Classifier: Topic :: Software Development +Classifier: Topic :: Software Development :: Libraries :: Python Modules +Requires-Dist: click>=4.1 +Requires-Dist: sqlparse>=0.1.19 +Requires-Dist: psycopg>=3.0.10 + +Meta-commands for Postgres +-------------------------- + +|Build Status| |PyPI| + +This package provides an API to execute meta-commands (AKA "special", or +"backslash commands") on PostgreSQL. + +Quick Start +----------- + +This is a python package. It can be installed with: + +:: + + $ pip install pgspecial + + +Usage +----- + +Once this library is included into your project, you will most likely use the +following imports: + +.. code-block:: python + + from pgspecial.main import PGSpecial + from pgspecial.namedqueries import NamedQueries + +Then you will create and use an instance of PGSpecial: + +.. code-block:: python + + pgspecial = PGSpecial() + for result in pgspecial.execute(cur, sql): + # Do something + +If you want to import named queries from an existing config file, it is +convenient to initialize and keep around the class variable in +``NamedQueries``: + +.. code-block:: python + + from configobj import ConfigObj + + NamedQueries.instance = NamedQueries.from_config( + ConfigObj('~/.config_file_name')) + +Contributions: +-------------- + +If you're interested in contributing to this project, first of all I would like +to extend my heartfelt gratitude. I've written a small doc to describe how to +get this running in a development setup. + +https://github.com/dbcli/pgspecial/blob/master/DEVELOP.rst + +Please feel free to file an issue if you need help. + +Projects using it: +------------------ + +This library is used by the following projects: + +pgcli_: A REPL for Postgres. + +`ipython-sql`_: %%sql magic for IPython + +OmniDB_: An web tool for database management + +If you find this module useful and include it in your project, I'll be happy +to know about it and list it here. + +.. |Build Status| image:: https://github.com/dbcli/pgspecial/workflows/pgspecial/badge.svg + :target: https://github.com/dbcli/pgspecial/actions?query=workflow%3Apgspecial + +.. |PyPI| image:: https://badge.fury.io/py/pgspecial.svg + :target: https://pypi.python.org/pypi/pgspecial/ + :alt: Latest Version + +.. _pgcli: https://github.com/dbcli/pgcli +.. _`ipython-sql`: https://github.com/catherinedevlin/ipython-sql +.. _OmniDB: https://github.com/OmniDB/OmniDB diff --git a/pgspecial.egg-info/SOURCES.txt b/pgspecial.egg-info/SOURCES.txt new file mode 100644 index 0000000..cc0fbf3 --- /dev/null +++ b/pgspecial.egg-info/SOURCES.txt @@ -0,0 +1,27 @@ +DEVELOP.rst +License.txt +README.rst +changelog.rst +pyproject.toml +requirements-dev.txt +setup.py +tox.ini +pgspecial/__init__.py +pgspecial/dbcommands.py +pgspecial/iocommands.py +pgspecial/main.py +pgspecial/namedqueries.py +pgspecial.egg-info/PKG-INFO +pgspecial.egg-info/SOURCES.txt +pgspecial.egg-info/dependency_links.txt +pgspecial.egg-info/requires.txt +pgspecial.egg-info/top_level.txt +pgspecial/help/__init__.py +pgspecial/help/commands.py +scripts/README.rst +scripts/docparser.py +tests/conftest.py +tests/dbutils.py +tests/test_internal.py +tests/test_named_queries.py +tests/test_specials.py
\ No newline at end of file diff --git a/pgspecial.egg-info/dependency_links.txt b/pgspecial.egg-info/dependency_links.txt new file mode 100644 index 0000000..8b13789 --- /dev/null +++ b/pgspecial.egg-info/dependency_links.txt @@ -0,0 +1 @@ + diff --git a/pgspecial.egg-info/requires.txt b/pgspecial.egg-info/requires.txt new file mode 100644 index 0000000..db384fb --- /dev/null +++ b/pgspecial.egg-info/requires.txt @@ -0,0 +1,3 @@ +click>=4.1 +sqlparse>=0.1.19 +psycopg>=3.0.10 diff --git a/pgspecial.egg-info/top_level.txt b/pgspecial.egg-info/top_level.txt new file mode 100644 index 0000000..a8ad873 --- /dev/null +++ b/pgspecial.egg-info/top_level.txt @@ -0,0 +1 @@ +pgspecial diff --git a/pgspecial/__init__.py b/pgspecial/__init__.py new file mode 100644 index 0000000..99e7587 --- /dev/null +++ b/pgspecial/__init__.py @@ -0,0 +1,12 @@ +__all__ = [] +__version__ = "2.1.2" + + +def export(defn): + """Decorator to explicitly mark functions that are exposed in a lib.""" + globals()[defn.__name__] = defn + __all__.append(defn.__name__) + return defn + + +from . import dbcommands, iocommands diff --git a/pgspecial/dbcommands.py b/pgspecial/dbcommands.py new file mode 100644 index 0000000..5b013bc --- /dev/null +++ b/pgspecial/dbcommands.py @@ -0,0 +1,1999 @@ +from __future__ import unicode_literals +import logging +import shlex +import subprocess +from collections import namedtuple + +from psycopg.sql import SQL + +from .main import special_command, RAW_QUERY + +TableInfo = namedtuple( + "TableInfo", + [ + "checks", + "relkind", + "hasindex", + "hasrules", + "hastriggers", + "hasoids", + "reloptions", + "tablespace", + "reloftype", + "relpersistence", + "relispartition", + ], +) + +log = logging.getLogger(__name__) + + +@special_command("\\l", "\\l[+] [pattern]", "List databases.", aliases=("\\list",)) +def list_databases(cur, pattern, verbose): + params = {} + query = SQL( + """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 + {verbose_fields} + FROM pg_catalog.pg_database d + {verbose_tables} + {pattern_where} + ORDER BY 1""" + ) + if verbose: + params["verbose_fields"] = SQL( + """, + CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT') + THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname)) + ELSE 'No Access' + END as size, + t.spcname as "Tablespace", + pg_catalog.shobj_description(d.oid, 'pg_database') as description""" + ) + params["verbose_tables"] = SQL( + """JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid""" + ) + else: + params["verbose_fields"] = SQL("") + params["verbose_tables"] = SQL("") + + if pattern: + _, schema = sql_name_pattern(pattern) + params["pattern_where"] = SQL("""WHERE d.datname ~ {}""").format(schema) + else: + params["pattern_where"] = SQL("") + formatted_query = query.format(**params) + log.debug(formatted_query.as_string(cur)) + cur.execute(formatted_query) + if cur.description: + headers = [titleize(x.name) for x in cur.description] + return [(None, cur, headers, cur.statusmessage)] + else: + return [(None, None, None, cur.statusmessage)] + + +@special_command("\\du", "\\du[+] [pattern]", "List roles.") +def list_roles(cur, pattern, verbose): + """ + Returns (title, rows, headers, status) + """ + + params = {} + + if cur.connection.info.server_version > 90000: + sql = SQL( + """ + SELECT r.rolname, + r.rolsuper, + r.rolinherit, + r.rolcreaterole, + r.rolcreatedb, + r.rolcanlogin, + r.rolconnlimit, + r.rolvaliduntil, + ARRAY(SELECT b.rolname FROM pg_catalog.pg_auth_members m JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid) WHERE m.member = r.oid) as memberof, + {verbose} + r.rolreplication + FROM pg_catalog.pg_roles r + {pattern} + ORDER BY 1 + """ + ) + if verbose: + params["verbose"] = SQL( + """pg_catalog.shobj_description(r.oid, 'pg_authid') AS description, """ + ) + else: + params["verbose"] = SQL("") + else: + sql = SQL( + """ + SELECT u.usename AS rolname, + u.usesuper AS rolsuper, + true AS rolinherit, + false AS rolcreaterole, + u.usecreatedb AS rolcreatedb, + true AS rolcanlogin, + -1 AS rolconnlimit, + u.valuntil as rolvaliduntil, + ARRAY(SELECT g.groname FROM pg_catalog.pg_group g WHERE u.usesysid = ANY(g.grolist)) as memberof + FROM pg_catalog.pg_user u + """ + ) + + if pattern: + _, schema = sql_name_pattern(pattern) + params["pattern"] = SQL("WHERE r.rolname ~ {}").format(schema) + else: + params["pattern"] = SQL("") + + formatted_query = sql.format(**params) + log.debug(formatted_query.as_string(cur)) + cur.execute(formatted_query) + if cur.description: + headers = [x.name for x in cur.description] + return [(None, cur, headers, cur.statusmessage)] + + +@special_command("\\dp", "\\dp [pattern]", "List privileges.", aliases=("\\z",)) +def list_privileges(cur, pattern, verbose): + """Returns (title, rows, headers, status)""" + sql = SQL( + """ + SELECT n.nspname as schema, + c.relname as name, + CASE c.relkind WHEN 'r' THEN 'table' + WHEN 'v' THEN 'view' + WHEN 'm' THEN 'materialized view' + WHEN 'S' THEN 'sequence' + WHEN 'f' THEN 'foreign table' + WHEN 'p' THEN 'partitioned table' END as type, + pg_catalog.array_to_string(c.relacl, E'\n') AS access_privileges, + + pg_catalog.array_to_string(ARRAY( + SELECT attname || E':\n ' || pg_catalog.array_to_string(attacl, E'\n ') + FROM pg_catalog.pg_attribute a + WHERE attrelid = c.oid AND NOT attisdropped AND attacl IS NOT NULL + ), E'\n') AS column_privileges, + pg_catalog.array_to_string(ARRAY( + SELECT polname + || CASE WHEN NOT polpermissive THEN + E' (RESTRICTIVE)' + ELSE '' END + || CASE WHEN polcmd != '*' THEN + E' (' || polcmd::pg_catalog.text || E'):' + ELSE E':' + END + || CASE WHEN polqual IS NOT NULL THEN + E'\n (u): ' || pg_catalog.pg_get_expr(polqual, polrelid) + ELSE E'' + END + || CASE WHEN polwithcheck IS NOT NULL THEN + E'\n (c): ' || pg_catalog.pg_get_expr(polwithcheck, polrelid) + ELSE E'' + END || CASE WHEN polroles <> '{0}' THEN + E'\n to: ' || pg_catalog.array_to_string( + ARRAY( + SELECT rolname + FROM pg_catalog.pg_roles + WHERE oid = ANY (polroles) + ORDER BY 1 + ), E', ') + ELSE E'' + END + FROM pg_catalog.pg_policy pol + WHERE polrelid = c.oid), E'\n') + AS policies + FROM pg_catalog.pg_class c + LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace + """ + ) + + if pattern: + schema, table = sql_name_pattern(pattern) + if table: + pattern = SQL( + " AND c.relname OPERATOR(pg_catalog.~) {} COLLATE pg_catalog.default " + ).format(table) + if schema: + pattern += SQL( + " AND n.nspname OPERATOR(pg_catalog.~) {} COLLATE pg_catalog.default " + ).format(schema) + else: + pattern = SQL(" AND pg_catalog.pg_table_is_visible(c.oid) ") + + where_clause = SQL( + """ + WHERE c.relkind IN ('r','v','m','S','f','p') + {pattern} + AND n.nspname !~ '^pg_' + """ + ).format(pattern=pattern) + + sql += where_clause + SQL(" ORDER BY 1, 2 ") + + log.debug(sql.as_string(cur)) + cur.execute(sql) + if cur.description: + headers = [titleize(x.name) for x in cur.description] + return [(None, cur, headers, cur.statusmessage)] + + +@special_command("\\ddp", "\\ddp [pattern]", "Lists default access privilege settings.") +def list_default_privileges(cur, pattern, verbose): + """Returns (title, rows, headers, status)""" + sql = SQL( + """ + SELECT pg_catalog.pg_get_userbyid(d.defaclrole) AS owner, + n.nspname AS schema, + CASE d.defaclobjtype WHEN 'r' THEN 'table' + WHEN 'S' THEN 'sequence' + WHEN 'f' THEN 'function' + WHEN 'T' THEN 'type' + WHEN 'n' THEN 'schema' END as type, + pg_catalog.array_to_string(d.defaclacl, E'\n') AS access_privileges + FROM pg_catalog.pg_default_acl d + LEFT JOIN pg_catalog.pg_namespace n ON n.oid = d.defaclnamespace + {where_clause} + ORDER BY 1, 2, 3 + """ + ) + + params = {} + if pattern: + params["where_clause"] = SQL( + """ + WHERE (n.nspname OPERATOR(pg_catalog.~) {pattern} COLLATE pg_catalog.default + OR pg_catalog.pg_get_userbyid(d.defaclrole) OPERATOR(pg_catalog.~) {pattern} COLLATE pg_catalog.default) + """ + ).format(pattern=f"^({pattern})$") + else: + params["where_clause"] = SQL("") + + log.debug(sql.format(**params).as_string(cur)) + cur.execute(sql.format(**params)) + if cur.description: + headers = [titleize(x.name) for x in cur.description] + return [(None, cur, headers, cur.statusmessage)] + + +@special_command("\\db", "\\db[+] [pattern]", "List tablespaces.") +def list_tablespaces(cur, pattern, **_): + """ + Returns (title, rows, headers, status) + """ + + params = {} + cur.execute( + "SELECT EXISTS(SELECT * FROM pg_proc WHERE proname = 'pg_tablespace_location')" + ) + (is_location,) = cur.fetchone() + + sql = SQL( + """SELECT n.spcname AS name, pg_catalog.pg_get_userbyid(n.spcowner) AS owner, + {location} AS location FROM pg_catalog.pg_tablespace n + {pattern} + ORDER BY 1 + """ + ) + + if is_location: + params["location"] = SQL(" pg_catalog.pg_tablespace_location(n.oid)") + else: + params["location"] = SQL(" 'Not supported'") + + if pattern: + _, tbsp = sql_name_pattern(pattern) + params["pattern"] = SQL(" WHERE n.spcname ~ {}").format(tbsp) + else: + params["pattern"] = SQL("") + + formatted_query = sql.format(**params) + log.debug(formatted_query.as_string(cur)) + cur.execute(formatted_query) + + headers = [titleize(x.name) for x in cur.description] if cur.description else None + return [(None, cur, headers, cur.statusmessage)] + + +@special_command("\\dn", "\\dn[+] [pattern]", "List schemas.") +def list_schemas(cur, pattern, verbose): + """ + Returns (title, rows, headers, status) + """ + + params = {} + sql = SQL( + """SELECT n.nspname AS name, pg_catalog.pg_get_userbyid(n.nspowner) AS owner + {verbose} + FROM pg_catalog.pg_namespace n WHERE n.nspname + {pattern} + ORDER BY 1 + """ + ) + + if verbose: + params["verbose"] = SQL( + """, pg_catalog.array_to_string(n.nspacl, E'\\n') AS access_privileges, pg_catalog.obj_description(n.oid, 'pg_namespace') AS description""" + ) + else: + params["verbose"] = SQL("") + + if pattern: + _, schema = sql_name_pattern(pattern) + params["pattern"] = SQL("~ {}").format(schema) + else: + params["pattern"] = SQL("!~ '^pg_' AND n.nspname <> 'information_schema'") + + formatted_query = sql.format(**params) + log.debug(formatted_query.as_string(cur)) + cur.execute(formatted_query) + if cur.description: + headers = [titleize(x.name) for x in cur.description] + return [(None, cur, headers, cur.statusmessage)] + + +# https://github.com/postgres/postgres/blob/master/src/bin/psql/describe.c#L5471-L5638 +@special_command("\\dx", "\\dx[+] [pattern]", "List extensions.") +def list_extensions(cur, pattern, verbose): + def _find_extensions(cur, pattern): + sql = SQL( + """ + SELECT e.extname, e.oid + FROM pg_catalog.pg_extension e + {pattern} + ORDER BY 1, 2; + """ + ) + + params = {} + if pattern: + _, schema = sql_name_pattern(pattern) + params["pattern"] += SQL("WHERE e.extname ~ {}").format(schema) + else: + params["pattern"] = SQL("") + + formatted_query = sql.format(**params) + log.debug(formatted_query.as_string(cur)) + cur.execute(formatted_query) + return cur.fetchall() + + def _describe_extension(cur, oid): + sql = SQL( + """ + SELECT pg_catalog.pg_describe_object(classid, objid, 0) + AS object_description + FROM pg_catalog.pg_depend + WHERE refclassid = 'pg_catalog.pg_extension'::pg_catalog.regclass + AND refobjid = {} + AND deptype = 'e' + ORDER BY 1""" + ).format(oid) + log.debug(sql.as_string(cur)) + cur.execute(sql) + + headers = [titleize(x.name) for x in cur.description] + return cur, headers, cur.statusmessage + + if cur.connection.info.server_version < 90100: + not_supported = "Server versions below 9.1 do not support extensions." + cur, headers = [], [] + yield None, cur, None, not_supported + return + + if verbose: + extensions = _find_extensions(cur, pattern) + + if extensions: + for ext_name, oid in extensions: + title = f'''\nObjects in extension "{ext_name}"''' + cur, headers, status = _describe_extension(cur, oid) + yield title, cur, headers, status + else: + yield None, None, None, f"""Did not find any extension named "{pattern}".""" + return + + sql = SQL( + """ + SELECT e.extname AS name, + e.extversion AS version, + n.nspname AS schema, + c.description AS description + FROM pg_catalog.pg_extension e + LEFT JOIN pg_catalog.pg_namespace n + ON n.oid = e.extnamespace + LEFT JOIN pg_catalog.pg_description c + ON c.objoid = e.oid + AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass + {where_clause} + ORDER BY 1, 2 + """ + ) + + params = {} + if pattern: + _, schema = sql_name_pattern(pattern) + params["where_clause"] = SQL("WHERE e.extname ~ {}").format(schema) + else: + params["where_clause"] = SQL("") + + formatted_query = sql.format(**params) + log.debug(formatted_query.as_string(cur)) + cur.execute(formatted_query) + if cur.description: + headers = [titleize(x.name) for x in cur.description] + yield None, cur, headers, cur.statusmessage + + +def list_objects(cur, pattern, verbose, relkinds): + """ + Returns (title, rows, header, status) + + This method is used by list_tables, list_views, list_materialized views + and list_indexes + + relkinds is a list of strings to filter pg_class.relkind + + """ + schema_pattern, table_pattern = sql_name_pattern(pattern) + + params = {"relkind": relkinds} + if verbose: + params["verbose_columns"] = SQL( + """ + ,pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as size, + pg_catalog.obj_description(c.oid, 'pg_class') as description """ + ) + else: + params["verbose_columns"] = SQL("") + + sql = SQL( + """SELECT n.nspname as schema, + c.relname as name, + CASE c.relkind + WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' + WHEN 'p' THEN 'partitioned table' + WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' + WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' + WHEN 'f' THEN 'foreign table' END + as type, + pg_catalog.pg_get_userbyid(c.relowner) as owner + {verbose_columns} + FROM pg_catalog.pg_class c + LEFT JOIN pg_catalog.pg_namespace n + ON n.oid = c.relnamespace + WHERE c.relkind = ANY({relkind}) + {schema_pattern} + {table_pattern} + ORDER BY 1, 2 + """ + ) + + if schema_pattern: + params["schema_pattern"] = SQL(" AND n.nspname ~ {}").format(schema_pattern) + else: + params["schema_pattern"] = SQL( + """ + AND n.nspname <> 'pg_catalog' + AND n.nspname <> 'information_schema' + AND n.nspname !~ '^pg_toast' + AND pg_catalog.pg_table_is_visible(c.oid) """ + ) + + if table_pattern: + params["table_pattern"] = SQL(" AND c.relname ~ {}").format(table_pattern) + else: + params["table_pattern"] = SQL("") + + formatted_query = sql.format(**params) + log.debug(formatted_query.as_string(cur)) + cur.execute(formatted_query) + + if cur.description: + headers = [titleize(x.name) for x in cur.description] + return [(None, cur, headers, cur.statusmessage)] + + +@special_command("\\dt", "\\dt[+] [pattern]", "List tables.") +def list_tables(cur, pattern, verbose): + return list_objects(cur, pattern, verbose, ["r", "p", ""]) + + +@special_command("\\dv", "\\dv[+] [pattern]", "List views.") +def list_views(cur, pattern, verbose): + return list_objects(cur, pattern, verbose, ["v", "s", ""]) + + +@special_command("\\dm", "\\dm[+] [pattern]", "List materialized views.") +def list_materialized_views(cur, pattern, verbose): + return list_objects(cur, pattern, verbose, ["m", "s", ""]) + + +@special_command("\\ds", "\\ds[+] [pattern]", "List sequences.") +def list_sequences(cur, pattern, verbose): + return list_objects(cur, pattern, verbose, ["S", "s", ""]) + + +@special_command("\\di", "\\di[+] [pattern]", "List indexes.") +def list_indexes(cur, pattern, verbose): + return list_objects(cur, pattern, verbose, ["i", "s", ""]) + + +@special_command("\\df", "\\df[+] [pattern]", "List functions.") +def list_functions(cur, pattern, verbose): + if verbose: + verbose_columns = """ + ,CASE + WHEN p.provolatile = 'i' THEN 'immutable' + WHEN p.provolatile = 's' THEN 'stable' + WHEN p.provolatile = 'v' THEN 'volatile' + END as "Volatility", + pg_catalog.pg_get_userbyid(p.proowner) as owner, + l.lanname as "Language", + p.prosrc as "Source code", + pg_catalog.obj_description(p.oid, 'pg_proc') as description """ + + verbose_table = """ LEFT JOIN pg_catalog.pg_language l + ON l.oid = p.prolang""" + else: + verbose_columns = verbose_table = "" + + if cur.connection.info.server_version >= 110000: + sql = ( + """ + SELECT n.nspname as schema, + p.proname as name, + pg_catalog.pg_get_function_result(p.oid) + as "Result data type", + pg_catalog.pg_get_function_arguments(p.oid) + as "Argument data types", + CASE + WHEN p.prokind = 'a' THEN 'agg' + WHEN p.prokind = 'w' THEN 'window' + WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype + THEN 'trigger' + ELSE 'normal' + END as type """ + + verbose_columns + + """ + FROM pg_catalog.pg_proc p + LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace + """ + + verbose_table + + """ + WHERE """ + ) + elif cur.connection.info.server_version > 90000: + sql = ( + """ + SELECT n.nspname as schema, + p.proname as name, + pg_catalog.pg_get_function_result(p.oid) + as "Result data type", + pg_catalog.pg_get_function_arguments(p.oid) + as "Argument data types", + CASE + WHEN p.proisagg THEN 'agg' + WHEN p.proiswindow THEN 'window' + WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype + THEN 'trigger' + ELSE 'normal' + END as type """ + + verbose_columns + + """ + FROM pg_catalog.pg_proc p + LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace + """ + + verbose_table + + """ + WHERE """ + ) + else: + sql = ( + """ + SELECT n.nspname as schema, + p.proname as name, + pg_catalog.format_type(p.prorettype, NULL) as "Result data type", + pg_catalog.oidvectortypes(p.proargtypes) as "Argument data types", + CASE + WHEN p.proisagg THEN 'agg' + WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger' + ELSE 'normal' + END as type """ + + verbose_columns + + """ + FROM pg_catalog.pg_proc p + LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace + """ + + verbose_table + + """ + WHERE """ + ) + + schema_pattern, func_pattern = sql_name_pattern(pattern) + params = {} + + if schema_pattern: + sql += " n.nspname ~ %(nspname)s " + params["nspname"] = schema_pattern + else: + sql += " pg_catalog.pg_function_is_visible(p.oid) " + + if func_pattern: + sql += " AND p.proname ~ %(proname)s " + params["proname"] = func_pattern + + if not (schema_pattern or func_pattern): + sql += """ AND n.nspname <> 'pg_catalog' + AND n.nspname <> 'information_schema' """ + + sql += " ORDER BY 1, 2, 4" + + log.debug("%s, %s", sql, params) + cur.execute(sql, params) + + if cur.description: + headers = [titleize(x.name) for x in cur.description] + return [(None, cur, headers, cur.statusmessage)] + + +@special_command("\\dT", "\\dT[S+] [pattern]", "List data types") +def list_datatypes(cur, pattern, verbose): + sql = """SELECT n.nspname as schema, + pg_catalog.format_type(t.oid, NULL) AS name, """ + + if verbose: + sql += r""" t.typname AS internal_name, + CASE + WHEN t.typrelid != 0 + THEN CAST('tuple' AS pg_catalog.text) + WHEN t.typlen < 0 + THEN CAST('var' AS pg_catalog.text) + ELSE CAST(t.typlen AS pg_catalog.text) + END AS size, + pg_catalog.array_to_string( + ARRAY( + SELECT e.enumlabel + FROM pg_catalog.pg_enum e + WHERE e.enumtypid = t.oid + ORDER BY e.enumsortorder + ), E'\n') AS elements, + pg_catalog.array_to_string(t.typacl, E'\n') + AS access_privileges, + pg_catalog.obj_description(t.oid, 'pg_type') + AS description""" + else: + sql += """ pg_catalog.obj_description(t.oid, 'pg_type') + as description """ + + if cur.connection.info.server_version > 90000: + sql += """ FROM pg_catalog.pg_type t + LEFT JOIN pg_catalog.pg_namespace n + ON n.oid = t.typnamespace + WHERE (t.typrelid = 0 OR + ( SELECT c.relkind = 'c' + FROM pg_catalog.pg_class c + WHERE c.oid = t.typrelid)) + AND NOT EXISTS( + SELECT 1 + FROM pg_catalog.pg_type el + WHERE el.oid = t.typelem + AND el.typarray = t.oid) """ + else: + sql += """ FROM pg_catalog.pg_type t + LEFT JOIN pg_catalog.pg_namespace n + ON n.oid = t.typnamespace + WHERE (t.typrelid = 0 OR + ( SELECT c.relkind = 'c' + FROM pg_catalog.pg_class c + WHERE c.oid = t.typrelid)) """ + + schema_pattern, type_pattern = sql_name_pattern(pattern) + params = {} + + if schema_pattern: + sql += " AND n.nspname ~ %(nspname)s " + params["nspname"] = schema_pattern + else: + sql += " AND pg_catalog.pg_type_is_visible(t.oid) " + + if type_pattern: + sql += """ AND (t.typname ~ %(typname)s + OR pg_catalog.format_type(t.oid, NULL) ~ %(typname)s) """ + params["typname"] = type_pattern + + if not (schema_pattern or type_pattern): + sql += """ AND n.nspname <> 'pg_catalog' + AND n.nspname <> 'information_schema' """ + + sql += " ORDER BY 1, 2" + log.debug("%s, %s", sql, params) + cur.execute(sql, params) + if cur.description: + headers = [titleize(x.name) for x in cur.description] + return [(None, cur, headers, cur.statusmessage)] + + +@special_command("\\dD", "\\dD[+] [pattern]", "List or describe domains.") +def list_domains(cur, pattern, verbose): + if verbose: + extra_cols = r""", + pg_catalog.array_to_string(t.typacl, E'\n') AS access_privileges, + d.description as description""" + extra_joins = """ + LEFT JOIN pg_catalog.pg_description d ON d.classoid = t.tableoid + AND d.objoid = t.oid AND d.objsubid = 0""" + else: + extra_cols = extra_joins = "" + + sql = f"""\ + SELECT n.nspname AS schema, + t.typname AS name, + pg_catalog.format_type(t.typbasetype, t.typtypmod) as type, + pg_catalog.ltrim((COALESCE((SELECT (' collate ' || c.collname) + FROM pg_catalog.pg_collation AS c, + pg_catalog.pg_type AS bt + WHERE c.oid = t.typcollation + AND bt.oid = t.typbasetype + AND t.typcollation <> bt.typcollation) , '') + || CASE + WHEN t.typnotnull + THEN ' not null' + ELSE '' + END) || CASE + WHEN t.typdefault IS NOT NULL + THEN(' default ' || t.typdefault) + ELSE '' + END) AS modifier, + pg_catalog.array_to_string(ARRAY( + SELECT pg_catalog.pg_get_constraintdef(r.oid, TRUE) + FROM pg_catalog.pg_constraint AS r + WHERE t.oid = r.contypid), ' ') AS check {extra_cols} + FROM pg_catalog.pg_type AS t + LEFT JOIN pg_catalog.pg_namespace AS n ON n.oid = t.typnamespace{extra_joins} + WHERE t.typtype = 'd' """ + + schema_pattern, name_pattern = sql_name_pattern(pattern) + params = {} + if schema_pattern or name_pattern: + if schema_pattern: + sql += " AND n.nspname ~ %(nspname)s" + params["nspname"] = schema_pattern + if name_pattern: + sql += " AND t.typname ~ %(typname)s" + params["typname"] = name_pattern + else: + sql += """ + AND (n.nspname <> 'pg_catalog') + AND (n.nspname <> 'information_schema') + AND pg_catalog.pg_type_is_visible(t.oid)""" + + sql += " ORDER BY 1, 2" + log.debug("%s, %s", sql, params) + cur.execute(sql, params) + if cur.description: + headers = [titleize(x.name) for x in cur.description] + return [(None, cur, headers, cur.statusmessage)] + + +@special_command("\\dF", "\\dF[+] [pattern]", "List text search configurations.") +def list_text_search_configurations(cur, pattern, verbose): + def _find_text_search_configs(cur, pattern): + sql = """ + SELECT c.oid, + c.cfgname, + n.nspname, + p.prsname, + np.nspname AS pnspname + FROM pg_catalog.pg_ts_config c + LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.cfgnamespace, + pg_catalog.pg_ts_parser p + LEFT JOIN pg_catalog.pg_namespace np ON np.oid = p.prsnamespace + WHERE p.oid = c.cfgparser + """ + + params = {} + if pattern: + _, schema = sql_name_pattern(pattern) + sql += "AND c.cfgname ~ %(cfgname)s" + params["cfgname"] = schema + + sql += " ORDER BY 1, 2;" + log.debug("%s, %s", sql, params) + cur.execute(sql, params) + return cur.fetchall() + + def _fetch_oid_details(cur, oid): + params = {"oid": oid} + sql = """ + SELECT + (SELECT t.alias + FROM pg_catalog.ts_token_type(c.cfgparser) AS t + WHERE t.tokid = m.maptokentype ) AS token, + pg_catalog.btrim(ARRAY + (SELECT mm.mapdict::pg_catalog.regdictionary + FROM pg_catalog.pg_ts_config_map AS mm + WHERE mm.mapcfg = m.mapcfg + AND mm.maptokentype = m.maptokentype + ORDER BY mapcfg, maptokentype, mapseqno) :: pg_catalog.text, '{}') AS dictionaries + FROM pg_catalog.pg_ts_config AS c, + pg_catalog.pg_ts_config_map AS m + WHERE c.oid = %(oid)s + AND m.mapcfg = c.oid + GROUP BY m.mapcfg, + m.maptokentype, + c.cfgparser + ORDER BY 1; + """ + + log.debug("%s, %s", sql, params) + cur.execute(sql, params) + + headers = [titleize(x.name) for x in cur.description] + return cur, headers, cur.statusmessage + + if cur.connection.info.server_version < 80300: + not_supported = "Server versions below 8.3 do not support full text search." + cur, headers = [], [] + yield None, cur, None, not_supported + return + + if verbose: + configs = _find_text_search_configs(cur, pattern) + + if configs: + for oid, cfgname, nspname, prsname, pnspname in configs: + extension = f'''\nText search configuration "{nspname}.{cfgname}"''' + parser = f'''\nParser: "{pnspname}.{prsname}"''' + title = extension + parser + cur, headers, status = _fetch_oid_details(cur, oid) + yield title, cur, headers, status + else: + yield ( + None, + None, + None, + 'Did not find any results for pattern "{}".'.format(pattern), + ) + return + + sql = """ + SELECT n.nspname AS schema, + c.cfgname AS name, + pg_catalog.obj_description(c.oid, 'pg_ts_config') AS description + FROM pg_catalog.pg_ts_config c + LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.cfgnamespace + """ + + params = {} + if pattern: + _, schema = sql_name_pattern(pattern) + sql += "WHERE c.cfgname ~ %(cfgname)s" + params["cfgname"] = schema + + sql += " ORDER BY 1, 2" + log.debug("%s, %s", sql, params) + cur.execute(sql, params) + if cur.description: + headers = [titleize(x.name) for x in cur.description] + yield None, cur, headers, cur.statusmessage + + +@special_command( + "describe", "DESCRIBE [pattern]", "", hidden=True, case_sensitive=False +) +@special_command( + "\\d", "\\d[+] [pattern]", "List or describe tables, views and sequences." +) +def describe_table_details(cur, pattern, verbose): + """ + Returns (title, rows, headers, status) + """ + + # This is a simple \d[+] command. No table name to follow. + if not pattern: + return list_objects(cur, pattern, verbose, ["r", "p", "v", "m", "S", "f", ""]) + + # This is a \d <tablename> command. A royal pain in the ass. + schema, relname = sql_name_pattern(pattern) + where = [] + params = {} + + if schema: + where.append("n.nspname ~ %(nspname)s") + params["nspname"] = schema + else: + where.append("pg_catalog.pg_table_is_visible(c.oid)") + + if relname: + where.append("c.relname OPERATOR(pg_catalog.~) %(relname)s") + params["relname"] = relname + + sql = ( + """SELECT c.oid, n.nspname, c.relname + FROM pg_catalog.pg_class c + LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace + """ + + ("WHERE " + " AND ".join(where) if where else "") + + """ + ORDER BY 2,3""" + ) + # Execute the sql, get the results and call describe_one_table_details on each table. + + log.debug("%s, %s", sql, params) + cur.execute(sql, params) + if not (cur.rowcount > 0): + return [(None, None, None, f"Did not find any relation named {pattern}.")] + + results = [] + for oid, nspname, relname in cur.fetchall(): + results.append(describe_one_table_details(cur, nspname, relname, oid, verbose)) + + return results + + +def describe_one_table_details(cur, schema_name, relation_name, oid, verbose): + if verbose and cur.connection.info.server_version >= 80200: + suffix = """pg_catalog.array_to_string(c.reloptions || array(select + 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')""" + else: + suffix = "''" + + if cur.connection.info.server_version >= 120000: + relhasoids = "false as relhasoids" + else: + relhasoids = "c.relhasoids" + + if cur.connection.info.server_version >= 100000: + sql = f"""SELECT c.relchecks, c.relkind, c.relhasindex, + c.relhasrules, c.relhastriggers, {relhasoids}, + {suffix}, + c.reltablespace, + CASE WHEN c.reloftype = 0 THEN '' + ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text + END, + c.relpersistence, + c.relispartition + FROM pg_catalog.pg_class c + LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid) + WHERE c.oid = '{oid}'""" + + elif cur.connection.info.server_version > 90000: + sql = f"""SELECT c.relchecks, c.relkind, c.relhasindex, + c.relhasrules, c.relhastriggers, c.relhasoids, + {suffix}, + c.reltablespace, + CASE WHEN c.reloftype = 0 THEN '' + ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text + END, + c.relpersistence, + false as relispartition + FROM pg_catalog.pg_class c + LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid) + WHERE c.oid = '{oid}'""" + + elif cur.connection.info.server_version >= 80400: + sql = f"""SELECT c.relchecks, + c.relkind, + c.relhasindex, + c.relhasrules, + c.relhastriggers, + c.relhasoids, + {suffix}, + c.reltablespace, + '' AS reloftype, + 'p' AS relpersistence, + false as relispartition + FROM pg_catalog.pg_class c + LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid) + WHERE c.oid = '{oid}'""" + + else: + sql = f"""SELECT c.relchecks, + c.relkind, + c.relhasindex, + c.relhasrules, + c.reltriggers > 0 AS relhastriggers, + c.relhasoids, + {suffix}, + c.reltablespace, + '' AS reloftype, + 'p' AS relpersistence, + false as relispartition + FROM pg_catalog.pg_class c + LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid) + WHERE c.oid = '{oid}'""" + + # Create a namedtuple called tableinfo and match what's in describe.c + + log.debug(sql) + cur.execute(sql) + if cur.rowcount > 0: + tableinfo = TableInfo._make(cur.fetchone()) + else: + return None, None, None, f"Did not find any relation with OID {oid}." + + # If it's a seq, fetch it's value and store it for later. + if tableinfo.relkind == "S": + # Do stuff here. + sql = f'''SELECT * FROM "{schema_name}"."{relation_name}"''' + log.debug(sql) + cur.execute(sql) + if not (cur.rowcount > 0): + return None, None, None, "Something went wrong." + + seq_values = cur.fetchone() + + # Get column info + cols = 0 + att_cols = {} + sql = """SELECT a.attname, + pg_catalog.format_type(a.atttypid, a.atttypmod) + , (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid, true) for 128) + FROM pg_catalog.pg_attrdef d + WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) + , a.attnotnull""" + att_cols["attname"] = cols + cols += 1 + att_cols["atttype"] = cols + cols += 1 + att_cols["attrdef"] = cols + cols += 1 + att_cols["attnotnull"] = cols + cols += 1 + if cur.connection.info.server_version >= 90100: + sql += """,\n(SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t + WHERE c.oid = a.attcollation + AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation""" + else: + sql += ",\n NULL AS attcollation" + att_cols["attcollation"] = cols + cols += 1 + if cur.connection.info.server_version >= 100000: + sql += ",\n a.attidentity" + else: + sql += ",\n ''::pg_catalog.char AS attidentity" + att_cols["attidentity"] = cols + cols += 1 + if cur.connection.info.server_version >= 120000: + sql += ",\n a.attgenerated" + else: + sql += ",\n ''::pg_catalog.char AS attgenerated" + att_cols["attgenerated"] = cols + cols += 1 + # index, or partitioned index + if tableinfo.relkind == "i" or tableinfo.relkind == "I": + if cur.connection.info.server_version >= 110000: + sql += ( + ",\n CASE WHEN a.attnum <= (SELECT i.indnkeyatts FROM pg_catalog.pg_index i " + f"WHERE i.indexrelid = '{oid}') THEN 'yes' ELSE 'no' END AS is_key" + ) + att_cols["indexkey"] = cols + cols += 1 + sql += ",\n pg_catalog.pg_get_indexdef(a.attrelid, a.attnum, TRUE) AS indexdef" + else: + sql += """,\n NULL AS indexdef""" + att_cols["indexdef"] = cols + cols += 1 + if tableinfo.relkind == "f" and cur.connection.info.server_version >= 90200: + sql += """, CASE WHEN attfdwoptions IS NULL THEN '' ELSE '(' || + array_to_string(ARRAY(SELECT quote_ident(option_name) || ' ' + || quote_literal(option_value) FROM + pg_options_to_table(attfdwoptions)), ', ') || ')' END AS attfdwoptions""" + else: + sql += """, NULL AS attfdwoptions""" + att_cols["attfdwoptions"] = cols + cols += 1 + if verbose: + sql += """, a.attstorage""" + att_cols["attstorage"] = cols + cols += 1 + if ( + tableinfo.relkind == "r" + or tableinfo.relkind == "i" + or tableinfo.relkind == "I" + or tableinfo.relkind == "m" + or tableinfo.relkind == "f" + or tableinfo.relkind == "p" + ): + sql += ( + ",\n CASE WHEN a.attstattarget=-1 THEN " + "NULL ELSE a.attstattarget END AS attstattarget" + ) + att_cols["attstattarget"] = cols + cols += 1 + if ( + tableinfo.relkind == "r" + or tableinfo.relkind == "v" + or tableinfo.relkind == "m" + or tableinfo.relkind == "f" + or tableinfo.relkind == "p" + or tableinfo.relkind == "c" + ): + sql += ",\n pg_catalog.col_description(a.attrelid, a.attnum)" + att_cols["attdescr"] = cols + cols += 1 + + sql += f""" FROM pg_catalog.pg_attribute a WHERE a.attrelid = '{oid}' AND + a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum; """ + + log.debug(sql) + cur.execute(sql) + res = cur.fetchall() + + # Set the column names. + headers = ["Column", "Type"] + + show_modifiers = False + if ( + tableinfo.relkind == "r" + or tableinfo.relkind == "p" + or tableinfo.relkind == "v" + or tableinfo.relkind == "m" + or tableinfo.relkind == "f" + or tableinfo.relkind == "c" + ): + headers.append("Modifiers") + show_modifiers = True + + if tableinfo.relkind == "S": + headers.append("Value") + + if tableinfo.relkind == "i": + headers.append("Definition") + + if tableinfo.relkind == "f": + headers.append("FDW Options") + + if verbose: + headers.append("Storage") + if ( + tableinfo.relkind == "r" + or tableinfo.relkind == "m" + or tableinfo.relkind == "f" + ): + headers.append("Stats target") + # Column comments, if the relkind supports this feature. */ + if ( + tableinfo.relkind == "r" + or tableinfo.relkind == "v" + or tableinfo.relkind == "m" + or tableinfo.relkind == "c" + or tableinfo.relkind == "f" + ): + headers.append("Description") + + view_def = "" + # /* Check if table is a view or materialized view */ + if (tableinfo.relkind == "v" or tableinfo.relkind == "m") and verbose: + sql = f"""SELECT pg_catalog.pg_get_viewdef('{oid}'::pg_catalog.oid, true)""" + log.debug(sql) + cur.execute(sql) + if cur.rowcount > 0: + (view_def,) = cur.fetchone() + + # Prepare the cells of the table to print. + cells = [] + for i, row in enumerate(res): + cell = [] + cell.append(row[att_cols["attname"]]) # Column + cell.append(row[att_cols["atttype"]]) # Type + + if show_modifiers: + modifier = "" + if row[att_cols["attcollation"]]: + modifier += f" collate {row[att_cols['attcollation']]}" + if row[att_cols["attnotnull"]]: + modifier += " not null" + if row[att_cols["attrdef"]]: + modifier += f" default {row[att_cols['attrdef']]}" + if row[att_cols["attidentity"]] == "a": + modifier += " generated always as identity" + elif row[att_cols["attidentity"]] == "d": + modifier += " generated by default as identity" + elif row[att_cols["attgenerated"]] == "s": + modifier += f" generated always as ({row[att_cols['attrdef']]}) stored" + cell.append(modifier) + + # Sequence + if tableinfo.relkind == "S": + cell.append(seq_values[i]) + + # Index column + if tableinfo.relkind == "i": + cell.append(row[att_cols["indexdef"]]) + + # /* FDW options for foreign table column, only for 9.2 or later */ + if tableinfo.relkind == "f": + cell.append(att_cols["attfdwoptions"]) + + if verbose: + storage = row[att_cols["attstorage"]] + + if storage[0] == "p": + cell.append("plain") + elif storage[0] == "m": + cell.append("main") + elif storage[0] == "x": + cell.append("extended") + elif storage[0] == "e": + cell.append("external") + else: + cell.append("???") + + if ( + tableinfo.relkind == "r" + or tableinfo.relkind == "m" + or tableinfo.relkind == "f" + ): + cell.append(row[att_cols["attstattarget"]]) + + # /* Column comments, if the relkind supports this feature. */ + if ( + tableinfo.relkind == "r" + or tableinfo.relkind == "v" + or tableinfo.relkind == "m" + or tableinfo.relkind == "c" + or tableinfo.relkind == "f" + ): + cell.append(row[att_cols["attdescr"]]) + cells.append(cell) + + # Make Footers + + status = [] + if tableinfo.relkind == "i": + # /* Footer information about an index */ + + if cur.connection.info.server_version > 90000: + sql = f"""SELECT i.indisunique, + i.indisprimary, + i.indisclustered, + i.indisvalid, + (NOT i.indimmediate) AND EXISTS ( + SELECT 1 + FROM pg_catalog.pg_constraint + WHERE conrelid = i.indrelid + AND conindid = i.indexrelid + AND contype IN ('p','u','x') + AND condeferrable + ) AS condeferrable, + (NOT i.indimmediate) AND EXISTS ( + SELECT 1 + FROM pg_catalog.pg_constraint + WHERE conrelid = i.indrelid + AND conindid = i.indexrelid + AND contype IN ('p','u','x') + AND condeferred + ) AS condeferred, + a.amname, + c2.relname, + pg_catalog.pg_get_expr(i.indpred, i.indrelid, true) + FROM pg_catalog.pg_index i, + pg_catalog.pg_class c, + pg_catalog.pg_class c2, + pg_catalog.pg_am a + WHERE i.indexrelid = c.oid + AND c.oid = '{oid}' + AND c.relam = a.oid + AND i.indrelid = c2.oid; + """ + else: + sql = f"""SELECT i.indisunique, + i.indisprimary, + i.indisclustered, + 't' AS indisvalid, + 'f' AS condeferrable, + 'f' AS condeferred, + a.amname, + c2.relname, + pg_catalog.pg_get_expr(i.indpred, i.indrelid, true) + FROM pg_catalog.pg_index i, + pg_catalog.pg_class c, + pg_catalog.pg_class c2, + pg_catalog.pg_am a + WHERE i.indexrelid = c.oid + AND c.oid = '{oid}' + AND c.relam = a.oid + AND i.indrelid = c2.oid; + """ + + log.debug(sql) + cur.execute(sql) + + ( + indisunique, + indisprimary, + indisclustered, + indisvalid, + deferrable, + deferred, + indamname, + indtable, + indpred, + ) = cur.fetchone() + + if indisprimary: + status.append("primary key, ") + elif indisunique: + status.append("unique, ") + status.append(f"{indamname}, ") + + # /* we assume here that index and table are in same schema */ + status.append(f'''for table "{schema_name}.{indtable}"''') + + if indpred: + status.append(f", predicate ({indpred})") + + if indisclustered: + status.append(", clustered") + + if not indisvalid: + status.append(", invalid") + + if deferrable: + status.append(", deferrable") + + if deferred: + status.append(", initially deferred") + + status.append("\n") + # add_tablespace_footer(&cont, tableinfo.relkind, + # tableinfo.tablespace, true); + + elif tableinfo.relkind == "S": + # /* Footer information about a sequence */ + # /* Get the column that owns this sequence */ + sql = ( + "SELECT pg_catalog.quote_ident(nspname) || '.' ||" + "\n pg_catalog.quote_ident(relname) || '.' ||" + "\n pg_catalog.quote_ident(attname)" + "\nFROM pg_catalog.pg_class c" + "\nINNER JOIN pg_catalog.pg_depend d ON c.oid=d.refobjid" + "\nINNER JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace" + "\nINNER JOIN pg_catalog.pg_attribute a ON (" + "\n a.attrelid=c.oid AND" + "\n a.attnum=d.refobjsubid)" + "\nWHERE d.classid='pg_catalog.pg_class'::pg_catalog.regclass" + "\n AND d.refclassid='pg_catalog.pg_class'::pg_catalog.regclass" + f"\n AND d.objid={oid} \n AND d.deptype='a'" + ) + + log.debug(sql) + cur.execute(sql) + result = cur.fetchone() + if result: + status.append(f"Owned by: {result[0]}") + + # /* + # * If we get no rows back, don't show anything (obviously). We should + # * never get more than one row back, but if we do, just ignore it and + # * don't print anything. + # */ + + elif ( + tableinfo.relkind == "r" + or tableinfo.relkind == "p" + or tableinfo.relkind == "m" + or tableinfo.relkind == "f" + ): + # /* Footer information about a table */ + + if tableinfo.hasindex: + if cur.connection.info.server_version > 90000: + sql = f"""SELECT c2.relname, + i.indisprimary, + i.indisunique, + i.indisclustered, + i.indisvalid, + pg_catalog.pg_get_indexdef(i.indexrelid, 0, true), + pg_catalog.pg_get_constraintdef(con.oid, true), + contype, + condeferrable, + condeferred, + c2.reltablespace + FROM pg_catalog.pg_class c, + pg_catalog.pg_class c2, + pg_catalog.pg_index i + LEFT JOIN pg_catalog.pg_constraint con + ON conrelid = i.indrelid + AND conindid = i.indexrelid + AND contype IN ('p','u','x') + WHERE c.oid = '{oid}' + AND c.oid = i.indrelid + AND i.indexrelid = c2.oid + ORDER BY i.indisprimary DESC, + i.indisunique DESC, + c2.relname; + """ + else: + sql = f"""SELECT c2.relname, + i.indisprimary, + i.indisunique, + i.indisclustered, + 't' AS indisvalid, + pg_catalog.pg_get_indexdef(i.indexrelid, 0, true), + pg_catalog.pg_get_constraintdef(con.oid, true), + contype, + condeferrable, + condeferred, + c2.reltablespace + FROM pg_catalog.pg_class c, + pg_catalog.pg_class c2, + pg_catalog.pg_index i + LEFT JOIN pg_catalog.pg_constraint con + ON conrelid = i.indrelid + AND contype IN ('p','u','x') + WHERE c.oid = '{oid}' + AND c.oid = i.indrelid + AND i.indexrelid = c2.oid + ORDER BY i.indisprimary DESC, + i.indisunique DESC, + c2.relname; + """ + + log.debug(sql) + result = cur.execute(sql) + + if cur.rowcount > 0: + status.append("Indexes:\n") + for row in cur: + # /* untranslated indextname */ + status.append(f''' "{row[0]}"''') + + # /* If exclusion constraint, print the constraintdef */ + if row[7] == "x": + status.append(" ") + status.append(row[6]) + else: + # /* Label as primary key or unique (but not both) */ + if row[1]: + status.append(" PRIMARY KEY,") + elif row[2]: + if row[7] == "u": + status.append(" UNIQUE CONSTRAINT,") + else: + status.append(" UNIQUE,") + + # /* Everything after "USING" is echoed verbatim */ + indexdef = row[5] + usingpos = indexdef.find(" USING ") + if usingpos >= 0: + indexdef = indexdef[(usingpos + 7) :] + status.append(f" {indexdef}") + + # /* Need these for deferrable PK/UNIQUE indexes */ + if row[8]: + status.append(" DEFERRABLE") + + if row[9]: + status.append(" INITIALLY DEFERRED") + + # /* Add these for all cases */ + if row[3]: + status.append(" CLUSTER") + + if not row[4]: + status.append(" INVALID") + + status.append("\n") + # printTableAddFooter(&cont, buf.data); + + # /* Print tablespace of the index on the same line */ + # add_tablespace_footer(&cont, 'i', + # atooid(PQgetvalue(result, i, 10)), + # false); + + # /* print table (and column) check constraints */ + if tableinfo.checks: + sql = ( + "SELECT r.conname, " + "pg_catalog.pg_get_constraintdef(r.oid, true)\n" + "FROM pg_catalog.pg_constraint r\n" + f"WHERE r.conrelid = '{oid}' AND r.contype = 'c'\n" + "ORDER BY 1;" + ) + log.debug(sql) + cur.execute(sql) + if cur.rowcount > 0: + status.append("Check constraints:\n") + for row in cur: + # /* untranslated contraint name and def */ + status.append(f""" "{row[0]}" {row[1]}""") + status.append("\n") + + # /* print foreign-key constraints (there are none if no triggers) */ + if tableinfo.hastriggers: + sql = ( + "SELECT conname,\n" + " pg_catalog.pg_get_constraintdef(r.oid, true) as condef\n" + "FROM pg_catalog.pg_constraint r\n" + f"WHERE r.conrelid = '{oid}' AND r.contype = 'f' ORDER BY 1;" + ) + log.debug(sql) + cur.execute(sql) + if cur.rowcount > 0: + status.append("Foreign-key constraints:\n") + for row in cur: + # /* untranslated constraint name and def */ + status.append(f""" "{row[0]}" {row[1]}\n""") + + # /* print incoming foreign-key references (none if no triggers) */ + if tableinfo.hastriggers: + sql = ( + "SELECT conrelid::pg_catalog.regclass, conname,\n" + " pg_catalog.pg_get_constraintdef(c.oid, true) as condef\n" + "FROM pg_catalog.pg_constraint c\n" + f"WHERE c.confrelid = '{oid}' AND c.contype = 'f' ORDER BY 1;" + ) + log.debug(sql) + cur.execute(sql) + if cur.rowcount > 0: + status.append("Referenced by:\n") + for row in cur: + status.append( + f""" TABLE "{row[0]}" CONSTRAINT "{row[1]}" {row[2]}\n""" + ) + + # /* print rules */ + if tableinfo.hasrules and tableinfo.relkind != "m": + sql = ( + "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true)), " + "ev_enabled\n" + "FROM pg_catalog.pg_rewrite r\n" + f"WHERE r.ev_class = '{oid}' ORDER BY 1;" + ) + log.debug(sql) + cur.execute(sql) + if cur.rowcount > 0: + for category in range(4): + have_heading = False + for row in cur: + if category == 0 and row[2] == "O": + list_rule = True + elif category == 1 and row[2] == "D": + list_rule = True + elif category == 2 and row[2] == "A": + list_rule = True + elif category == 3 and row[2] == "R": + list_rule = True + + if not list_rule: + continue + + if not have_heading: + if category == 0: + status.append("Rules:") + if category == 1: + status.append("Disabled rules:") + if category == 2: + status.append("Rules firing always:") + if category == 3: + status.append("Rules firing on replica only:") + have_heading = True + + # /* Everything after "CREATE RULE" is echoed verbatim */ + ruledef = row[1] + status.append(f" {ruledef}") + + # /* print partition info */ + if tableinfo.relispartition: + sql = ( + "select quote_ident(np.nspname) || '.' ||\n" + " quote_ident(cp.relname) || ' ' ||\n" + " pg_get_expr(cc.relpartbound, cc.oid, true) as partition_of,\n" + " pg_get_partition_constraintdef(cc.oid) as partition_constraint\n" + "from pg_inherits i\n" + "inner join pg_class cp\n" + "on cp.oid = i.inhparent\n" + "inner join pg_namespace np\n" + "on np.oid = cp.relnamespace\n" + "inner join pg_class cc\n" + "on cc.oid = i.inhrelid\n" + "inner join pg_namespace nc\n" + "on nc.oid = cc.relnamespace\n" + f"where cc.oid = {oid}" + ) + log.debug(sql) + cur.execute(sql) + for row in cur: + status.append(f"Partition of: {row[0]}\n") + status.append(f"Partition constraint: {row[1]}\n") + + if tableinfo.relkind == "p": + # /* print partition key */ + sql = f"select pg_get_partkeydef({oid})" + log.debug(sql) + cur.execute(sql) + for row in cur: + status.append(f"Partition key: {row[0]}\n") + # /* print list of partitions */ + sql = ( + "select quote_ident(n.nspname) || '.' ||\n" + " quote_ident(c.relname) || ' ' ||\n" + " pg_get_expr(c.relpartbound, c.oid, true)\n" + "from pg_inherits i\n" + "inner join pg_class c\n" + "on c.oid = i.inhrelid\n" + "inner join pg_namespace n\n" + "on n.oid = c.relnamespace\n" + f"where i.inhparent = {oid} order by 1" + ) + log.debug(sql) + cur.execute(sql) + if cur.rowcount > 0: + if verbose: + first = True + for row in cur: + if first: + status.append(f"Partitions: {row[0]}\n") + first = False + else: + status.append(f" {row[0]}\n") + else: + status.append( + "Number of partitions %i: (Use \\d+ to list them.)\n" + % cur.rowcount + ) + + if view_def: + # /* Footer information about a view */ + status.append("View definition:\n") + status.append(f"{view_def} \n") + + # /* print rules */ + if tableinfo.hasrules: + sql = ( + "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true))\n" + "FROM pg_catalog.pg_rewrite r\n" + f"WHERE r.ev_class = '{oid}' AND r.rulename != '_RETURN' ORDER BY 1;" + ) + + log.debug(sql) + cur.execute(sql) + if cur.rowcount > 0: + status.append("Rules:\n") + for row in cur: + # /* Everything after "CREATE RULE" is echoed verbatim */ + ruledef = row[1] + status.append(f" {ruledef}\n") + + # /* + # * Print triggers next, if any (but only user-defined triggers). This + # * could apply to either a table or a view. + # */ + if tableinfo.hastriggers: + if cur.connection.info.server_version > 90000: + sql = f"""SELECT t.tgname, + pg_catalog.pg_get_triggerdef(t.oid, true), + t.tgenabled + FROM pg_catalog.pg_trigger t + WHERE t.tgrelid = '{oid}' AND NOT t.tgisinternal + ORDER BY 1 + """ + else: + sql = f"""SELECT t.tgname, + pg_catalog.pg_get_triggerdef(t.oid), + t.tgenabled + FROM pg_catalog.pg_trigger t + WHERE t.tgrelid = '{oid}' + ORDER BY 1 + """ + + log.debug(sql) + cur.execute(sql) + if cur.rowcount > 0: + # /* + # * split the output into 4 different categories. Enabled triggers, + # * disabled triggers and the two special ALWAYS and REPLICA + # * configurations. + # */ + for category in range(4): + have_heading = False + list_trigger = False + for row in cur: + # /* + # * Check if this trigger falls into the current category + # */ + tgenabled = row[2] + if category == 0: + if tgenabled == "O" or tgenabled == True: + list_trigger = True + elif category == 1: + if tgenabled == "D" or tgenabled == False: + list_trigger = True + elif category == 2: + if tgenabled == "A": + list_trigger = True + elif category == 3: + if tgenabled == "R": + list_trigger = True + if list_trigger == False: + continue + + # /* Print the category heading once */ + if not have_heading: + if category == 0: + status.append("Triggers:") + elif category == 1: + status.append("Disabled triggers:") + elif category == 2: + status.append("Triggers firing always:") + elif category == 3: + status.append("Triggers firing on replica only:") + status.append("\n") + have_heading = True + + # /* Everything after "TRIGGER" is echoed verbatim */ + tgdef = row[1] + triggerpos = tgdef.find(" TRIGGER ") + if triggerpos >= 0: + tgdef = triggerpos + 9 + + status.append(f" {row[1][tgdef:]}\n") + + # /* + # * Finish printing the footer information about a table. + # */ + if tableinfo.relkind == "r" or tableinfo.relkind == "m" or tableinfo.relkind == "f": + # /* print foreign server name */ + if tableinfo.relkind == "f": + # /* Footer information about foreign table */ + sql = f"""SELECT s.srvname,\n + array_to_string(ARRAY(SELECT + quote_ident(option_name) || ' ' || + quote_literal(option_value) FROM + pg_options_to_table(ftoptions)), ', ') + FROM pg_catalog.pg_foreign_table f,\n + pg_catalog.pg_foreign_server s\n + WHERE f.ftrelid = {oid} AND s.oid = f.ftserver;""" + log.debug(sql) + cur.execute(sql) + row = cur.fetchone() + + # /* Print server name */ + status.append(f"Server: {row[0]}\n") + + # /* Print per-table FDW options, if any */ + if row[1]: + status.append(f"FDW Options: ({row[1]})\n") + + # /* print inherited tables */ + if not tableinfo.relispartition: + sql = ( + "SELECT c.oid::pg_catalog.regclass\n" + "FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i\n" + "WHERE c.oid = i.inhparent\n" + f" AND i.inhrelid = '{oid}'\n" + "ORDER BY inhseqno" + ) + log.debug(sql) + cur.execute(sql) + spacer = "" + if cur.rowcount > 0: + status.append("Inherits") + spacer = ":" + trailer = ",\n" + for idx, row in enumerate(cur, 1): + if idx == 2: + spacer = " " * (len("Inherits") + 1) + if idx == cur.rowcount: + trailer = "\n" + status.append(f"{spacer} {row[0]}{trailer}") + + # /* print child tables */ + if cur.connection.info.server_version > 90000: + sql = f"""SELECT c.oid::pg_catalog.regclass + FROM pg_catalog.pg_class c, + pg_catalog.pg_inherits i + WHERE c.oid = i.inhrelid + AND i.inhparent = '{oid}' + ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text; + """ + else: + sql = f"""SELECT c.oid::pg_catalog.regclass + FROM pg_catalog.pg_class c, + pg_catalog.pg_inherits i + WHERE c.oid = i.inhrelid + AND i.inhparent = '{oid}' + ORDER BY c.oid; + """ + + log.debug(sql) + cur.execute(sql) + + if not verbose: + # /* print the number of child tables, if any */ + if cur.rowcount > 0: + status.append( + "Number of child tables: %d (Use \\d+ to list" + " them.)\n" % cur.rowcount + ) + else: + if cur.rowcount > 0: + status.append("Child tables") + + spacer = ":" + trailer = ",\n" + # /* display the list of child tables */ + for idx, row in enumerate(cur, 1): + if idx == 2: + spacer = " " * (len("Child tables") + 1) + if idx == cur.rowcount: + trailer = "\n" + status.append(f"{spacer} {row[0]}{trailer}") + + # /* Table type */ + if tableinfo.reloftype: + status.append(f"Typed table of type: {tableinfo.reloftype}\n") + + # /* OIDs, if verbose and not a materialized view */ + if verbose and tableinfo.relkind != "m": + status.append(f"Has OIDs: {'yes' if tableinfo.hasoids else 'no'}\n") + + # /* Tablespace info */ + # add_tablespace_footer(&cont, tableinfo.relkind, tableinfo.tablespace, + # true); + + # /* reloptions, if verbose */ + if verbose and tableinfo.reloptions: + status.append(f"Options: {tableinfo.reloptions}\n") + + return (None, cells, headers, "".join(status)) + + +def sql_name_pattern(pattern): + """ + Takes a wildcard-pattern and converts to an appropriate SQL pattern to be + used in a WHERE clause. + + Returns: schema_pattern, table_pattern + + >>> sql_name_pattern('foo*."b""$ar*"') + ('^(foo.*)$', '^(b"\\\\$ar\\\\*)$') + """ + + inquotes = False + relname = "" + schema = None + pattern_len = len(pattern) + i = 0 + + while i < pattern_len: + c = pattern[i] + if c == '"': + if inquotes and i + 1 < pattern_len and pattern[i + 1] == '"': + relname += '"' + i += 1 + else: + inquotes = not inquotes + elif not inquotes and c.isupper(): + relname += c.lower() + elif not inquotes and c == "*": + relname += ".*" + elif not inquotes and c == "?": + relname += "." + elif not inquotes and c == ".": + # Found schema/name separator, move current pattern to schema + schema = relname + relname = "" + else: + # Dollar is always quoted, whether inside quotes or not. + if c == "$" or inquotes and c in "|*+?()[]{}.^\\": + relname += "\\" + relname += c + i += 1 + + if relname: + relname = "^(" + relname + ")$" + + if schema: + schema = "^(" + schema + ")$" + + return schema, relname + + +class _FakeCursor(list): + "Minimalistic wrapper simulating a real cursor, as far as pgcli is concerned." + + def rowcount(self): + return len(self) + + +@special_command("\\sf", "\\sf[+] FUNCNAME", "Show a function's definition.") +def show_function_definition(cur, pattern, verbose): + params = {"pattern": pattern} + if "(" in pattern: + sql = "SELECT %(pattern)s::pg_catalog.regprocedure::pg_catalog.oid" + else: + sql = "SELECT %(pattern)s::pg_catalog.regproc::pg_catalog.oid" + log.debug("%s, %s", sql, params) + cur.execute(sql, params) + (foid,) = cur.fetchone() + + params = {"foid": foid} + sql = "SELECT pg_catalog.pg_get_functiondef(%(foid)s) as source" + log.debug("%s, %s", sql, params) + cur.execute(sql, params) + if cur.description: + headers = [titleize(x.name) for x in cur.description] + if verbose: + (source,) = cur.fetchone() + rows = _FakeCursor() + rown = None + for row in source.splitlines(): + if rown is None: + if row.startswith("AS "): + rown = 1 + else: + rown += 1 + rown_v = "" if rown is None else rown + rows.append(f"{rown_v:<7} {row}") + cur = [("\n".join(rows) + "\n",)] + else: + headers = None + return [(None, cur, headers, None)] + + +@special_command("\\!", "\\! [command]", "Pass commands to shell.") +def shell_command(cur, pattern, verbose): + cur, headers = [], [] + params = shlex.split(pattern) + return [(None, cur, headers, subprocess.call(params))] + + +@special_command("\\dE", "\\dE[+] [pattern]", "List foreign tables.", aliases=()) +def list_foreign_tables(cur, pattern, verbose): + params = {} + query = SQL( + """ + SELECT n.nspname as schema, + c.relname as name, + CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'table' WHEN 'I' THEN 'index' END as type, + pg_catalog.pg_get_userbyid(c.relowner) as owner + {verbose_cols} + FROM pg_catalog.pg_class c + LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace + WHERE c.relkind IN ('f','') + AND n.nspname <> 'pg_catalog' + AND n.nspname <> 'information_schema' + AND n.nspname !~ '^pg_toast' + AND pg_catalog.pg_table_is_visible(c.oid) + {filter} + ORDER BY 1,2; + """ + ) + + if verbose: + params["verbose_cols"] = SQL( + """ + , pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as size, + pg_catalog.obj_description(c.oid, 'pg_class') as description """ + ) + else: + params["verbose_cols"] = SQL("") + + if pattern: + _, tbl_name = sql_name_pattern(pattern) + params["filter"] = SQL(" AND c.relname OPERATOR(pg_catalog.~) {} ").format( + f"^({tbl_name})$" + ) + else: + params["filter"] = SQL("") + + formatted_query = query.format(**params) + log.debug(formatted_query.as_string(cur)) + cur.execute(formatted_query) + if cur.description: + headers = [titleize(x.name) for x in cur.description] + return [(None, cur, headers, cur.statusmessage)] + else: + return [(None, None, None, cur.statusmessage)] + + +def titleize(column): + return column[0].capitalize() + " ".join(c for c in column[1:].split("_")) diff --git a/pgspecial/help/__init__.py b/pgspecial/help/__init__.py new file mode 100644 index 0000000..e69de29 --- /dev/null +++ b/pgspecial/help/__init__.py diff --git a/pgspecial/help/commands.py b/pgspecial/help/commands.py new file mode 100644 index 0000000..033d875 --- /dev/null +++ b/pgspecial/help/commands.py @@ -0,0 +1,666 @@ +helpcommands = { + "ABORT": { + "description": "Description\nABORT rolls back the current transaction and causes", + "synopsis": "\nABORT [ WORK | TRANSACTION ]\n", + }, + "ALTER AGGREGATE": { + "description": "Description\nALTER AGGREGATE changes the definition of an", + "synopsis": "\nALTER AGGREGATE name ( aggregate_signature ) RENAME TO new_name\nALTER AGGREGATE name ( aggregate_signature )\n OWNER TO { new_owner | CURRENT_USER | SESSION_USER }\nALTER AGGREGATE name ( aggregate_signature ) SET SCHEMA new_schema\nwhere aggregate_signature is:\n\n* |\n[ argmode ] [ argname ] argtype [ , ... ] |\n[ [ argmode ] [ argname ] argtype [ , ... ] ] ORDER BY [ argmode ] [ argname ] argtype [ , ... ]\n", + }, + "ALTER COLLATION": { + "description": "Description\nALTER COLLATION changes the definition of a", + "synopsis": "\nALTER COLLATION name RENAME TO new_name\nALTER COLLATION name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }\nALTER COLLATION name SET SCHEMA new_schema\n", + }, + "ALTER CONVERSION": { + "description": "Description\nALTER CONVERSION changes the definition of a", + "synopsis": "\nALTER CONVERSION name RENAME TO new_name\nALTER CONVERSION name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }\nALTER CONVERSION name SET SCHEMA new_schema\n", + }, + "ALTER DATABASE": { + "description": "Description\nALTER DATABASE changes the attributes", + "synopsis": "\nALTER DATABASE name [ [ WITH ] option [ ... ] ]\n\nwhere option can be:\n\n ALLOW_CONNECTIONS allowconn\n CONNECTION LIMIT connlimit\n IS_TEMPLATE istemplate\n\nALTER DATABASE name RENAME TO new_name\n\nALTER DATABASE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }\n\nALTER DATABASE name SET TABLESPACE new_tablespace\n\nALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT }\nALTER DATABASE name SET configuration_parameter FROM CURRENT\nALTER DATABASE name RESET configuration_parameter\nALTER DATABASE name RESET ALL\n", + }, + "ALTER DEFAULT PRIVILEGES": { + "description": "Description\nALTER DEFAULT PRIVILEGES allows you to set the privileges", + "synopsis": "\nALTER DEFAULT PRIVILEGES\n [ FOR { ROLE | USER } target_role [, ...] ]\n [ IN SCHEMA schema_name [, ...] ]\n abbreviated_grant_or_revoke\nwhere abbreviated_grant_or_revoke is one of:\n\nGRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }\n [, ...] | ALL [ PRIVILEGES ] }\n ON TABLES\n TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]\n\nGRANT { { USAGE | SELECT | UPDATE }\n [, ...] | ALL [ PRIVILEGES ] }\n ON SEQUENCES\n TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]\n\nGRANT { EXECUTE | ALL [ PRIVILEGES ] }\n ON FUNCTIONS\n TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]\n\nGRANT { USAGE | ALL [ PRIVILEGES ] }\n ON TYPES\n TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]\n\nREVOKE [ GRANT OPTION FOR ]\n { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }\n [, ...] | ALL [ PRIVILEGES ] }\n ON TABLES\n FROM { [ GROUP ] role_name | PUBLIC } [, ...]\n [ CASCADE | RESTRICT ]\n\nREVOKE [ GRANT OPTION FOR ]\n { { USAGE | SELECT | UPDATE }\n [, ...] | ALL [ PRIVILEGES ] }\n ON SEQUENCES\n FROM { [ GROUP ] role_name | PUBLIC } [, ...]\n [ CASCADE | RESTRICT ]\n\nREVOKE [ GRANT OPTION FOR ]\n { EXECUTE | ALL [ PRIVILEGES ] }\n ON FUNCTIONS\n FROM { [ GROUP ] role_name | PUBLIC } [, ...]\n [ CASCADE | RESTRICT ]\n\nREVOKE [ GRANT OPTION FOR ]\n { USAGE | ALL [ PRIVILEGES ] }\n ON TYPES\n FROM { [ GROUP ] role_name | PUBLIC } [, ...]\n [ CASCADE | RESTRICT ]\n", + }, + "ALTER DOMAIN": { + "description": "Description\nALTER DOMAIN changes the definition of an existing domain.", + "synopsis": "\nALTER DOMAIN name\n { SET DEFAULT expression | DROP DEFAULT }\nALTER DOMAIN name\n { SET | DROP } NOT NULL\nALTER DOMAIN name\n ADD domain_constraint [ NOT VALID ]\nALTER DOMAIN name\n DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ]\nALTER DOMAIN name\n RENAME CONSTRAINT constraint_name TO new_constraint_name\nALTER DOMAIN name\n VALIDATE CONSTRAINT constraint_name\nALTER DOMAIN name\n OWNER TO { new_owner | CURRENT_USER | SESSION_USER }\nALTER DOMAIN name\n RENAME TO new_name\nALTER DOMAIN name\n SET SCHEMA new_schema\n", + }, + "ALTER EVENT TRIGGER": { + "description": "Description\nALTER EVENT TRIGGER changes properties of an", + "synopsis": "\nALTER EVENT TRIGGER name DISABLE\nALTER EVENT TRIGGER name ENABLE [ REPLICA | ALWAYS ]\nALTER EVENT TRIGGER name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }\nALTER EVENT TRIGGER name RENAME TO new_name\n", + }, + "ALTER EXTENSION": { + "description": "Description\nALTER EXTENSION changes the definition of an installed", + "synopsis": "\nALTER EXTENSION name UPDATE [ TO new_version ]\nALTER EXTENSION name SET SCHEMA new_schema\nALTER EXTENSION name ADD member_object\nALTER EXTENSION name DROP member_object\nwhere member_object is:\n\n AGGREGATE aggregate_name ( aggregate_signature ) |\n CAST (source_type AS target_type) |\n COLLATION object_name |\n CONVERSION object_name |\n DOMAIN object_name |\n EVENT TRIGGER object_name |\n FOREIGN DATA WRAPPER object_name |\n FOREIGN TABLE object_name |\n FUNCTION function_name ( [ [ argmode ] [ argname ] argtype [, ...] ] ) |\n MATERIALIZED VIEW object_name |\n OPERATOR operator_name (left_type, right_type) |\n OPERATOR CLASS object_name USING index_method |\n OPERATOR FAMILY object_name USING index_method |\n [ PROCEDURAL ] LANGUAGE object_name |\n SCHEMA object_name |\n SEQUENCE object_name |\n SERVER object_name |\n TABLE object_name |\n TEXT SEARCH CONFIGURATION object_name |\n TEXT SEARCH DICTIONARY object_name |\n TEXT SEARCH PARSER object_name |\n TEXT SEARCH TEMPLATE object_name |\n TRANSFORM FOR type_name LANGUAGE lang_name |\n TYPE object_name |\n VIEW object_name\nand aggregate_signature is:\n\n* |\n[ argmode ] [ argname ] argtype [ , ... ] |\n[ [ argmode ] [ argname ] argtype [ , ... ] ] ORDER BY [ argmode ] [ argname ] argtype [ , ... ]\n", + }, + "ALTER FOREIGN DATA WRAPPER": { + "description": "Description\nALTER FOREIGN DATA WRAPPER changes the", + "synopsis": "\nALTER FOREIGN DATA WRAPPER name\n [ HANDLER handler_function | NO HANDLER ]\n [ VALIDATOR validator_function | NO VALIDATOR ]\n [ OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ]) ]\nALTER FOREIGN DATA WRAPPER name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }\nALTER FOREIGN DATA WRAPPER name RENAME TO new_name\n", + }, + "ALTER FOREIGN TABLE": { + "description": "Description\nALTER FOREIGN TABLE changes the definition of an", + "synopsis": "\nALTER FOREIGN TABLE [ IF EXISTS ] [ ONLY ] name [ * ]\n action [, ... ]\nALTER FOREIGN TABLE [ IF EXISTS ] [ ONLY ] name [ * ]\n RENAME [ COLUMN ] column_name TO new_column_name\nALTER FOREIGN TABLE [ IF EXISTS ] name\n RENAME TO new_name\nALTER FOREIGN TABLE [ IF EXISTS ] name\n SET SCHEMA new_schema\nwhere action is one of:\n\n ADD [ COLUMN ] column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]\n DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]\n ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ]\n ALTER [ COLUMN ] column_name SET DEFAULT expression\n ALTER [ COLUMN ] column_name DROP DEFAULT\n ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL\n ALTER [ COLUMN ] column_name SET STATISTICS integer\n ALTER [ COLUMN ] column_name SET ( attribute_option = value [, ... ] )\n ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] )\n ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }\n ALTER [ COLUMN ] column_name OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ])\n ADD table_constraint [ NOT VALID ]\n VALIDATE CONSTRAINT constraint_name\n DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ]\n DISABLE TRIGGER [ trigger_name | ALL | USER ]\n ENABLE TRIGGER [ trigger_name | ALL | USER ]\n ENABLE REPLICA TRIGGER trigger_name\n ENABLE ALWAYS TRIGGER trigger_name\n SET WITH OIDS\n SET WITHOUT OIDS\n INHERIT parent_table\n NO INHERIT parent_table\n OWNER TO { new_owner | CURRENT_USER | SESSION_USER }\n OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ])\n", + }, + "ALTER FUNCTION": { + "description": "Description\nALTER FUNCTION changes the definition of a", + "synopsis": "\nALTER FUNCTION name ( [ [ argmode ] [ argname ] argtype [, ...] ] )\n action [ ... ] [ RESTRICT ]\nALTER FUNCTION name ( [ [ argmode ] [ argname ] argtype [, ...] ] )\n RENAME TO new_name\nALTER FUNCTION name ( [ [ argmode ] [ argname ] argtype [, ...] ] )\n OWNER TO { new_owner | CURRENT_USER | SESSION_USER }\nALTER FUNCTION name ( [ [ argmode ] [ argname ] argtype [, ...] ] )\n SET SCHEMA new_schema\nwhere action is one of:\n\n CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT\n IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF\n [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER\n PARALLEL { UNSAFE | RESTRICTED | SAFE }\n COST execution_cost\n ROWS result_rows\n SET configuration_parameter { TO | = } { value | DEFAULT }\n SET configuration_parameter FROM CURRENT\n RESET configuration_parameter\n RESET ALL\n", + }, + "ALTER GROUP": { + "description": "Description\nALTER GROUP changes the attributes of a user group.", + "synopsis": "\nALTER GROUP role_specification ADD USER user_name [, ... ]\nALTER GROUP role_specification DROP USER user_name [, ... ]\n\nwhere role_specification can be:\nrole_name\n | CURRENT_USER\n | SESSION_USER\n\nALTER GROUP group_name RENAME TO new_name\n", + }, + "ALTER INDEX": { + "description": "Description\nALTER INDEX changes the definition of an existing index.", + "synopsis": "\nALTER INDEX [ IF EXISTS ] name RENAME TO new_name\nALTER INDEX [ IF EXISTS ] name SET TABLESPACE tablespace_name\nALTER INDEX [ IF EXISTS ] name SET ( storage_parameter = value [, ... ] )\nALTER INDEX [ IF EXISTS ] name RESET ( storage_parameter [, ... ] )\nALTER INDEX ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ]\n SET TABLESPACE new_tablespace [ NOWAIT ]\n", + }, + "ALTER LANGUAGE": { + "description": "Description\nALTER LANGUAGE changes the definition of a", + "synopsis": "\nALTER [ PROCEDURAL ] LANGUAGE name RENAME TO new_name\nALTER [ PROCEDURAL ] LANGUAGE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }\n", + }, + "ALTER LARGE OBJECT": { + "description": "Description\nALTER LARGE OBJECT changes the definition of a", + "synopsis": "\nALTER LARGE OBJECT large_object_oid OWNER TO { new_owner | CURRENT_USER | SESSION_USER }\n", + }, + "ALTER MATERIALIZED VIEW": { + "description": "Description\nALTER MATERIALIZED VIEW changes various auxiliary", + "synopsis": "\nALTER MATERIALIZED VIEW [ IF EXISTS ] name\naction [, ... ]\nALTER MATERIALIZED VIEW [ IF EXISTS ] name\n RENAME [ COLUMN ] column_name TO new_column_name\nALTER MATERIALIZED VIEW [ IF EXISTS ] name\n RENAME TO new_name\nALTER MATERIALIZED VIEW [ IF EXISTS ] name\n SET SCHEMA new_schema\nALTER MATERIALIZED VIEW ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ]\n SET TABLESPACE new_tablespace [ NOWAIT ]\n\nwhere action is one of:\n\n ALTER [ COLUMN ] column_name SET STATISTICS integer\n ALTER [ COLUMN ] column_name SET ( attribute_option = value [, ... ] )\n ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] )\n ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }\n CLUSTER ON index_name\n SET WITHOUT CLUSTER\n SET ( storage_parameter = value [, ... ] )\n RESET ( storage_parameter [, ... ] )\n OWNER TO { new_owner | CURRENT_USER | SESSION_USER }\n SET TABLESPACE new_tablespace\n", + }, + "ALTER OPCLASS": { + "description": "Description\nALTER OPERATOR CLASS changes the definition of", + "synopsis": "\nALTER OPERATOR CLASS name USING index_method\n RENAME TO new_name\n\nALTER OPERATOR CLASS name USING index_method\n OWNER TO { new_owner | CURRENT_USER | SESSION_USER }\n\nALTER OPERATOR CLASS name USING index_method\n SET SCHEMA new_schema\n", + }, + "ALTER OPERATOR": { + "description": "Description\nALTER OPERATOR changes the definition of", + "synopsis": "\nALTER OPERATOR name ( { left_type | NONE } , { right_type | NONE } )\n OWNER TO { new_owner | CURRENT_USER | SESSION_USER }\n\nALTER OPERATOR name ( { left_type | NONE } , { right_type | NONE } )\n SET SCHEMA new_schema\n\nALTER OPERATOR name ( { left_type | NONE } , { right_type | NONE } )\n SET ( { RESTRICT = { res_proc | NONE }\n | JOIN = { join_proc | NONE }\n } [, ... ] )\n", + }, + "ALTER OPFAMILY": { + "description": "Description\nALTER OPERATOR FAMILY changes the definition of", + "synopsis": "\nALTER OPERATOR FAMILY name USING index_method ADD\n { OPERATOR strategy_number operator_name ( op_type, op_type )\n [ FOR SEARCH | FOR ORDER BY sort_family_name ]\n | FUNCTION support_number [ ( op_type [ , op_type ] ) ]\n function_name ( argument_type [, ...] )\n } [, ... ]\n\nALTER OPERATOR FAMILY name USING index_method DROP\n { OPERATOR strategy_number ( op_type [ , op_type ] )\n | FUNCTION support_number ( op_type [ , op_type ] )\n } [, ... ]\n\nALTER OPERATOR FAMILY name USING index_method\n RENAME TO new_name\n\nALTER OPERATOR FAMILY name USING index_method\n OWNER TO { new_owner | CURRENT_USER | SESSION_USER }\n\nALTER OPERATOR FAMILY name USING index_method\n SET SCHEMA new_schema\n", + }, + "ALTER POLICY": { + "description": "Description\nALTER POLICY changes the ", + "synopsis": "\nALTER POLICY name ON table_name\n [ RENAME TO new_name ]\n [ TO { role_name | PUBLIC | CURRENT_USER | SESSION_USER } [, ...] ]\n [ USING ( using_expression ) ]\n [ WITH CHECK ( check_expression ) ]\n", + }, + "ALTER ROLE": { + "description": "Description\nALTER ROLE changes the attributes of a", + "synopsis": "\nALTER ROLE role_specification [ WITH ] option [ ... ]\n\nwhere option can be:\n\n SUPERUSER | NOSUPERUSER\n | CREATEDB | NOCREATEDB\n | CREATEROLE | NOCREATEROLE\n | CREATEUSER | NOCREATEUSER\n | INHERIT | NOINHERIT\n | LOGIN | NOLOGIN\n | REPLICATION | NOREPLICATION\n | BYPASSRLS | NOBYPASSRLS\n | CONNECTION LIMIT connlimit\n | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'\n | VALID UNTIL 'timestamp'\n\nALTER ROLE name RENAME TO new_name\n\nALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT }\nALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT\nALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] RESET configuration_parameter\nALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] RESET ALL\n\nwhere role_specification can be:\n\n [ GROUP ] role_name\n | CURRENT_USER\n | SESSION_USER\n", + }, + "ALTER RULE": { + "description": "Description\nALTER RULE changes properties of an existing", + "synopsis": "\nALTER RULE name ON table_name RENAME TO new_name\n", + }, + "ALTER SCHEMA": { + "description": "Description\nALTER SCHEMA changes the definition of a schema.", + "synopsis": "\nALTER SCHEMA name RENAME TO new_name\nALTER SCHEMA name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }\n", + }, + "ALTER SEQUENCE": { + "description": "Description\nALTER SEQUENCE changes the parameters of an existing", + "synopsis": "\nALTER SEQUENCE [ IF EXISTS ] name [ INCREMENT [ BY ] increment ]\n [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]\n [ START [ WITH ] start ]\n [ RESTART [ [ WITH ] restart ] ]\n [ CACHE cache ] [ [ NO ] CYCLE ]\n [ OWNED BY { table_name.column_name | NONE } ]\nALTER SEQUENCE [ IF EXISTS ] name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }\nALTER SEQUENCE [ IF EXISTS ] name RENAME TO new_name\nALTER SEQUENCE [ IF EXISTS ] name SET SCHEMA new_schema\n", + }, + "ALTER SERVER": { + "description": "Description\nALTER SERVER changes the definition of a foreign", + "synopsis": "\nALTER SERVER name [ VERSION 'new_version' ]\n [ OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ] ) ]\nALTER SERVER name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }\nALTER SERVER name RENAME TO new_name\n", + }, + "ALTER SYSTEM": { + "description": "Description\nALTER SYSTEM is used for changing server configuration", + "synopsis": "\nALTER SYSTEM SET configuration_parameter { TO | = } { value | 'value' | DEFAULT }\n\nALTER SYSTEM RESET configuration_parameter\nALTER SYSTEM RESET ALL\n", + }, + "ALTER TABLE": { + "description": "Description\nALTER TABLE changes the definition of an existing table.", + "synopsis": "\nALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]\n action [, ... ]\nALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]\n RENAME [ COLUMN ] column_name TO new_column_name\nALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]\n RENAME CONSTRAINT constraint_name TO new_constraint_name\nALTER TABLE [ IF EXISTS ] name\n RENAME TO new_name\nALTER TABLE [ IF EXISTS ] name\n SET SCHEMA new_schema\nALTER TABLE ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ]\n SET TABLESPACE new_tablespace [ NOWAIT ]\n\nwhere action is one of:\n\n ADD [ COLUMN ] [ IF NOT EXISTS ] column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]\n DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]\n ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]\n ALTER [ COLUMN ] column_name SET DEFAULT expression\n ALTER [ COLUMN ] column_name DROP DEFAULT\n ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL\n ALTER [ COLUMN ] column_name SET STATISTICS integer\n ALTER [ COLUMN ] column_name SET ( attribute_option = value [, ... ] )\n ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] )\n ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }\n ADD table_constraint [ NOT VALID ]\n ADD table_constraint_using_index\n ALTER CONSTRAINT constraint_name [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]\n VALIDATE CONSTRAINT constraint_name\n DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ]\n DISABLE TRIGGER [ trigger_name | ALL | USER ]\n ENABLE TRIGGER [ trigger_name | ALL | USER ]\n ENABLE REPLICA TRIGGER trigger_name\n ENABLE ALWAYS TRIGGER trigger_name\n DISABLE RULE rewrite_rule_name\n ENABLE RULE rewrite_rule_name\n ENABLE REPLICA RULE rewrite_rule_name\n ENABLE ALWAYS RULE rewrite_rule_name\n DISABLE ROW LEVEL SECURITY\n ENABLE ROW LEVEL SECURITY\n CLUSTER ON index_name\n SET WITHOUT CLUSTER\n SET WITH OIDS\n SET WITHOUT OIDS\n SET TABLESPACE new_tablespace\n SET { LOGGED | UNLOGGED }\n SET ( storage_parameter = value [, ... ] )\n RESET ( storage_parameter [, ... ] )\n INHERIT parent_table\n NO INHERIT parent_table\n OF type_name\n NOT OF\n OWNER TO { new_owner | CURRENT_USER | SESSION_USER }\n REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING }\n\nand table_constraint_using_index is:\n\n [ CONSTRAINT constraint_name ]\n { UNIQUE | PRIMARY KEY } USING INDEX index_name\n [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]\n", + }, + "ALTER TABLESPACE": { + "description": "Description\nALTER TABLESPACE can be used to change the definition of", + "synopsis": "\nALTER TABLESPACE name RENAME TO new_name\nALTER TABLESPACE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }\nALTER TABLESPACE name SET ( tablespace_option = value [, ... ] )\nALTER TABLESPACE name RESET ( tablespace_option [, ... ] )\n", + }, + "ALTER TRIGGER": { + "description": "Description\nALTER TRIGGER changes properties of an existing", + "synopsis": "\nALTER TRIGGER name ON table_name RENAME TO new_name\n", + }, + "ALTER TSCONFIG": { + "description": "Description\nALTER TEXT SEARCH CONFIGURATION changes the definition of", + "synopsis": "\nALTER TEXT SEARCH CONFIGURATION name\n ADD MAPPING FOR token_type [, ... ] WITH dictionary_name [, ... ]\nALTER TEXT SEARCH CONFIGURATION name\n ALTER MAPPING FOR token_type [, ... ] WITH dictionary_name [, ... ]\nALTER TEXT SEARCH CONFIGURATION name\n ALTER MAPPING REPLACE old_dictionary WITH new_dictionary\nALTER TEXT SEARCH CONFIGURATION name\n ALTER MAPPING FOR token_type [, ... ] REPLACE old_dictionary WITH new_dictionary\nALTER TEXT SEARCH CONFIGURATION name\n DROP MAPPING [ IF EXISTS ] FOR token_type [, ... ]\nALTER TEXT SEARCH CONFIGURATION name RENAME TO new_name\nALTER TEXT SEARCH CONFIGURATION name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }\nALTER TEXT SEARCH CONFIGURATION name SET SCHEMA new_schema\n", + }, + "ALTER TSDICTIONARY": { + "description": "Description\nALTER TEXT SEARCH DICTIONARY changes the definition of", + "synopsis": "\nALTER TEXT SEARCH DICTIONARY name (\n option [ = value ] [, ... ]\n)\nALTER TEXT SEARCH DICTIONARY name RENAME TO new_name\nALTER TEXT SEARCH DICTIONARY name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }\nALTER TEXT SEARCH DICTIONARY name SET SCHEMA new_schema\n", + }, + "ALTER TSPARSER": { + "description": "Description\nALTER TEXT SEARCH PARSER changes the definition of", + "synopsis": "\nALTER TEXT SEARCH PARSER name RENAME TO new_name\nALTER TEXT SEARCH PARSER name SET SCHEMA new_schema\n", + }, + "ALTER TSTEMPLATE": { + "description": "Description\nALTER TEXT SEARCH TEMPLATE changes the definition of", + "synopsis": "\nALTER TEXT SEARCH TEMPLATE name RENAME TO new_name\nALTER TEXT SEARCH TEMPLATE name SET SCHEMA new_schema\n", + }, + "ALTER TYPE": { + "description": "Description\nALTER TYPE changes the definition of an existing type.", + "synopsis": "\nALTER TYPE name action [, ... ]\nALTER TYPE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }\nALTER TYPE name RENAME ATTRIBUTE attribute_name TO new_attribute_name [ CASCADE | RESTRICT ]\nALTER TYPE name RENAME TO new_name\nALTER TYPE name SET SCHEMA new_schema\nALTER TYPE name ADD VALUE [ IF NOT EXISTS ] new_enum_value [ { BEFORE | AFTER } existing_enum_value ]\n\nwhere action is one of:\n\n ADD ATTRIBUTE attribute_name data_type [ COLLATE collation ] [ CASCADE | RESTRICT ]\n DROP ATTRIBUTE [ IF EXISTS ] attribute_name [ CASCADE | RESTRICT ]\n ALTER ATTRIBUTE attribute_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ CASCADE | RESTRICT ]\n", + }, + "ALTER USER": { + "description": "Description\nALTER USER is now an alias for", + "synopsis": "\nALTER USER role_specification [ WITH ] option [ ... ]\n\nwhere option can be:\n\n SUPERUSER | NOSUPERUSER\n | CREATEDB | NOCREATEDB\n | CREATEROLE | NOCREATEROLE\n | CREATEUSER | NOCREATEUSER\n | INHERIT | NOINHERIT\n | LOGIN | NOLOGIN\n | REPLICATION | NOREPLICATION\n | CONNECTION LIMIT connlimit\n | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'\n | VALID UNTIL 'timestamp'\n\nALTER USER name RENAME TO new_name\n\nALTER USER role_specification SET configuration_parameter { TO | = } { value | DEFAULT }\nALTER USER role_specification SET configuration_parameter FROM CURRENT\nALTER USER role_specification RESET configuration_parameter\nALTER USER role_specification RESET ALL\n\nwhere role_specification can be:\n\n [ GROUP ] role_name\n | CURRENT_USER\n | SESSION_USER\n", + }, + "ALTER USER MAPPING": { + "description": "Description\nALTER USER MAPPING changes the definition of a", + "synopsis": "\nALTER USER MAPPING FOR { user_name | USER | CURRENT_USER | SESSION_USER | PUBLIC }\n SERVER server_name\n OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ] )\n", + }, + "ALTER VIEW": { + "description": "Description\nALTER VIEW changes various auxiliary properties", + "synopsis": "\nALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name SET DEFAULT expression\nALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name DROP DEFAULT\nALTER VIEW [ IF EXISTS ] name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }\nALTER VIEW [ IF EXISTS ] name RENAME TO new_name\nALTER VIEW [ IF EXISTS ] name SET SCHEMA new_schema\nALTER VIEW [ IF EXISTS ] name SET ( view_option_name [= view_option_value] [, ... ] )\nALTER VIEW [ IF EXISTS ] name RESET ( view_option_name [, ... ] )\n", + }, + "ANALYZE": { + "description": "Description\nANALYZE collects statistics about the contents", + "synopsis": "\nANALYZE [ VERBOSE ] [ table_name [ ( column_name [, ...] ) ] ]\n", + }, + "BEGIN": { + "description": "Description\nBEGIN initiates a transaction block, that is,", + "synopsis": "\nBEGIN [ WORK | TRANSACTION ] [ transaction_mode [, ...] ]\n\nwhere transaction_mode is one of:\n\n ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }\n READ WRITE | READ ONLY\n [ NOT ] DEFERRABLE\n", + }, + "CHECKPOINT": { + "description": "Description\n A checkpoint is a point in the transaction log sequence at which", + "synopsis": "\nCHECKPOINT\n", + }, + "CLOSE": { + "description": "Description\nCLOSE frees the resources associated with an open cursor.", + "synopsis": "\nCLOSE { name | ALL }\n", + }, + "CLUSTER": { + "description": "Description\nCLUSTER instructs PostgreSQL", + "synopsis": "\nCLUSTER [VERBOSE] table_name [ USING index_name ]\nCLUSTER [VERBOSE]\n", + }, + "COMMENT": { + "description": "Description\nCOMMENT stores a comment about a database object.", + "synopsis": "\nCOMMENT ON\n{\n AGGREGATE aggregate_name ( aggregate_signature ) |\n CAST (source_type AS target_type) |\n COLLATION object_name |\n COLUMN relation_name.column_name |\n CONSTRAINT constraint_name ON table_name |\n CONSTRAINT constraint_name ON DOMAIN domain_name |\n CONVERSION object_name |\n DATABASE object_name |\n DOMAIN object_name |\n EXTENSION object_name |\n EVENT TRIGGER object_name |\n FOREIGN DATA WRAPPER object_name |\n FOREIGN TABLE object_name |\n FUNCTION function_name ( [ [ argmode ] [ argname ] argtype [, ...] ] ) |\n INDEX object_name |\n LARGE OBJECT large_object_oid |\n MATERIALIZED VIEW object_name |\n OPERATOR operator_name (left_type, right_type) |\n OPERATOR CLASS object_name USING index_method |\n OPERATOR FAMILY object_name USING index_method |\n POLICY policy_name ON table_name |\n [ PROCEDURAL ] LANGUAGE object_name |\n ROLE object_name |\n RULE rule_name ON table_name |\n SCHEMA object_name |\n SEQUENCE object_name |\n SERVER object_name |\n TABLE object_name |\n TABLESPACE object_name |\n TEXT SEARCH CONFIGURATION object_name |\n TEXT SEARCH DICTIONARY object_name |\n TEXT SEARCH PARSER object_name |\n TEXT SEARCH TEMPLATE object_name |\n TRANSFORM FOR type_name LANGUAGE lang_name |\n TRIGGER trigger_name ON table_name |\n TYPE object_name |\n VIEW object_name\n} IS 'text'\n\nwhere aggregate_signature is:\n\n* |\n[ argmode ] [ argname ] argtype [ , ... ] |\n[ [ argmode ] [ argname ] argtype [ , ... ] ] ORDER BY [ argmode ] [ argname ] argtype [ , ... ]\n", + }, + "COMMIT": { + "description": "Description\nCOMMIT commits the current transaction. All", + "synopsis": "\nCOMMIT [ WORK | TRANSACTION ]\n", + }, + "COMMIT PREPARED": { + "description": "Description\nCOMMIT PREPARED commits a transaction that is in", + "synopsis": "\nCOMMIT PREPARED transaction_id\n", + }, + "COPY": { + "description": "Description\nCOPY moves data between", + "synopsis": "\nCOPY table_name [ ( column_name [, ...] ) ]\n FROM { 'filename' | PROGRAM 'command' | STDIN }\n [ [ WITH ] ( option [, ...] ) ]\n\nCOPY { table_name [ ( column_name [, ...] ) ] | ( query ) }\n TO { 'filename' | PROGRAM 'command' | STDOUT }\n [ [ WITH ] ( option [, ...] ) ]\n\nwhere option can be one of:\n\n FORMAT format_name\n OIDS [ boolean ]\n FREEZE [ boolean ]\n DELIMITER 'delimiter_character'\n NULL 'null_string'\n HEADER [ boolean ]\n QUOTE 'quote_character'\n ESCAPE 'escape_character'\n FORCE_QUOTE { ( column_name [, ...] ) | * }\n FORCE_NOT_NULL ( column_name [, ...] )\n FORCE_NULL ( column_name [, ...] )\n ENCODING 'encoding_name'\n", + }, + "CREATE AGGREGATE": { + "description": "Description\nCREATE AGGREGATE defines a new aggregate", + "synopsis": "\nCREATE AGGREGATE name ( [ argmode ] [ argname ] arg_data_type [ , ... ] ) (\n SFUNC = sfunc,\n STYPE = state_data_type\n [ , SSPACE = state_data_size ]\n [ , FINALFUNC = ffunc ]\n [ , FINALFUNC_EXTRA ]\n [ , INITCOND = initial_condition ]\n [ , MSFUNC = msfunc ]\n [ , MINVFUNC = minvfunc ]\n [ , MSTYPE = mstate_data_type ]\n [ , MSSPACE = mstate_data_size ]\n [ , MFINALFUNC = mffunc ]\n [ , MFINALFUNC_EXTRA ]\n [ , MINITCOND = minitial_condition ]\n [ , SORTOP = sort_operator ]\n)\n\nCREATE AGGREGATE name ( [ [ argmode ] [ argname ] arg_data_type [ , ... ] ]\n ORDER BY [ argmode ] [ argname ] arg_data_type [ , ... ] ) (\n SFUNC = sfunc,\n STYPE = state_data_type\n [ , SSPACE = state_data_size ]\n [ , FINALFUNC = ffunc ]\n [ , FINALFUNC_EXTRA ]\n [ , INITCOND = initial_condition ]\n [ , HYPOTHETICAL ]\n)\n\nor the old syntax\n\nCREATE AGGREGATE name (\n BASETYPE = base_type,\n SFUNC = sfunc,\n STYPE = state_data_type\n [ , SSPACE = state_data_size ]\n [ , FINALFUNC = ffunc ]\n [ , FINALFUNC_EXTRA ]\n [ , INITCOND = initial_condition ]\n [ , MSFUNC = msfunc ]\n [ , MINVFUNC = minvfunc ]\n [ , MSTYPE = mstate_data_type ]\n [ , MSSPACE = mstate_data_size ]\n [ , MFINALFUNC = mffunc ]\n [ , MFINALFUNC_EXTRA ]\n [ , MINITCOND = minitial_condition ]\n [ , SORTOP = sort_operator ]\n)\n", + }, + "CREATE CAST": { + "description": "Description\nCREATE CAST defines a new cast. A cast", + "synopsis": "\nCREATE CAST (source_type AS target_type)\n WITH FUNCTION function_name (argument_type [, ...])\n [ AS ASSIGNMENT | AS IMPLICIT ]\n\nCREATE CAST (source_type AS target_type)\n WITHOUT FUNCTION\n [ AS ASSIGNMENT | AS IMPLICIT ]\n\nCREATE CAST (source_type AS target_type)\n WITH INOUT\n [ AS ASSIGNMENT | AS IMPLICIT ]\n", + }, + "CREATE COLLATION": { + "description": "Description\nCREATE COLLATION defines a new collation using", + "synopsis": "\nCREATE COLLATION name (\n [ LOCALE = locale, ]\n [ LC_COLLATE = lc_collate, ]\n [ LC_CTYPE = lc_ctype ]\n)\nCREATE COLLATION name FROM existing_collation\n", + }, + "CREATE CONVERSION": { + "description": "Description\nCREATE CONVERSION defines a new conversion between", + "synopsis": "\nCREATE [ DEFAULT ] CONVERSION name\n FOR source_encoding TO dest_encoding FROM function_name\n", + }, + "CREATE DATABASE": { + "description": "Description\nCREATE DATABASE creates a new", + "synopsis": "\nCREATE DATABASE name\n [ [ WITH ] [ OWNER [=] user_name ]\n [ TEMPLATE [=] template ]\n [ ENCODING [=] encoding ]\n [ LC_COLLATE [=] lc_collate ]\n [ LC_CTYPE [=] lc_ctype ]\n [ TABLESPACE [=] tablespace_name ]\n [ ALLOW_CONNECTIONS [=] allowconn ]\n [ CONNECTION LIMIT [=] connlimit ] ]\n [ IS_TEMPLATE [=] istemplate ]\n", + }, + "CREATE DOMAIN": { + "description": "Description\nCREATE DOMAIN creates a new domain. A domain is", + "synopsis": "\nCREATE DOMAIN name [ AS ] data_type\n [ COLLATE collation ]\n [ DEFAULT expression ]\n [ constraint [ ... ] ]\n\nwhere constraint is:\n\n[ CONSTRAINT constraint_name ]\n{ NOT NULL | NULL | CHECK (expression) }\n", + }, + "CREATE EVENT TRIGGER": { + "description": "Description\nCREATE EVENT TRIGGER creates a new event trigger.", + "synopsis": "\nCREATE EVENT TRIGGER name\n ON event\n [ WHEN filter_variable IN (filter_value [, ... ]) [ AND ... ] ]\n EXECUTE PROCEDURE function_name()\n", + }, + "CREATE EXTENSION": { + "description": "Description\nCREATE EXTENSION loads a new extension into the current", + "synopsis": "\nCREATE EXTENSION [ IF NOT EXISTS ] extension_name\n [ WITH ] [ SCHEMA schema_name ]\n [ VERSION version ]\n [ FROM old_version ]\n", + }, + "CREATE FOREIGN DATA WRAPPER": { + "description": "Description\nCREATE FOREIGN DATA WRAPPER creates a new", + "synopsis": "\nCREATE FOREIGN DATA WRAPPER name\n [ HANDLER handler_function | NO HANDLER ]\n [ VALIDATOR validator_function | NO VALIDATOR ]\n [ OPTIONS ( option 'value' [, ... ] ) ]\n", + }, + "CREATE FOREIGN TABLE": { + "description": "Description\nCREATE FOREIGN TABLE creates a new foreign table", + "synopsis": "\nCREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [\n { column_name data_type [ OPTIONS ( option 'value' [, ... ] ) ] [ COLLATE collation ] [ column_constraint [ ... ] ]\n | table_constraint }\n [, ... ]\n] )\n[ INHERITS ( parent_table [, ... ] ) ]\n SERVER server_name\n[ OPTIONS ( option 'value' [, ... ] ) ]\n\nwhere column_constraint is:\n\n[ CONSTRAINT constraint_name ]\n{ NOT NULL |\n NULL |\n CHECK ( expression ) [ NO INHERIT ] |\n DEFAULT default_expr }\n\nand table_constraint is:\n\n[ CONSTRAINT constraint_name ]\nCHECK ( expression ) [ NO INHERIT ]\n", + }, + "CREATE FUNCTION": { + "description": "Description\nCREATE FUNCTION defines a new function.", + "synopsis": "\nCREATE [ OR REPLACE ] FUNCTION\n name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )\n [ RETURNS rettype\n | RETURNS TABLE ( column_name column_type [, ...] ) ]\n { LANGUAGE lang_name\n | TRANSFORM { FOR TYPE type_name } [, ... ]\n | WINDOW\n | IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF\n | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT\n | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER\n | PARALLEL { UNSAFE | RESTRICTED | SAFE }\n | COST execution_cost\n | ROWS result_rows\n | SET configuration_parameter { TO value | = value | FROM CURRENT }\n | AS 'definition'\n | AS 'obj_file', 'link_symbol'\n } ...\n [ WITH ( attribute [, ...] ) ]\n", + }, + "CREATE GROUP": { + "description": "Description\nCREATE GROUP is now an alias for", + "synopsis": "\nCREATE GROUP name [ [ WITH ] option [ ... ] ]\n\nwhere option can be:\n\n SUPERUSER | NOSUPERUSER\n | CREATEDB | NOCREATEDB\n | CREATEROLE | NOCREATEROLE\n | CREATEUSER | NOCREATEUSER\n | INHERIT | NOINHERIT\n | LOGIN | NOLOGIN\n | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'\n | VALID UNTIL 'timestamp'\n | IN ROLE role_name [, ...]\n | IN GROUP role_name [, ...]\n | ROLE role_name [, ...]\n | ADMIN role_name [, ...]\n | USER role_name [, ...]\n | SYSID uid\n", + }, + "CREATE INDEX": { + "description": "Description\nCREATE INDEX constructs an index on the specified column(s)", + "synopsis": "\nCREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON table_name [ USING method ]\n ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )\n [ WITH ( storage_parameter = value [, ... ] ) ]\n [ TABLESPACE tablespace_name ]\n [ WHERE predicate ]\n", + }, + "CREATE LANGUAGE": { + "description": "Description\nCREATE LANGUAGE registers a new", + "synopsis": "\nCREATE [ OR REPLACE ] [ PROCEDURAL ] LANGUAGE name\nCREATE [ OR REPLACE ] [ TRUSTED ] [ PROCEDURAL ] LANGUAGE name\n HANDLER call_handler [ INLINE inline_handler ] [ VALIDATOR valfunction ]\n", + }, + "CREATE MATERIALIZED VIEW": { + "description": "Description\nCREATE MATERIALIZED VIEW defines a materialized view of", + "synopsis": "\nCREATE MATERIALIZED VIEW [ IF NOT EXISTS ] table_name\n [ (column_name [, ...] ) ]\n [ WITH ( storage_parameter [= value] [, ... ] ) ]\n [ TABLESPACE tablespace_name ]\n AS query\n [ WITH [ NO ] DATA ]\n", + }, + "CREATE OPCLASS": { + "description": "Description\nCREATE OPERATOR CLASS creates a new operator class.", + "synopsis": "\nCREATE OPERATOR CLASS name [ DEFAULT ] FOR TYPE data_type\n USING index_method [ FAMILY family_name ] AS\n { OPERATOR strategy_number operator_name [ ( op_type, op_type ) ] [ FOR SEARCH | FOR ORDER BY sort_family_name ]\n | FUNCTION support_number [ ( op_type [ , op_type ] ) ] function_name ( argument_type [, ...] )\n | STORAGE storage_type\n } [, ... ]\n", + }, + "CREATE OPERATOR": { + "description": "Description\nCREATE OPERATOR defines a new operator,", + "synopsis": "\nCREATE OPERATOR name (\n PROCEDURE = function_name\n [, LEFTARG = left_type ] [, RIGHTARG = right_type ]\n [, COMMUTATOR = com_op ] [, NEGATOR = neg_op ]\n [, RESTRICT = res_proc ] [, JOIN = join_proc ]\n [, HASHES ] [, MERGES ]\n)\n", + }, + "CREATE OPFAMILY": { + "description": "Description\nCREATE OPERATOR FAMILY creates a new operator family.", + "synopsis": "\nCREATE OPERATOR FAMILY name USING index_method\n", + }, + "CREATE POLICY": { + "description": "Description\n The CREATE POLICY command defines a new policy for a", + "synopsis": "\nCREATE POLICY name ON table_name\n [ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]\n [ TO { role_name | PUBLIC | CURRENT_USER | SESSION_USER } [, ...] ]\n [ USING ( using_expression ) ]\n [ WITH CHECK ( check_expression ) ]\n", + }, + "CREATE ROLE": { + "description": "Description\nCREATE ROLE adds a new role to a", + "synopsis": "\nCREATE ROLE name [ [ WITH ] option [ ... ] ]\n\nwhere option can be:\n\n SUPERUSER | NOSUPERUSER\n | CREATEDB | NOCREATEDB\n | CREATEROLE | NOCREATEROLE\n | CREATEUSER | NOCREATEUSER\n | INHERIT | NOINHERIT\n | LOGIN | NOLOGIN\n | REPLICATION | NOREPLICATION\n | BYPASSRLS | NOBYPASSRLS\n | CONNECTION LIMIT connlimit\n | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'\n | VALID UNTIL 'timestamp'\n | IN ROLE role_name [, ...]\n | IN GROUP role_name [, ...]\n | ROLE role_name [, ...]\n | ADMIN role_name [, ...]\n | USER role_name [, ...]\n | SYSID uid\n", + }, + "CREATE RULE": { + "description": "Description\nCREATE RULE defines a new rule applying to a specified", + "synopsis": "\nCREATE [ OR REPLACE ] RULE name AS ON event\n TO table_name [ WHERE condition ]\n DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }\n\nwhere event can be one of:\n\n SELECT | INSERT | UPDATE | DELETE\n", + }, + "CREATE SCHEMA": { + "description": "Description\nCREATE SCHEMA enters a new schema", + "synopsis": "\nCREATE SCHEMA schema_name [ AUTHORIZATION role_specification ] [ schema_element [ ... ] ]\nCREATE SCHEMA AUTHORIZATION role_specification [ schema_element [ ... ] ]\nCREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION role_specification ]\nCREATE SCHEMA IF NOT EXISTS AUTHORIZATION role_specification\nwhere role_specification can be:\n\n [ GROUP ] user_name\n | CURRENT_USER\n | SESSION_USER\n", + }, + "CREATE SEQUENCE": { + "description": "Description\nCREATE SEQUENCE creates a new sequence number", + "synopsis": "\nCREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] name [ INCREMENT [ BY ] increment ]\n [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]\n [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]\n [ OWNED BY { table_name.column_name | NONE } ]\n", + }, + "CREATE SERVER": { + "description": "Description\nCREATE SERVER defines a new foreign server. The", + "synopsis": "\nCREATE SERVER server_name [ TYPE 'server_type' ] [ VERSION 'server_version' ]\n FOREIGN DATA WRAPPER fdw_name\n [ OPTIONS ( option 'value' [, ... ] ) ]\n", + }, + "CREATE TABLE": { + "description": "Description\nCREATE TABLE will create a new, initially empty table", + "synopsis": "\nCREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [\n { column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]\n | table_constraint\n | LIKE source_table [ like_option ... ] }\n [, ... ]\n] )\n[ INHERITS ( parent_table [, ... ] ) ]\n[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]\n[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]\n[ TABLESPACE tablespace_name ]\n\nCREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name\n OF type_name [ (\n { column_name WITH OPTIONS [ column_constraint [ ... ] ]\n | table_constraint }\n [, ... ]\n) ]\n[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]\n[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]\n[ TABLESPACE tablespace_name ]\n\nwhere column_constraint is:\n\n[ CONSTRAINT constraint_name ]\n{ NOT NULL |\n NULL |\n CHECK ( expression ) [ NO INHERIT ] |\n DEFAULT default_expr |\n UNIQUE index_parameters |\n PRIMARY KEY index_parameters |\n REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]\n [ ON DELETE action ] [ ON UPDATE action ] }\n[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]\n\nand table_constraint is:\n\n[ CONSTRAINT constraint_name ]\n{ CHECK ( expression ) [ NO INHERIT ] |\n UNIQUE ( column_name [, ... ] ) index_parameters |\n PRIMARY KEY ( column_name [, ... ] ) index_parameters |\n EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] |\n FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]\n [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }\n[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]\n\nand like_option is:\n\n{ INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | ALL }\n\nindex_parameters in UNIQUE, PRIMARY KEY, and EXCLUDE constraints are:\n\n[ WITH ( storage_parameter [= value] [, ... ] ) ]\n[ USING INDEX TABLESPACE tablespace_name ]\n\nexclude_element in an EXCLUDE constraint is:\n\n{ column_name | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]\n", + }, + "CREATE TABLE AS": { + "description": "Description\nCREATE TABLE AS creates a table and fills it", + "synopsis": "\nCREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name\n [ (column_name [, ...] ) ]\n [ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]\n [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]\n [ TABLESPACE tablespace_name ]\n AS query\n [ WITH [ NO ] DATA ]\n", + }, + "CREATE TABLESPACE": { + "description": "Description\nCREATE TABLESPACE registers a new cluster-wide", + "synopsis": "\nCREATE TABLESPACE tablespace_name\n [ OWNER { new_owner | CURRENT_USER | SESSION_USER } ]\n LOCATION 'directory'\n [ WITH ( tablespace_option = value [, ... ] ) ]\n", + }, + "CREATE TRANSFORM": { + "description": "Description\nCREATE TRANSFORM defines a new transform.", + "synopsis": "\nCREATE [ OR REPLACE ] TRANSFORM FOR type_name LANGUAGE lang_name (\n FROM SQL WITH FUNCTION from_sql_function_name (argument_type [, ...]),\n TO SQL WITH FUNCTION to_sql_function_name (argument_type [, ...])\n);\n", + }, + "CREATE TRIGGER": { + "description": "Description\nCREATE TRIGGER creates a new trigger. The", + "synopsis": "\nCREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }\n ON table_name\n [ FROM referenced_table_name ]\n [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]\n [ FOR [ EACH ] { ROW | STATEMENT } ]\n [ WHEN ( condition ) ]\n EXECUTE PROCEDURE function_name ( arguments )\n\nwhere event can be one of:\n\n INSERT\n UPDATE [ OF column_name [, ... ] ]\n DELETE\n TRUNCATE\n", + }, + "CREATE TSCONFIG": { + "description": "Description\nCREATE TEXT SEARCH CONFIGURATION creates a new text", + "synopsis": "\nCREATE TEXT SEARCH CONFIGURATION name (\n PARSER = parser_name |\n COPY = source_config\n)\n", + }, + "CREATE TSDICTIONARY": { + "description": "Description\nCREATE TEXT SEARCH DICTIONARY creates a new text search", + "synopsis": "\nCREATE TEXT SEARCH DICTIONARY name (\n TEMPLATE = template\n [, option = value [, ... ]]\n)\n", + }, + "CREATE TSPARSER": { + "description": "Description\nCREATE TEXT SEARCH PARSER creates a new text search", + "synopsis": "\nCREATE TEXT SEARCH PARSER name (\n START = start_function ,\n GETTOKEN = gettoken_function ,\n END = end_function ,\n LEXTYPES = lextypes_function\n [, HEADLINE = headline_function ]\n)\n", + }, + "CREATE TSTEMPLATE": { + "description": "Description\nCREATE TEXT SEARCH TEMPLATE creates a new text search", + "synopsis": "\nCREATE TEXT SEARCH TEMPLATE name (\n [ INIT = init_function , ]\n LEXIZE = lexize_function\n)\n", + }, + "CREATE TYPE": { + "description": "Description\nCREATE TYPE registers a new data type for use in", + "synopsis": "\nCREATE TYPE name AS\n ( [ attribute_name data_type [ COLLATE collation ] [, ... ] ] )\n\nCREATE TYPE name AS ENUM\n ( [ 'label' [, ... ] ] )\n\nCREATE TYPE name AS RANGE (\n SUBTYPE = subtype\n [ , SUBTYPE_OPCLASS = subtype_operator_class ]\n [ , COLLATION = collation ]\n [ , CANONICAL = canonical_function ]\n [ , SUBTYPE_DIFF = subtype_diff_function ]\n)\n\nCREATE TYPE name (\n INPUT = input_function,\n OUTPUT = output_function\n [ , RECEIVE = receive_function ]\n [ , SEND = send_function ]\n [ , TYPMOD_IN = type_modifier_input_function ]\n [ , TYPMOD_OUT = type_modifier_output_function ]\n [ , ANALYZE = analyze_function ]\n [ , INTERNALLENGTH = { internallength | VARIABLE } ]\n [ , PASSEDBYVALUE ]\n [ , ALIGNMENT = alignment ]\n [ , STORAGE = storage ]\n [ , LIKE = like_type ]\n [ , CATEGORY = category ]\n [ , PREFERRED = preferred ]\n [ , DEFAULT = default ]\n [ , ELEMENT = element ]\n [ , DELIMITER = delimiter ]\n [ , COLLATABLE = collatable ]\n)\n\nCREATE TYPE name\n", + }, + "CREATE USER": { + "description": "Description\nCREATE USER is now an alias for", + "synopsis": "\nCREATE USER name [ [ WITH ] option [ ... ] ]\n\nwhere option can be:\n\n SUPERUSER | NOSUPERUSER\n | CREATEDB | NOCREATEDB\n | CREATEROLE | NOCREATEROLE\n | CREATEUSER | NOCREATEUSER\n | INHERIT | NOINHERIT\n | LOGIN | NOLOGIN\n | REPLICATION | NOREPLICATION\n | CONNECTION LIMIT connlimit\n | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'\n | VALID UNTIL 'timestamp'\n | IN ROLE role_name [, ...]\n | IN GROUP role_name [, ...]\n | ROLE role_name [, ...]\n | ADMIN role_name [, ...]\n | USER role_name [, ...]\n | SYSID uid\n", + }, + "CREATE USER MAPPING": { + "description": "Description\nCREATE USER MAPPING defines a mapping of a user", + "synopsis": "\nCREATE USER MAPPING FOR { user_name | USER | CURRENT_USER | PUBLIC }\n SERVER server_name\n [ OPTIONS ( option 'value' [ , ... ] ) ]\n", + }, + "CREATE VIEW": { + "description": "Description\nCREATE VIEW defines a view of a query. The view", + "synopsis": "\nCREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW name [ ( column_name [, ...] ) ]\n [ WITH ( view_option_name [= view_option_value] [, ... ] ) ]\n AS query\n [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]\n", + }, + "DEALLOCATE": { + "description": "Description\nDEALLOCATE is used to deallocate a previously", + "synopsis": "\nDEALLOCATE [ PREPARE ] { name | ALL }\n", + }, + "DECLARE": { + "description": "Description\nDECLARE allows a user to create cursors, which", + "synopsis": "\nDECLARE name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ]\n CURSOR [ { WITH | WITHOUT } HOLD ] FOR query\n", + }, + "DELETE": { + "description": "Description\nDELETE deletes rows that satisfy the", + "synopsis": "\n[ WITH [ RECURSIVE ] with_query [, ...] ]\nDELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]\n [ USING using_list ]\n [ WHERE condition | WHERE CURRENT OF cursor_name ]\n [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]\n", + }, + "DISCARD": { + "description": "Description\nDISCARD releases internal resources associated with a", + "synopsis": "\nDISCARD { ALL | PLANS | SEQUENCES | TEMPORARY | TEMP }\n", + }, + "DO": { + "description": "Description\nDO executes an anonymous code block, or in other", + "synopsis": "\nDO [ LANGUAGE lang_name ] code\n", + }, + "DROP AGGREGATE": { + "description": "Description\nDROP AGGREGATE removes an existing", + "synopsis": "\nDROP AGGREGATE [ IF EXISTS ] name ( aggregate_signature ) [ CASCADE | RESTRICT ]\n\nwhere aggregate_signature is:\n\n* |\n[ argmode ] [ argname ] argtype [ , ... ] |\n[ [ argmode ] [ argname ] argtype [ , ... ] ] ORDER BY [ argmode ] [ argname ] argtype [ , ... ]\n", + }, + "DROP CAST": { + "description": "Description\nDROP CAST removes a previously defined cast.", + "synopsis": "\nDROP CAST [ IF EXISTS ] (source_type AS target_type) [ CASCADE | RESTRICT ]\n", + }, + "DROP COLLATION": { + "description": "Description\nDROP COLLATION removes a previously defined collation.", + "synopsis": "\nDROP COLLATION [ IF EXISTS ] name [ CASCADE | RESTRICT ]\n", + }, + "DROP CONVERSION": { + "description": "Description\nDROP CONVERSION removes a previously defined conversion.", + "synopsis": "\nDROP CONVERSION [ IF EXISTS ] name [ CASCADE | RESTRICT ]\n", + }, + "DROP DATABASE": { + "description": "Description\nDROP DATABASE drops a database. It removes the", + "synopsis": "\nDROP DATABASE [ IF EXISTS ] name\n", + }, + "DROP DOMAIN": { + "description": "Description\nDROP DOMAIN removes a domain. Only the owner of", + "synopsis": "\nDROP DOMAIN [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]\n", + }, + "DROP EVENT TRIGGER": { + "description": "Description\nDROP EVENT TRIGGER removes an existing event trigger.", + "synopsis": "\nDROP EVENT TRIGGER [ IF EXISTS ] name [ CASCADE | RESTRICT ]\n", + }, + "DROP EXTENSION": { + "description": "Description\nDROP EXTENSION removes extensions from the database.", + "synopsis": "\nDROP EXTENSION [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]\n", + }, + "DROP FOREIGN DATA WRAPPER": { + "description": "Description\nDROP FOREIGN DATA WRAPPER removes an existing", + "synopsis": "\nDROP FOREIGN DATA WRAPPER [ IF EXISTS ] name [ CASCADE | RESTRICT ]\n", + }, + "DROP FOREIGN TABLE": { + "description": "Description\nDROP FOREIGN TABLE removes a foreign table.", + "synopsis": "\nDROP FOREIGN TABLE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]\n", + }, + "DROP FUNCTION": { + "description": "Description\nDROP FUNCTION removes the definition of an existing", + "synopsis": "\nDROP FUNCTION [ IF EXISTS ] name ( [ [ argmode ] [ argname ] argtype [, ...] ] )\n [ CASCADE | RESTRICT ]\n", + }, + "DROP GROUP": { + "description": "Description\nDROP GROUP is now an alias for", + "synopsis": "\nDROP GROUP [ IF EXISTS ] name [, ...]\n", + }, + "DROP INDEX": { + "description": "Description\nDROP INDEX drops an existing index from the database", + "synopsis": "\nDROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]\n", + }, + "DROP LANGUAGE": { + "description": "Description\nDROP LANGUAGE removes the definition of a", + "synopsis": "\nDROP [ PROCEDURAL ] LANGUAGE [ IF EXISTS ] name [ CASCADE | RESTRICT ]\n", + }, + "DROP MATERIALIZED VIEW": { + "description": "Description\nDROP MATERIALIZED VIEW drops an existing materialized", + "synopsis": "\nDROP MATERIALIZED VIEW [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]\n", + }, + "DROP OPCLASS": { + "description": "Description\nDROP OPERATOR CLASS drops an existing operator class.", + "synopsis": "\nDROP OPERATOR CLASS [ IF EXISTS ] name USING index_method [ CASCADE | RESTRICT ]\n", + }, + "DROP OPERATOR": { + "description": "Description\nDROP OPERATOR drops an existing operator from", + "synopsis": "\nDROP OPERATOR [ IF EXISTS ] name ( { left_type | NONE } , { right_type | NONE } ) [ CASCADE | RESTRICT ]\n", + }, + "DROP OPFAMILY": { + "description": "Description\nDROP OPERATOR FAMILY drops an existing operator family.", + "synopsis": "\nDROP OPERATOR FAMILY [ IF EXISTS ] name USING index_method [ CASCADE | RESTRICT ]\n", + }, + "DROP OWNED": { + "description": "Description\nDROP OWNED drops all the objects within the current", + "synopsis": "\nDROP OWNED BY { name | CURRENT_USER | SESSION_USER } [, ...] [ CASCADE | RESTRICT ]\n", + }, + "DROP POLICY": { + "description": "Description\nDROP POLICY removes the specified policy from the table.", + "synopsis": "\nDROP POLICY [ IF EXISTS ] name ON table_name\n", + }, + "DROP ROLE": { + "description": "Description\nDROP ROLE removes the specified role(s).", + "synopsis": "\nDROP ROLE [ IF EXISTS ] name [, ...]\n", + }, + "DROP RULE": { + "description": "Description\nDROP RULE drops a rewrite rule.", + "synopsis": "\nDROP RULE [ IF EXISTS ] name ON table_name [ CASCADE | RESTRICT ]\n", + }, + "DROP SCHEMA": { + "description": "Description\nDROP SCHEMA removes schemas from the database.", + "synopsis": "\nDROP SCHEMA [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]\n", + }, + "DROP SEQUENCE": { + "description": "Description\nDROP SEQUENCE removes sequence number", + "synopsis": "\nDROP SEQUENCE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]\n", + }, + "DROP SERVER": { + "description": "Description\nDROP SERVER removes an existing foreign server", + "synopsis": "\nDROP SERVER [ IF EXISTS ] name [ CASCADE | RESTRICT ]\n", + }, + "DROP TABLE": { + "description": "Description\nDROP TABLE removes tables from the database.", + "synopsis": "\nDROP TABLE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]\n", + }, + "DROP TABLESPACE": { + "description": "Description\nDROP TABLESPACE removes a tablespace from the system.", + "synopsis": "\nDROP TABLESPACE [ IF EXISTS ] name\n", + }, + "DROP TRANSFORM": { + "description": "Description\nDROP TRANSFORM removes a previously defined transform.", + "synopsis": "\nDROP TRANSFORM [ IF EXISTS ] FOR type_name LANGUAGE lang_name\n", + }, + "DROP TRIGGER": { + "description": "Description\nDROP TRIGGER removes an existing", + "synopsis": "\nDROP TRIGGER [ IF EXISTS ] name ON table_name [ CASCADE | RESTRICT ]\n", + }, + "DROP TSCONFIG": { + "description": "Description\nDROP TEXT SEARCH CONFIGURATION drops an existing text", + "synopsis": "\nDROP TEXT SEARCH CONFIGURATION [ IF EXISTS ] name [ CASCADE | RESTRICT ]\n", + }, + "DROP TSDICTIONARY": { + "description": "Description\nDROP TEXT SEARCH DICTIONARY drops an existing text", + "synopsis": "\nDROP TEXT SEARCH DICTIONARY [ IF EXISTS ] name [ CASCADE | RESTRICT ]\n", + }, + "DROP TSPARSER": { + "description": "Description\nDROP TEXT SEARCH PARSER drops an existing text search", + "synopsis": "\nDROP TEXT SEARCH PARSER [ IF EXISTS ] name [ CASCADE | RESTRICT ]\n", + }, + "DROP TSTEMPLATE": { + "description": "Description\nDROP TEXT SEARCH TEMPLATE drops an existing text search", + "synopsis": "\nDROP TEXT SEARCH TEMPLATE [ IF EXISTS ] name [ CASCADE | RESTRICT ]\n", + }, + "DROP TYPE": { + "description": "Description\nDROP TYPE removes a user-defined data type.", + "synopsis": "\nDROP TYPE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]\n", + }, + "DROP USER": { + "description": "Description\nDROP USER is now an alias for", + "synopsis": "\nDROP USER [ IF EXISTS ] name [, ...]\n", + }, + "DROP USER MAPPING": { + "description": "Description\nDROP USER MAPPING removes an existing user", + "synopsis": "\nDROP USER MAPPING [ IF EXISTS ] FOR { user_name | USER | CURRENT_USER | PUBLIC } SERVER server_name\n", + }, + "DROP VIEW": { + "description": "Description\nDROP VIEW drops an existing view. To execute", + "synopsis": "\nDROP VIEW [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]\n", + }, + "END": { + "description": "Description\nEND commits the current transaction. All changes", + "synopsis": "\nEND [ WORK | TRANSACTION ]\n", + }, + "EXECUTE": { + "description": "Description\nEXECUTE is used to execute a previously prepared", + "synopsis": "\nEXECUTE name [ ( parameter [, ...] ) ]\n", + }, + "EXPLAIN": { + "description": "Description\n This command displays the execution plan that the", + "synopsis": "\nEXPLAIN [ ( option [, ...] ) ] statement\nEXPLAIN [ ANALYZE ] [ VERBOSE ] statement\nwhere option can be one of:\n\n ANALYZE [ boolean ]\n VERBOSE [ boolean ]\n COSTS [ boolean ]\n BUFFERS [ boolean ]\n TIMING [ boolean ]\n FORMAT { TEXT | XML | JSON | YAML }\n", + }, + "FETCH": { + "description": "Description\nFETCH retrieves rows using a previously-created cursor.", + "synopsis": "\nFETCH [ direction [ FROM | IN ] ] cursor_name\nwhere direction can be empty or one of:\n\n NEXT\n PRIOR\n FIRST\n LAST\n ABSOLUTE count\n RELATIVE count\ncount\n ALL\n FORWARD\n FORWARD count\n FORWARD ALL\n BACKWARD\n BACKWARD count\n BACKWARD ALL\n", + }, + "GRANT": { + "description": "Description\n The GRANT command has two basic variants: one", + "synopsis": "\nGRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }\n [, ...] | ALL [ PRIVILEGES ] }\n ON { [ TABLE ] table_name [, ...]\n | ALL TABLES IN SCHEMA schema_name [, ...] }\n TO role_specification [, ...] [ WITH GRANT OPTION ]\n\nGRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )\n [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }\n ON [ TABLE ] table_name [, ...]\n TO role_specification [, ...] [ WITH GRANT OPTION ]\n\nGRANT { { USAGE | SELECT | UPDATE }\n [, ...] | ALL [ PRIVILEGES ] }\n ON { SEQUENCE sequence_name [, ...]\n | ALL SEQUENCES IN SCHEMA schema_name [, ...] }\n TO role_specification [, ...] [ WITH GRANT OPTION ]\n\nGRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }\n ON DATABASE database_name [, ...]\n TO role_specification [, ...] [ WITH GRANT OPTION ]\n\nGRANT { USAGE | ALL [ PRIVILEGES ] }\n ON DOMAIN domain_name [, ...]\n TO role_specification [, ...] [ WITH GRANT OPTION ]\n\nGRANT { USAGE | ALL [ PRIVILEGES ] }\n ON FOREIGN DATA WRAPPER fdw_name [, ...]\n TO role_specification [, ...] [ WITH GRANT OPTION ]\n\nGRANT { USAGE | ALL [ PRIVILEGES ] }\n ON FOREIGN SERVER server_name [, ...]\n TO role_specification [, ...] [ WITH GRANT OPTION ]\n\nGRANT { EXECUTE | ALL [ PRIVILEGES ] }\n ON { FUNCTION function_name ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) [, ...]\n | ALL FUNCTIONS IN SCHEMA schema_name [, ...] }\n TO role_specification [, ...] [ WITH GRANT OPTION ]\n\nGRANT { USAGE | ALL [ PRIVILEGES ] }\n ON LANGUAGE lang_name [, ...]\n TO role_specification [, ...] [ WITH GRANT OPTION ]\n\nGRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }\n ON LARGE OBJECT loid [, ...]\n TO role_specification [, ...] [ WITH GRANT OPTION ]\n\nGRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }\n ON SCHEMA schema_name [, ...]\n TO role_specification [, ...] [ WITH GRANT OPTION ]\n\nGRANT { CREATE | ALL [ PRIVILEGES ] }\n ON TABLESPACE tablespace_name [, ...]\n TO role_specification [, ...] [ WITH GRANT OPTION ]\n\nGRANT { USAGE | ALL [ PRIVILEGES ] }\n ON TYPE type_name [, ...]\n TO role_specification [, ...] [ WITH GRANT OPTION ]\n\nwhere role_specification can be:\n\n [ GROUP ] role_name\n | PUBLIC\n | CURRENT_USER\n | SESSION_USER\n\nGRANT role_name [, ...] TO role_name [, ...] [ WITH ADMIN OPTION ]\n", + }, + "IMPORT FOREIGN SCHEMA": { + "description": "Description\nIMPORT FOREIGN SCHEMA creates foreign tables that", + "synopsis": "\nIMPORT FOREIGN SCHEMA remote_schema\n [ { LIMIT TO | EXCEPT } ( table_name [, ...] ) ]\n FROM SERVER server_name\n INTO local_schema\n [ OPTIONS ( option 'value' [, ... ] ) ]\n", + }, + "INSERT": { + "description": "Description\nINSERT inserts new rows into a table.", + "synopsis": "\n[ WITH [ RECURSIVE ] with_query [, ...] ]\nINSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]\n { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }\n [ ON CONFLICT [ conflict_target ] conflict_action ]\n [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]\n\nwhere conflict_target can be one of:\n\n ( { column_name_index | ( expression_index ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]\n ON CONSTRAINT constraint_name\nand conflict_action is one of:\n\n DO NOTHING\n DO UPDATE SET { column_name = { expression | DEFAULT } |\n ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) |\n ( column_name [, ...] ) = ( sub-SELECT )\n } [, ...]\n [ WHERE condition ]\n", + }, + "LISTEN": { + "description": "Description\nLISTEN registers the current session as a", + "synopsis": "\nLISTEN channel\n", + }, + "LOAD": { + "description": "Description\n This command loads a shared library file into the PostgreSQL", + "synopsis": "\nLOAD 'filename'\n", + }, + "LOCK": { + "description": "Description\nLOCK TABLE obtains a table-level lock, waiting", + "synopsis": "\nLOCK [ TABLE ] [ ONLY ] name [ * ] [, ...] [ IN lockmode MODE ] [ NOWAIT ]\n\nwhere lockmode is one of:\n\n ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE\n | SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE\n", + }, + "MOVE": { + "description": "Description\nMOVE repositions a cursor without retrieving any data.", + "synopsis": "\nMOVE [ direction [ FROM | IN ] ] cursor_name\nwhere direction can be empty or one of:\n\n NEXT\n PRIOR\n FIRST\n LAST\n ABSOLUTE count\n RELATIVE count\ncount\n ALL\n FORWARD\n FORWARD count\n FORWARD ALL\n BACKWARD\n BACKWARD count\n BACKWARD ALL\n", + }, + "NOTIFY": { + "description": "Description\n The NOTIFY command sends a notification event together", + "synopsis": "\nNOTIFY channel [ , payload ]\n", + }, + "PGBENCH": { + "description": "Description\npgbench is a simple program for running benchmark", + "synopsis": "\nclient_id transaction_no time file_no time_epoch time_us schedule_lag\n", + }, + "PREPARE": { + "description": "Description\nPREPARE creates a prepared statement. A prepared", + "synopsis": "\nPREPARE name [ ( data_type [, ...] ) ] AS statement\n", + }, + "PREPARE TRANSACTION": { + "description": "Description\nPREPARE TRANSACTION prepares the current transaction", + "synopsis": "\nPREPARE TRANSACTION transaction_id\n", + }, + "REASSIGN OWNED": { + "description": "Description\nREASSIGN OWNED instructs the system to change", + "synopsis": "\nREASSIGN OWNED BY { old_role | CURRENT_USER | SESSION_USER } [, ...]\n TO { new_role | CURRENT_USER | SESSION_USER }\n", + }, + "REFRESH MATERIALIZED VIEW": { + "description": "Description\nREFRESH MATERIALIZED VIEW completely replaces the", + "synopsis": "\nREFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name\n [ WITH [ NO ] DATA ]\n", + }, + "REINDEX": { + "description": "Description\nREINDEX rebuilds an index using the data", + "synopsis": "\nREINDEX [ ( { VERBOSE } [, ...] ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } name\n", + }, + "RELEASE SAVEPOINT": { + "description": "Description\nRELEASE SAVEPOINT destroys a savepoint previously defined", + "synopsis": "\nRELEASE [ SAVEPOINT ] savepoint_name\n", + }, + "RESET": { + "description": "Description\nRESET restores run-time parameters to their", + "synopsis": "\nRESET configuration_parameter\nRESET ALL\n", + }, + "REVOKE": { + "description": "Description\n The REVOKE command revokes previously granted", + "synopsis": "\nREVOKE [ GRANT OPTION FOR ]\n { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }\n [, ...] | ALL [ PRIVILEGES ] }\n ON { [ TABLE ] table_name [, ...]\n | ALL TABLES IN SCHEMA schema_name [, ...] }\n FROM { [ GROUP ] role_name | PUBLIC } [, ...]\n [ CASCADE | RESTRICT ]\n\nREVOKE [ GRANT OPTION FOR ]\n { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )\n [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }\n ON [ TABLE ] table_name [, ...]\n FROM { [ GROUP ] role_name | PUBLIC } [, ...]\n [ CASCADE | RESTRICT ]\n\nREVOKE [ GRANT OPTION FOR ]\n { { USAGE | SELECT | UPDATE }\n [, ...] | ALL [ PRIVILEGES ] }\n ON { SEQUENCE sequence_name [, ...]\n | ALL SEQUENCES IN SCHEMA schema_name [, ...] }\n FROM { [ GROUP ] role_name | PUBLIC } [, ...]\n [ CASCADE | RESTRICT ]\n\nREVOKE [ GRANT OPTION FOR ]\n { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }\n ON DATABASE database_name [, ...]\n FROM { [ GROUP ] role_name | PUBLIC } [, ...]\n [ CASCADE | RESTRICT ]\n\nREVOKE [ GRANT OPTION FOR ]\n { USAGE | ALL [ PRIVILEGES ] }\n ON DOMAIN domain_name [, ...]\n FROM { [ GROUP ] role_name | PUBLIC } [, ...]\n [ CASCADE | RESTRICT ]\n\nREVOKE [ GRANT OPTION FOR ]\n { USAGE | ALL [ PRIVILEGES ] }\n ON FOREIGN DATA WRAPPER fdw_name [, ...]\n FROM { [ GROUP ] role_name | PUBLIC } [, ...]\n [ CASCADE | RESTRICT ]\n\nREVOKE [ GRANT OPTION FOR ]\n { USAGE | ALL [ PRIVILEGES ] }\n ON FOREIGN SERVER server_name [, ...]\n FROM { [ GROUP ] role_name | PUBLIC } [, ...]\n [ CASCADE | RESTRICT ]\n\nREVOKE [ GRANT OPTION FOR ]\n { EXECUTE | ALL [ PRIVILEGES ] }\n ON { FUNCTION function_name ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) [, ...]\n | ALL FUNCTIONS IN SCHEMA schema_name [, ...] }\n FROM { [ GROUP ] role_name | PUBLIC } [, ...]\n [ CASCADE | RESTRICT ]\n\nREVOKE [ GRANT OPTION FOR ]\n { USAGE | ALL [ PRIVILEGES ] }\n ON LANGUAGE lang_name [, ...]\n FROM { [ GROUP ] role_name | PUBLIC } [, ...]\n [ CASCADE | RESTRICT ]\n\nREVOKE [ GRANT OPTION FOR ]\n { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }\n ON LARGE OBJECT loid [, ...]\n FROM { [ GROUP ] role_name | PUBLIC } [, ...]\n [ CASCADE | RESTRICT ]\n\nREVOKE [ GRANT OPTION FOR ]\n { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }\n ON SCHEMA schema_name [, ...]\n FROM { [ GROUP ] role_name | PUBLIC } [, ...]\n [ CASCADE | RESTRICT ]\n\nREVOKE [ GRANT OPTION FOR ]\n { CREATE | ALL [ PRIVILEGES ] }\n ON TABLESPACE tablespace_name [, ...]\n FROM { [ GROUP ] role_name | PUBLIC } [, ...]\n [ CASCADE | RESTRICT ]\n\nREVOKE [ GRANT OPTION FOR ]\n { USAGE | ALL [ PRIVILEGES ] }\n ON TYPE type_name [, ...]\n FROM { [ GROUP ] role_name | PUBLIC } [, ...]\n [ CASCADE | RESTRICT ]\n\nREVOKE [ ADMIN OPTION FOR ]\n role_name [, ...] FROM role_name [, ...]\n [ CASCADE | RESTRICT ]\n", + }, + "ROLLBACK": { + "description": "Description\nROLLBACK rolls back the current transaction and causes", + "synopsis": "\nROLLBACK [ WORK | TRANSACTION ]\n", + }, + "ROLLBACK PREPARED": { + "description": "Description\nROLLBACK PREPARED rolls back a transaction that is in", + "synopsis": "\nROLLBACK PREPARED transaction_id\n", + }, + "ROLLBACK TO": { + "description": "Description\n Roll back all commands that were executed after the savepoint was", + "synopsis": "\nROLLBACK [ WORK | TRANSACTION ] TO [ SAVEPOINT ] savepoint_name\n", + }, + "SAVEPOINT": { + "description": "Description\nSAVEPOINT establishes a new savepoint within", + "synopsis": "\nSAVEPOINT savepoint_name\n", + }, + "SECURITY LABEL": { + "description": "Description\nSECURITY LABEL applies a security label to a database", + "synopsis": "\nSECURITY LABEL [ FOR provider ] ON\n{\n TABLE object_name |\n COLUMN table_name.column_name |\n AGGREGATE aggregate_name ( aggregate_signature ) |\n DATABASE object_name |\n DOMAIN object_name |\n EVENT TRIGGER object_name |\n FOREIGN TABLE object_name\n FUNCTION function_name ( [ [ argmode ] [ argname ] argtype [, ...] ] ) |\n LARGE OBJECT large_object_oid |\n MATERIALIZED VIEW object_name |\n [ PROCEDURAL ] LANGUAGE object_name |\n ROLE object_name |\n SCHEMA object_name |\n SEQUENCE object_name |\n TABLESPACE object_name |\n TYPE object_name |\n VIEW object_name\n} IS 'label'\n\nwhere aggregate_signature is:\n\n* |\n[ argmode ] [ argname ] argtype [ , ... ] |\n[ [ argmode ] [ argname ] argtype [ , ... ] ] ORDER BY [ argmode ] [ argname ] argtype [ , ... ]\n", + }, + "SELECT": { + "description": "Description\nSELECT retrieves rows from zero or more tables.", + "synopsis": "\n[ WITH [ RECURSIVE ] with_query [, ...] ]\nSELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]\n [ * | expression [ [ AS ] output_name ] [, ...] ]\n [ FROM from_item [, ...] ]\n [ WHERE condition ]\n [ GROUP BY grouping_element [, ...] ]\n [ HAVING condition [, ...] ]\n [ WINDOW window_name AS ( window_definition ) [, ...] ]\n [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]\n [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]\n [ LIMIT { count | ALL } ]\n [ OFFSET start [ ROW | ROWS ] ]\n [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]\n [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]\n\nwhere from_item can be one of:\n\n [ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]\n [ TABLESAMPLE sampling_method ( argument [, ...] ) [ REPEATABLE ( seed ) ] ]\n [ LATERAL ] ( select ) [ AS ] alias [ ( column_alias [, ...] ) ]\n with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]\n [ LATERAL ] function_name ( [ argument [, ...] ] )\n [ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]\n [ LATERAL ] function_name ( [ argument [, ...] ] ) [ AS ] alias ( column_definition [, ...] )\n [ LATERAL ] function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )\n [ LATERAL ] ROWS FROM( function_name ( [ argument [, ...] ] ) [ AS ( column_definition [, ...] ) ] [, ...] )\n [ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]\n from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]\n\nand grouping_element can be one of:\n\n ( )\n expression\n ( expression [, ...] )\n ROLLUP ( { expression | ( expression [, ...] ) } [, ...] )\n CUBE ( { expression | ( expression [, ...] ) } [, ...] )\n GROUPING SETS ( grouping_element [, ...] )\n\nand with_query is:\nwith_query_name [ ( column_name [, ...] ) ] AS ( select | values | insert | update | delete )\n\nTABLE [ ONLY ] table_name [ * ]\n", + }, + "SELECT INTO": { + "description": "Description\nSELECT INTO creates a new table and fills it", + "synopsis": "\n[ WITH [ RECURSIVE ] with_query [, ...] ]\nSELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]\n * | expression [ [ AS ] output_name ] [, ...]\n INTO [ TEMPORARY | TEMP | UNLOGGED ] [ TABLE ] new_table\n [ FROM from_item [, ...] ]\n [ WHERE condition ]\n [ GROUP BY expression [, ...] ]\n [ HAVING condition [, ...] ]\n [ WINDOW window_name AS ( window_definition ) [, ...] ]\n [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]\n [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]\n [ LIMIT { count | ALL } ]\n [ OFFSET start [ ROW | ROWS ] ]\n [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]\n [ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ]\n", + }, + "SET": { + "description": "Description\n The SET command changes run-time configuration", + "synopsis": "\nSET [ SESSION | LOCAL ] configuration_parameter { TO | = } { value | 'value' | DEFAULT }\nSET [ SESSION | LOCAL ] TIME ZONE { timezone | LOCAL | DEFAULT }\n", + }, + "SET CONSTRAINTS": { + "description": "Description\nSET CONSTRAINTS sets the behavior of constraint", + "synopsis": "\nSET CONSTRAINTS { ALL | name [, ...] } { DEFERRED | IMMEDIATE }\n", + }, + "SET ROLE": { + "description": "Description\n This command sets the current user", + "synopsis": "\nSET [ SESSION | LOCAL ] ROLE role_name\nSET [ SESSION | LOCAL ] ROLE NONE\nRESET ROLE\n", + }, + "SET SESSION AUTH": { + "description": "Description\n This command sets the session user identifier and the current user", + "synopsis": "\nSET [ SESSION | LOCAL ] SESSION AUTHORIZATION user_name\nSET [ SESSION | LOCAL ] SESSION AUTHORIZATION DEFAULT\nRESET SESSION AUTHORIZATION\n", + }, + "SET TRANSACTION": { + "description": "Description\n The SET TRANSACTION command sets the", + "synopsis": "\nSET TRANSACTION transaction_mode [, ...]\nSET TRANSACTION SNAPSHOT snapshot_id\nSET SESSION CHARACTERISTICS AS TRANSACTION transaction_mode [, ...]\n\nwhere transaction_mode is one of:\n\n ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }\n READ WRITE | READ ONLY\n [ NOT ] DEFERRABLE\n", + }, + "SHOW": { + "description": "Description\nSHOW will display the current setting of", + "synopsis": "\nSHOW name\nSHOW ALL\n", + }, + "START TRANSACTION": { + "description": "Description\n This command begins a new transaction block. If the isolation level,", + "synopsis": "\nSTART TRANSACTION [ transaction_mode [, ...] ]\n\nwhere transaction_mode is one of:\n\n ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }\n READ WRITE | READ ONLY\n [ NOT ] DEFERRABLE\n", + }, + "TRUNCATE": { + "description": "Description\nTRUNCATE quickly removes all rows from a set of", + "synopsis": "\nTRUNCATE [ TABLE ] [ ONLY ] name [ * ] [, ... ]\n [ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ]\n", + }, + "UNLISTEN": { + "description": "Description\nUNLISTEN is used to remove an existing", + "synopsis": "\nUNLISTEN { channel | * }\n", + }, + "UPDATE": { + "description": "Description\nUPDATE changes the values of the specified", + "synopsis": "\n[ WITH [ RECURSIVE ] with_query [, ...] ]\nUPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]\n SET { column_name = { expression | DEFAULT } |\n ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) |\n ( column_name [, ...] ) = ( sub-SELECT )\n } [, ...]\n [ FROM from_list ]\n [ WHERE condition | WHERE CURRENT OF cursor_name ]\n [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]\n", + }, + "VACUUM": { + "description": "Description\nVACUUM reclaims storage occupied by dead tuples.", + "synopsis": "\nVACUUM [ ( { FULL | FREEZE | VERBOSE | ANALYZE } [, ...] ) ] [ table_name [ (column_name [, ...] ) ] ]\nVACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table_name ]\nVACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table_name [ (column_name [, ...] ) ] ]\n", + }, + "VALUES": { + "description": "Description\nVALUES computes a row value or set of row values", + "synopsis": "\nVALUES ( expression [, ...] ) [, ...]\n [ ORDER BY sort_expression [ ASC | DESC | USING operator ] [, ...] ]\n [ LIMIT { count | ALL } ]\n [ OFFSET start [ ROW | ROWS ] ]\n [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]\n", + }, +} diff --git a/pgspecial/iocommands.py b/pgspecial/iocommands.py new file mode 100644 index 0000000..1a4bc8f --- /dev/null +++ b/pgspecial/iocommands.py @@ -0,0 +1,342 @@ +from __future__ import unicode_literals +import re +import sys +import logging +import click +import io +import shlex +import sqlparse +import psycopg +from os.path import expanduser +from .namedqueries import NamedQueries +from . import export +from .main import show_extra_help_command, special_command + +NAMED_QUERY_PLACEHOLDERS = frozenset({"$1", "$*", "$@"}) + +DEFAULT_WATCH_SECONDS = 2 + +_logger = logging.getLogger(__name__) + + +@export +def editor_command(command): + """ + Is this an external editor command? (\\e or \\ev) + + :param command: string + + Returns the specific external editor command found. + """ + # It is possible to have `\e filename` or `SELECT * FROM \e`. So we check + # for both conditions. + + stripped = command.strip() + for sought in ("\\e ", "\\ev ", "\\ef "): + if stripped.startswith(sought): + return sought.strip() + for sought in ("\\e",): + if stripped.endswith(sought): + return sought + + +@export +def get_filename(sql): + if sql.strip().startswith("\\e"): + command, _, filename = sql.partition(" ") + return filename.strip() or None + + +@export +@show_extra_help_command( + "\\watch", + f"\\watch [sec={DEFAULT_WATCH_SECONDS}]", + "Execute query every `sec` seconds.", +) +def get_watch_command(command): + match = re.match(r"(.*?)[\s]*\\watch(\s+\d+)?\s*;?\s*$", command, re.DOTALL) + if match: + groups = match.groups(default=f"{DEFAULT_WATCH_SECONDS}") + return groups[0], int(groups[1]) + return None, None + + +@export +def get_editor_query(sql): + """Get the query part of an editor command.""" + sql = sql.strip() + + # The reason we can't simply do .strip('\e') is that it strips characters, + # not a substring. So it'll strip "e" in the end of the sql also! + # Ex: "select * from style\e" -> "select * from styl". + pattern = re.compile(r"(^\\e|\\e$)") + while pattern.search(sql): + sql = pattern.sub("", sql) + + return sql + + +@export +def open_external_editor(filename=None, sql=None, editor=None): + """ + Open external editor, wait for the user to type in his query, + return the query. + :return: list with one tuple, query as first element. + """ + + message = None + filename = filename.strip().split(" ", 1)[0] if filename else None + + sql = sql or "" + MARKER = "# Type your query above this line.\n" + + # Populate the editor buffer with the partial sql (if available) and a + # placeholder comment. + query = click.edit( + "{sql}\n\n{marker}".format(sql=sql, marker=MARKER), + filename=filename, + extension=".sql", + editor=editor, + ) + + if filename: + try: + query = read_from_file(filename) + except IOError: + message = "Error reading file: %s." % filename + + if query is not None: + query = query.split(MARKER, 1)[0].rstrip("\n") + else: + # Don't return None for the caller to deal with. + # Empty string is ok. + query = sql + + return (query, message) + + +def read_from_file(path): + with io.open(expanduser(path), encoding="utf-8") as f: + contents = f.read() + return contents + + +def _index_of_file_name(tokenlist): + for idx, token in reversed(list(enumerate(tokenlist[:-2]))): + if token.is_keyword and token.value.upper() in ("TO", "FROM"): + return idx + 2 + raise Exception("Missing keyword in \\copy command. Either TO or FROM is required.") + + +@special_command( + "\\copy", + "\\copy [tablename] to/from [filename]", + "Copy data between a file and a table.", +) +def copy(cur, pattern, verbose): + """Copies table data to/from files""" + + # Replace the specified file destination with STDIN or STDOUT + parsed = sqlparse.parse(pattern) + tokens = parsed[0].tokens + idx = _index_of_file_name(tokens) + file_name = tokens[idx].value + before_file_name = "".join(t.value for t in tokens[:idx]) + after_file_name = "".join(t.value for t in tokens[idx + 1 :]) + + direction = tokens[idx - 2].value.upper() + replacement_file_name = "STDIN" if direction == "FROM" else "STDOUT" + query = f"{before_file_name} {replacement_file_name} {after_file_name}" + open_mode = "r" if direction == "FROM" else "wb" + if file_name.startswith("'") and file_name.endswith("'"): + file = io.open(expanduser(file_name.strip("'")), mode=open_mode) + elif "stdin" in file_name.lower(): + file = sys.stdin.buffer + elif "stdout" in file_name.lower(): + file = sys.stdout.buffer + else: + raise Exception("Enclose filename in single quotes") + + if direction == "FROM": + with cur.copy("copy " + query) as pgcopy: + while True: + data = file.read(8192) + if not data: + break + pgcopy.write(data) + else: + with cur.copy("copy " + query) as pgcopy: + for data in pgcopy: + file.write(bytes(data)) + + if cur.description: + headers = [x.name for x in cur.description] + return [(None, None, headers, cur.statusmessage)] + else: + return [(None, None, None, cur.statusmessage)] + + +def subst_favorite_query_args(query, args): + """replace positional parameters ($1,$2,...$n) in query.""" + is_query_with_aggregation = ("$*" in query) or ("$@" in query) + + # In case of arguments aggregation we replace all positional arguments until the + # first one not present in the query. Then we aggregate all the remaining ones and + # replace the placeholder with them. + for idx, val in enumerate(args, start=1): + subst_var = "$" + str(idx) + if subst_var not in query: + if is_query_with_aggregation: + # remove consumed arguments ( - 1 to include current value) + args = args[idx - 1 :] + break + + return [ + None, + "query does not have substitution parameter " + + subst_var + + ":\n " + + query, + ] + + query = query.replace(subst_var, val) + # we consumed all arguments + else: + args = [] + + if is_query_with_aggregation and not args: + return [None, "missing substitution for $* or $@ in query:\n" + query] + + if "$*" in query: + query = query.replace("$*", ", ".join(args)) + elif "$@" in query: + query = query.replace("$@", ", ".join(map("'{}'".format, args))) + + match = re.search("\\$\\d+", query) + if match: + return [ + None, + "missing substitution for " + match.group(0) + " in query:\n " + query, + ] + + return [query, None] + + +@special_command( + "\\n", "\\n[+] [name] [param1 param2 ...]", "List or execute named queries." +) +def execute_named_query(cur, pattern, **_): + """Returns (title, rows, headers, status)""" + if pattern == "": + return list_named_queries(True) + + params = shlex.split(pattern) + pattern = params.pop(0) + + query = NamedQueries.instance.get(pattern) + title = "> {}".format(query) + if query is None: + message = "No named query: {}".format(pattern) + return [(None, None, None, message)] + + try: + if any(p in query for p in NAMED_QUERY_PLACEHOLDERS): + query, params = subst_favorite_query_args(query, params) + if query is None: + raise Exception("Bad arguments\n" + params) + cur.execute(query) + except psycopg.errors.SyntaxError: + if "%s" in query: + raise Exception( + "Bad arguments: " + 'please use "$1", "$2", etc. for named queries instead of "%s"' + ) + else: + raise + except (IndexError, TypeError): + raise Exception("Bad arguments") + + if cur.description: + headers = [x.name for x in cur.description] + return [(title, cur, headers, cur.statusmessage)] + else: + return [(title, None, None, cur.statusmessage)] + + +def list_named_queries(verbose): + """List of all named queries. + Returns (title, rows, headers, status)""" + if not verbose: + rows = [[r] for r in NamedQueries.instance.list()] + headers = ["Name"] + else: + headers = ["Name", "Query"] + rows = [[r, NamedQueries.instance.get(r)] for r in NamedQueries.instance.list()] + + if not rows: + status = NamedQueries.instance.usage + else: + status = "" + return [("", rows, headers, status)] + + +@special_command("\\np", "\\np name_pattern", "Print a named query.") +def get_named_query(pattern, **_): + """Get a named query that matches name_pattern. + + The named pattern can be a regular expression. Returns (title, + rows, headers, status) + + """ + + usage = "Syntax: \\np name.\n\n" + NamedQueries.instance.usage + if not pattern: + return [(None, None, None, usage)] + + name = pattern.strip() + if not name: + return [(None, None, None, usage + "Err: A name is required.")] + + headers = ["Name", "Query"] + rows = [ + (r, NamedQueries.instance.get(r)) + for r in NamedQueries.instance.list() + if re.search(name, r) + ] + + status = "" + if not rows: + status = "No match found" + + return [("", rows, headers, status)] + + +@special_command("\\ns", "\\ns name query", "Save a named query.") +def save_named_query(pattern, **_): + """Save a new named query. + Returns (title, rows, headers, status)""" + + usage = "Syntax: \\ns name query.\n\n" + NamedQueries.instance.usage + if not pattern: + return [(None, None, None, usage)] + + name, _, query = pattern.partition(" ") + + # If either name or query is missing then print the usage and complain. + if (not name) or (not query): + return [(None, None, None, usage + "Err: Both name and query are required.")] + + NamedQueries.instance.save(name, query) + return [(None, None, None, "Saved.")] + + +@special_command("\\nd", "\\nd [name]", "Delete a named query.") +def delete_named_query(pattern, **_): + """Delete an existing named query.""" + usage = "Syntax: \\nd name.\n\n" + NamedQueries.instance.usage + if not pattern: + return [(None, None, None, usage)] + + status = NamedQueries.instance.delete(pattern) + + return [(None, None, None, status)] diff --git a/pgspecial/main.py b/pgspecial/main.py new file mode 100644 index 0000000..b13191e --- /dev/null +++ b/pgspecial/main.py @@ -0,0 +1,349 @@ +from __future__ import unicode_literals +import os +import logging +from collections import namedtuple + +from . import export +from .help.commands import helpcommands + +log = logging.getLogger(__name__) + +NO_QUERY = 0 +PARSED_QUERY = 1 +RAW_QUERY = 2 + +PAGER_ALWAYS = 2 +PAGER_LONG_OUTPUT = 1 +PAGER_OFF = 0 + +PAGER_MSG = { + PAGER_OFF: "Pager usage is off.", + PAGER_LONG_OUTPUT: "Pager is used for long output.", + PAGER_ALWAYS: "Pager is always used.", +} + +SpecialCommand = namedtuple( + "SpecialCommand", + ["handler", "syntax", "description", "arg_type", "hidden", "case_sensitive"], +) + + +@export +class CommandNotFound(Exception): + pass + + +@export +class PGSpecial(object): + # Default static commands that don't rely on PGSpecial state are registered + # via the special_command decorator and stored in default_commands + default_commands = {} + + def __init__(self): + self.timing_enabled = True + + self.commands = self.default_commands.copy() + self.timing_enabled = False + self.expanded_output = False + self.auto_expand = False + self.pager_config = PAGER_ALWAYS + self.pager = os.environ.get("PAGER", "") + + self.register( + self.show_help, "\\?", "\\?", "Show Commands.", arg_type=PARSED_QUERY + ) + + self.register( + self.toggle_expanded_output, + "\\x", + "\\x", + "Toggle expanded output.", + arg_type=PARSED_QUERY, + ) + + self.register( + self.call_pset, + "\\pset", + "\\pset [key] [value]", + "A limited version of traditional \\pset", + arg_type=PARSED_QUERY, + ) + + self.register( + self.show_command_help, + "\\h", + "\\h", + "Show SQL syntax and help.", + arg_type=PARSED_QUERY, + ) + + self.register( + self.toggle_timing, + "\\timing", + "\\timing", + "Toggle timing of commands.", + arg_type=NO_QUERY, + ) + + self.register( + self.set_pager, + "\\pager", + "\\pager [command]", + "Set PAGER. Print the query results via PAGER.", + arg_type=PARSED_QUERY, + ) + + def register(self, *args, **kwargs): + register_special_command(*args, command_dict=self.commands, **kwargs) + + def execute(self, cur, sql): + commands = self.commands + command, verbose, pattern = parse_special_command(sql) + + if (command not in commands) and (command.lower() not in commands): + raise CommandNotFound + + try: + special_cmd = commands[command] + except KeyError: + special_cmd = commands[command.lower()] + if special_cmd.case_sensitive: + raise CommandNotFound("Command not found: %s" % command) + + if special_cmd.arg_type == NO_QUERY: + return special_cmd.handler() + elif special_cmd.arg_type == PARSED_QUERY: + return special_cmd.handler(cur=cur, pattern=pattern, verbose=verbose) + elif special_cmd.arg_type == RAW_QUERY: + return special_cmd.handler(cur=cur, query=sql) + + def show_help(self, pattern, **_): + if pattern.strip(): + return self.show_command_help(pattern) + + headers = ["Command", "Description"] + result = [] + + for _, value in sorted(self.commands.items()): + if not value.hidden: + result.append((value.syntax, value.description)) + return [(None, result, headers, None)] + + def show_command_help_listing(self): + table = chunks(sorted(helpcommands.keys()), 6) + return [(None, table, [], None)] + + def show_command_help(self, pattern, **_): + command = pattern.strip().upper() + message = "" + + if not command: + return self.show_command_help_listing() + + if command in helpcommands: + helpcommand = helpcommands[command] + + if "description" in helpcommand: + message += helpcommand["description"] + if "synopsis" in helpcommand: + message += "\nSyntax:\n" + message += helpcommand["synopsis"] + else: + message = 'No help available for "%s"' % pattern + message += "\nTry \\h with no arguments to see available help." + + return [(None, None, None, message)] + + def toggle_expanded_output(self, pattern, **_): + flag = pattern.strip() + if flag == "auto": + self.auto_expand = True + self.expanded_output = False + return [(None, None, None, "Expanded display is used automatically.")] + elif flag == "off": + self.expanded_output = False + elif flag == "on": + self.expanded_output = True + else: + self.expanded_output = not (self.expanded_output or self.auto_expand) + + self.auto_expand = self.expanded_output + message = "Expanded display is " + message += "on." if self.expanded_output else "off." + return [(None, None, None, message)] + + def toggle_timing(self): + self.timing_enabled = not self.timing_enabled + message = "Timing is " + message += "on." if self.timing_enabled else "off." + return [(None, None, None, message)] + + def call_pset(self, pattern, **_): + pattern = pattern.split(" ", 2) + val = pattern[1] if len(pattern) > 1 else "" + key = pattern[0] + if hasattr(self, "pset_" + key): + return getattr(self, "pset_" + key)(val) + else: + return [(None, None, None, "'%s' is currently not supported by pset" % key)] + + def pset_pager(self, value): + if value == "always": + self.pager_config = PAGER_ALWAYS + elif value == "off": + self.pager_config = PAGER_OFF + elif value == "on": + self.pager_config = PAGER_LONG_OUTPUT + elif self.pager_config == PAGER_LONG_OUTPUT: + self.pager_config = PAGER_OFF + else: + self.pager_config = PAGER_LONG_OUTPUT + return [(None, None, None, "%s" % PAGER_MSG[self.pager_config])] + + def set_pager(self, pattern, **_): + if not pattern: + if not self.pager: + os.environ.pop("PAGER", None) + msg = "Pager reset to system default." + else: + os.environ["PAGER"] = self.pager + msg = "Reset pager back to default. Default: %s" % self.pager + else: + os.environ["PAGER"] = pattern + msg = "PAGER set to %s." % pattern + + return [(None, None, None, msg)] + + +@export +def content_exceeds_width(row, width): + # Account for 3 characters between each column + separator_space = len(row) * 3 + # Add 2 columns for a bit of buffer + line_len = sum([len(x) for x in row]) + separator_space + 2 + return line_len > width + + +@export +def parse_special_command(sql): + command, _, arg = sql.partition(" ") + verbose = "+" in command + + command = command.strip().replace("+", "") + return (command, verbose, arg.strip()) + + +def show_extra_help_command(command, syntax, description): + r""" + A decorator used internally for registering help for a command that is not + automatically executed via PGSpecial.execute, but invoked manually by the + caller (e.g. \watch). + """ + + @special_command(command, syntax, description, arg_type=NO_QUERY) + def placeholder(): + raise RuntimeError + + def wrapper(wrapped): + return wrapped + + return wrapper + + +def special_command( + command, + syntax, + description, + arg_type=PARSED_QUERY, + hidden=False, + case_sensitive=True, + aliases=(), +): + """A decorator used internally for static special commands""" + + def wrapper(wrapped): + register_special_command( + wrapped, + command, + syntax, + description, + arg_type, + hidden, + case_sensitive, + aliases, + command_dict=PGSpecial.default_commands, + ) + return wrapped + + return wrapper + + +def register_special_command( + handler, + command, + syntax, + description, + arg_type=PARSED_QUERY, + hidden=False, + case_sensitive=True, + aliases=(), + command_dict=None, +): + cmd = command.lower() if not case_sensitive else command + command_dict[cmd] = SpecialCommand( + handler, syntax, description, arg_type, hidden, case_sensitive + ) + for alias in aliases: + cmd = alias.lower() if not case_sensitive else alias + command_dict[cmd] = SpecialCommand( + handler, + syntax, + description, + arg_type, + case_sensitive=case_sensitive, + hidden=True, + ) + + +def chunks(l, n): + n = max(1, n) + return [l[i : i + n] for i in range(0, len(l), n)] + + +@special_command( + "\\e", "\\e [file]", "Edit the query with external editor.", arg_type=NO_QUERY +) +@special_command( + "\\ef", + "\\ef [funcname [line]]", + "Edit the contents of the query buffer.", + arg_type=NO_QUERY, + hidden=True, +) +@special_command( + "\\ev", + "\\ev [viewname [line]]", + "Edit the contents of the query buffer.", + arg_type=NO_QUERY, + hidden=True, +) +def doc_only(): + "Documention placeholder. Implemented in pgcli.main.handle_editor_command" + raise RuntimeError + + +@special_command( + "\\do", "\\do[S] [pattern]", "List operators.", arg_type=NO_QUERY, hidden=True +) +@special_command( + "\\dp", + "\\dp [pattern]", + "List table, view, and sequence access privileges.", + arg_type=NO_QUERY, + hidden=True, +) +@special_command( + "\\z", "\\z [pattern]", "Same as \\dp.", arg_type=NO_QUERY, hidden=True +) +def place_holder(): + raise NotImplementedError diff --git a/pgspecial/namedqueries.py b/pgspecial/namedqueries.py new file mode 100644 index 0000000..ef46617 --- /dev/null +++ b/pgspecial/namedqueries.py @@ -0,0 +1,61 @@ +# -*- coding: utf-8 -*- +class NamedQueries(object): + section_name = "named queries" + + usage = """Named Queries are a way to save frequently used queries +with a short name. Think of them as favorites. +Examples: + + # Save a new named query. + > \\ns simple select * from abc where a is not Null; + + # List all named queries. + > \\n + +--------+----------------------------------------+ + | Name | Query | + |--------+----------------------------------------| + | simple | SELECT * FROM xyzb where a is not null | + +--------+----------------------------------------+ + + # Run a named query. + > \\n simple + +-----+ + | a | + |-----| + | 50 | + +-----+ + + # Delete a named query. + > \\nd simple + simple: Deleted +""" + + # Class-level variable, for convenience to use as a singleton. + instance = None + + def __init__(self, config): + self.config = config + + @classmethod + def from_config(cls, config): + return NamedQueries(config) + + def list(self): + return self.config.get(self.section_name, []) + + def get(self, name): + return self.config.get(self.section_name, {}).get(name, None) + + def save(self, name, query): + if self.section_name not in self.config: + self.config[self.section_name] = {} + self.config[self.section_name][name] = query + self.config.write() + + def delete(self, name): + try: + del self.config[self.section_name][name] + except KeyError: + return "%s: Not Found." % name + self.config.write() + return "%s: Deleted" % name diff --git a/pyproject.toml b/pyproject.toml new file mode 100644 index 0000000..9001390 --- /dev/null +++ b/pyproject.toml @@ -0,0 +1,32 @@ +[build-system] +requires = ["setuptools"] +build-backend = "setuptools.build_meta" + +[tool.black] +line-length = 88 +target-version = ['py37'] +include = '\.pyi?$' +exclude = ''' +/( + \.eggs + | \.git + | \.hg + | \.mypy_cache + | \.tox + | \.venv + | \.cache + | \.pytest_cache + | _build + | buck-out + | build + | dist + | tests/data +)/ +''' + +[tool.pytest.ini_options] +minversion = "6.0" +addopts = "--capture=sys --showlocals -rxs" +testpaths = [ + "tests", +] diff --git a/requirements-dev.txt b/requirements-dev.txt new file mode 100644 index 0000000..bd13eb4 --- /dev/null +++ b/requirements-dev.txt @@ -0,0 +1,11 @@ +pytest>=6.2.4 +tox>=1.9.2 +autopep8==1.3.3 +coverage +codecov>=1.5.1 +pre-commit>=1.16.0 +black>=20.8b1 +configobj>=5.0.6 +twine==1.11.0 +wheel==0.38.1 +build==1.0.3
\ No newline at end of file diff --git a/scripts/README.rst b/scripts/README.rst new file mode 100644 index 0000000..6d96afd --- /dev/null +++ b/scripts/README.rst @@ -0,0 +1,21 @@ +Scripts +-------------------------- + +**docparser.py** + +Parses SGML files containing Postgres command information and converts them +into a JSON data structure, this is the converted into a python dictionary +and saved to `pgspecial/help/commands.py` + +This should me manually ran and the results committed after each new release +of the main Postgres project. + +SGML files can be found: https://github.com/postgres/postgres/tree/master/doc/src/sgml/ref +Grab a copy of this directory on your local system. + +**Usage** + +:: + pip install beautifulsoup4 + # From root of project + echo -n "helpcommands = " > pgspecial/help/commands.py; python scripts/docparser.py ref/ | python -mjson.tool | sed 's/"\: null/": None/g' >> pgspecial/help/commands.py diff --git a/scripts/docparser.py b/scripts/docparser.py new file mode 100644 index 0000000..a6019c9 --- /dev/null +++ b/scripts/docparser.py @@ -0,0 +1,51 @@ +from bs4 import BeautifulSoup +import sys +import os +import json + + +def get_section(doc, section): + element = doc.find(section) + if element: + return element.get_text() + + +def get_description(doc): + text = get_section(doc, "refsect1") + if text: + lines = filter(lambda x: x.strip(), text.split("\n")) + + if len(lines) > 1 and lines[0] == "Description": + return lines[0] + "\n" + lines[1] + + +def parse(file_name): + with open(file_name, "r") as file: + doc = BeautifulSoup(file.read(), "html.parser") + desc = get_description(doc) + synopsis = get_section(doc, "synopsis") + if desc and synopsis: + return {"description": desc, "synopsis": synopsis} + + +if __name__ == "__main__": + if len(sys.argv) < 2: + print("Parse postgres SGML reference files into JSON") + print("Usage:") + print( + 'echo -n "commands = " > command_help.py; python parser.py ref/ | python -mjson.tool | sed \'s/"\\: null/": None/g\' >> command_help.py' + ) + print("") + sys.exit(0) + + dir = sys.argv[1] + docs = {} + + for file_name in os.listdir(dir): + if file_name.endswith(".sgml"): + path = dir.rstrip("/") + "/" + file_name + command = file_name[:-5].replace("_", " ") + parsed = parse(path) + if parsed: + docs[command.upper()] = parsed + print(json.dumps(docs)) diff --git a/setup.cfg b/setup.cfg new file mode 100644 index 0000000..8bfd5a1 --- /dev/null +++ b/setup.cfg @@ -0,0 +1,4 @@ +[egg_info] +tag_build = +tag_date = 0 + diff --git a/setup.py b/setup.py new file mode 100644 index 0000000..7a9bf2a --- /dev/null +++ b/setup.py @@ -0,0 +1,47 @@ +import re +import ast +from setuptools import setup, find_packages + +_version_re = re.compile(r"__version__\s+=\s+(.*)") + +with open("pgspecial/__init__.py", "rb") as f: + version = str( + ast.literal_eval(_version_re.search(f.read().decode("utf-8")).group(1)) + ) + +description = "Meta-commands handler for Postgres Database." + + +setup( + name="pgspecial", + author="Pgcli Core Team", + author_email="pgcli-dev@googlegroups.com", + version=version, + license="LICENSE.txt", + url="https://www.dbcli.com", + packages=find_packages(), + description=description, + long_description=open("README.rst").read(), + install_requires=[ + "click >= 4.1", + "sqlparse >= 0.1.19", + "psycopg >= 3.0.10", + ], + classifiers=[ + "Intended Audience :: Developers", + "License :: OSI Approved :: BSD License", + "Operating System :: Unix", + "Programming Language :: Python", + "Programming Language :: Python :: 3", + "Programming Language :: Python :: 3.7", + "Programming Language :: Python :: 3.8", + "Programming Language :: Python :: 3.9", + "Programming Language :: Python :: 3.10", + "Programming Language :: Python :: 3.11", + "Programming Language :: SQL", + "Topic :: Database", + "Topic :: Database :: Front-Ends", + "Topic :: Software Development", + "Topic :: Software Development :: Libraries :: Python Modules", + ], +) diff --git a/tests/conftest.py b/tests/conftest.py new file mode 100644 index 0000000..0516dad --- /dev/null +++ b/tests/conftest.py @@ -0,0 +1,40 @@ +import pytest +from dbutils import create_db, db_connection, setup_db, teardown_db, TEST_DB_NAME +import locale +from pgspecial.main import PGSpecial + + +locale.setlocale(locale.LC_ALL, "") + + +@pytest.fixture(scope="module") +def connection(): + create_db(TEST_DB_NAME) + connection = db_connection(TEST_DB_NAME) + setup_db(connection) + yield connection + teardown_db(connection) + connection.close() + + +@pytest.fixture +def cursor(connection): + with connection.cursor() as cur: + return cur + + +@pytest.fixture +def executor(connection): + cur = connection.cursor() + pgspecial = PGSpecial() + + def query_runner(sql): + results = [] + for title, rows, headers, status in pgspecial.execute(cur=cur, sql=sql): + if rows: + results.extend((title, list(rows), headers, status)) + else: + results.extend((title, None, headers, status)) + return results + + return query_runner diff --git a/tests/dbutils.py b/tests/dbutils.py new file mode 100644 index 0000000..36951ac --- /dev/null +++ b/tests/dbutils.py @@ -0,0 +1,186 @@ +from contextlib import contextmanager + +import pytest +import psycopg +from os import getenv + + +# TODO: should this be somehow be divined from environment? +POSTGRES_USER = getenv("PGUSER", "postgres") +POSTGRES_HOST = getenv("PGHOST", "localhost") +POSTGRES_PORT = getenv("PGPORT", 5432) +POSTGRES_PASSWORD = getenv("PGPASSWORD", "postgres") + +TEST_DB_NAME = "_test_db" +FOREIGN_TEST_DB_NAME = "_foreign_test_db" + + +def db_connection(dbname=None): + conn = psycopg.connect( + user=POSTGRES_USER, + host=POSTGRES_HOST, + password=POSTGRES_PASSWORD, + port=POSTGRES_PORT, + dbname=dbname, + ) + conn.autocommit = True + return conn + + +try: + conn = db_connection(dbname=None) + CAN_CONNECT_TO_DB = True + SERVER_VERSION = conn.info.server_version +except: + CAN_CONNECT_TO_DB = False + SERVER_VERSION = 0 + + +dbtest = pytest.mark.skipif( + not CAN_CONNECT_TO_DB, + reason="Need a postgres instance at localhost accessible by user " + "'%s'" % POSTGRES_USER, +) + + +def create_db(dbname=TEST_DB_NAME): + with db_connection(dbname=None).cursor() as cur: + try: + cur.execute( + """CREATE DATABASE %s encoding UTF8 LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8' template template0""" + % dbname + ) + except: + pass + + +def setup_db(conn): + with conn.cursor() as cur: + # schemas + cur.execute("create schema schema1") + cur.execute("create schema schema2") + cur.execute("create schema schema3") + + # tables + cur.execute( + "create table tbl1(id1 integer, txt1 text, CONSTRAINT id_text PRIMARY KEY(id1, txt1))" + ) + cur.execute("create table tbl2(id2 serial, txt2 text)") + cur.execute("create table schema2.tbl2(id2 serial, txt2 text)") + cur.execute("create table schema1.tbl2(id2 serial, txt2 text)") + cur.execute("create table schema1.s1_tbl1(id1 integer, txt1 text)") + cur.execute("create table tbl3(c3 circle, exclude using gist (c3 with &&))") + cur.execute('create table "Inh1"(value1 integer) inherits (tbl1)') + cur.execute("create table inh2(value2 integer) inherits (tbl1, tbl2)") + cur.execute( + """ + create table schema3.test_generated_default + (id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, some_stuff text)""" + ) + + # views + cur.execute("create view vw1 as select * from tbl1") + cur.execute( + """create view schema1.s1_vw1 as + select * from schema1.s1_tbl1""" + ) + + # materialized views + cur.execute("create materialized view mvw1 as select * from tbl1") + cur.execute( + """create materialized view schema1.s1_mvw1 as + select * from schema1.s1_tbl1""" + ) + + # datatype + cur.execute("create type foo AS (a int, b text)") + + # functions + cur.execute( + """create function func1() returns int language sql as + $$select 1$$""" + ) + cur.execute( + """create function schema1.s1_func1() returns int language + sql as $$select 2$$""" + ) + + # domains + cur.execute("create domain gender_t char(1)" " check (value in ('F', 'M'))") + cur.execute("create domain schema1.smallint_t smallint") + cur.execute("create domain schema1.bigint_t bigint") + cur.execute("comment on domain schema1.bigint_t is" " 'a really large integer'") + + # privileges + cur.execute("CREATE ROLE test_role;") + cur.execute( + "ALTER DEFAULT PRIVILEGES IN SCHEMA schema1 GRANT SELECT ON TABLES TO test_role;" + ) + cur.execute( + "ALTER DEFAULT PRIVILEGES IN SCHEMA schema2 GRANT ALL ON TABLES TO test_role;" + ) + + +def teardown_db(conn): + with conn.cursor() as cur: + cur.execute( + """ + DROP SCHEMA public CASCADE; + CREATE SCHEMA public; + DROP SCHEMA IF EXISTS schema1 CASCADE; + DROP SCHEMA IF EXISTS schema2 CASCADE; + DROP SCHEMA IF EXISTS schema3 CASCADE; + DROP ROLE IF EXISTS test_role;""" + ) + + +def setup_foreign(conn): + foreign_conn = db_connection(FOREIGN_TEST_DB_NAME) + with foreign_conn.cursor() as foreign_cur: + foreign_cur.execute("create table if not exists foreign_foo (a int, b text)") + + with conn.cursor() as cur: + # foreign database wrapper + cur.execute("create extension if not exists postgres_fdw") + cur.execute( + "create server if not exists foreign_db_server " + "foreign data wrapper postgres_fdw " + f"options (host '127.0.0.1', dbname '{FOREIGN_TEST_DB_NAME}') " + ) + cur.execute( + "create user mapping if not exists for current_user " + "server foreign_db_server " + "options (user 'postgres') " + ) + cur.execute( + "create foreign table if not exists foreign_foo (a int, b text) " + "server foreign_db_server " + "options (schema_name 'public', table_name 'foreign_foo') " + ) + + +def teardown_foreign(conn): + with conn.cursor() as cur: + cur.execute("drop server if exists foreign_db_server cascade") + cur.execute("drop extension if exists postgres_fdw") + cur.execute(f"drop database if exists {FOREIGN_TEST_DB_NAME}") + + +@contextmanager +def foreign_db_environ(): + conn2 = db_connection(dbname=TEST_DB_NAME) + create_db(FOREIGN_TEST_DB_NAME) + setup_foreign(conn2) + yield + teardown_foreign(conn2) + + +try: + with foreign_db_environ(): + CAN_CREATE_FDW_EXTENSION = True +except: + CAN_CREATE_FDW_EXTENSION = False + +fdw_test = pytest.mark.skipif( + not CAN_CREATE_FDW_EXTENSION, reason="Unable to create a postgres_fdw extension." +) diff --git a/tests/test_internal.py b/tests/test_internal.py new file mode 100644 index 0000000..64cca4e --- /dev/null +++ b/tests/test_internal.py @@ -0,0 +1,120 @@ +""" +Tests for specific internal functions, not overall integration tests. +""" +import pytest + +from pgspecial import iocommands + + +@pytest.mark.parametrize( + "command,expected_watch_command,expected_timing", + [ + ("SELECT * FROM foo \\watch", "SELECT * FROM foo", 2), + ("SELECT * FROM foo \\watch 123", "SELECT * FROM foo", 123), + ("SELECT *\nFROM foo \\watch 1", "SELECT *\nFROM foo", 1), + ("SELECT * FROM foo \\watch 1 ", "SELECT * FROM foo", 1), + ("SELECT * FROM foo; \\watch 1 ; ", "SELECT * FROM foo;", 1), + ("SELECT * FROM foo;\\watch 1;", "SELECT * FROM foo;", 1), + ], +) +def test_get_watch_command(command, expected_watch_command, expected_timing): + assert iocommands.get_watch_command(command) == ( + expected_watch_command, + expected_timing, + ) + + +def test_plain_editor_commands_detected(): + assert not iocommands.editor_command("select * from foo") + assert not iocommands.editor_command(r"\easy does it") + + assert iocommands.editor_command(r"\e") == r"\e" + assert iocommands.editor_command(r"\e myfile.txt") == r"\e" + assert iocommands.editor_command(r"select * from foo \e") == r"\e" + + assert iocommands.editor_command(r" \e ") == r"\e" + assert iocommands.editor_command(r"select * from foo \e ") == r"\e" + + +def test_edit_view_command_detected(): + assert iocommands.editor_command(r"\ev myview") == r"\ev" + + +def test_subst_favorite_query_args(): + template_query = "select * from foo where bar = $2 and zoo = '$1'" + subst_query, error = iocommands.subst_favorite_query_args( + template_query, ("postgres", "42") + ) + assert error is None + assert subst_query == "select * from foo where bar = 42 and zoo = 'postgres'" + + +def test_subst_favorite_query_args_bad_arg_positional(): + template_query = "select * from foo where bar = $1" + subst_query, error = iocommands.subst_favorite_query_args( + template_query, ("1337", "42") + ) + assert subst_query is None + assert error.startswith("query does not have substitution parameter $2") + + +@pytest.mark.parametrize( + "named_query,query_args", + [ + ( + "select * from foo where bar = $2 and zoo = '$1'", + ("42",), + ), + ( + "select * from foo where bar IN ($@)", + tuple(), + ), + ( + "select * from foo where (id = $1 or id = $2) AND bar IN ($@)", + ("1337", "42"), + ), + ( + "select * from foo where (id = $1 or id = $3) AND bar IN ($@)", + ("1337", "postgres", "42"), + ), + ], + ids=[ + "missing positional argument", + "missing aggregation arguments", + "missing aggregation arguments with positional", + "missing positional argument after aggregation", + ], +) +def test_subst_favorite_query_args_missing_arg(named_query, query_args): + subst_query, error = iocommands.subst_favorite_query_args(named_query, query_args) + assert subst_query is None + assert error.startswith("missing substitution for ") + + +@pytest.mark.parametrize( + "template_query,query_args,query", + [ + ( + "select * from foo where bar IN ($*)", + ("42", "1337"), + "select * from foo where bar IN (42, 1337)", + ), + ( + "select * from foo where bar IN ($@)", + ("Alice", "Bob", "Charlie"), + "select * from foo where bar IN ('Alice', 'Bob', 'Charlie')", + ), + ( + "select * from foo where bar IN ($@) and (id = $1 or id = $2)", + ("42", "1337", "Alice", "Bob", "Charlie"), + "select * from foo where bar IN ('Alice', 'Bob', 'Charlie') and (id = 42 or id = 1337)", + ), + ], + ids=["raw aggregation", "string aggregation", "positional and aggregation"], +) +def test_subst_favorite_query_args_aggregation(template_query, query_args, query): + subst_query, error = iocommands.subst_favorite_query_args( + template_query, query_args + ) + assert error is None + assert subst_query == query diff --git a/tests/test_named_queries.py b/tests/test_named_queries.py new file mode 100644 index 0000000..720974b --- /dev/null +++ b/tests/test_named_queries.py @@ -0,0 +1,46 @@ +"""Tests for named queries.""" + +import pytest +import configparser +import tempfile + +from pgspecial.namedqueries import NamedQueries +from pgspecial.main import PGSpecial +from configobj import ConfigObj + + +@pytest.fixture(scope="module") +def named_query(): + with tempfile.NamedTemporaryFile() as f: + NamedQueries.instance = NamedQueries.from_config(ConfigObj(f)) + yield + NamedQueries.instance = None + + +def test_save_named_queries(named_query): + PGSpecial().execute(None, "\\ns test select * from foo") + expected = {"test": "select * from foo"} + assert NamedQueries.instance.list() == expected + + +def test_delete_named_queries(named_query): + PGSpecial().execute(None, "\\ns test_foo select * from foo") + assert "test_foo" in NamedQueries.instance.list() + + PGSpecial().execute(None, "\\nd test_foo") + assert "test_foo" not in NamedQueries.instance.list() + + +def test_print_named_queries(named_query): + PGSpecial().execute(None, "\\ns test_name select * from bar") + assert "test_name" in NamedQueries.instance.list() + + result = PGSpecial().execute(None, "\\np test_n.*") + assert result == [("", [("test_name", "select * from bar")], ["Name", "Query"], "")] + + result = PGSpecial().execute(None, "\\np") + assert result[0][:3] == ( + None, + None, + None, + ) diff --git a/tests/test_specials.py b/tests/test_specials.py new file mode 100755 index 0000000..08319f4 --- /dev/null +++ b/tests/test_specials.py @@ -0,0 +1,1143 @@ +#!/usr/bin/python +# -*- coding: utf-8 -*- + +import pytest +from dbutils import dbtest, POSTGRES_USER, SERVER_VERSION, foreign_db_environ, fdw_test +import itertools +import locale + +objects_listing_headers = ["Schema", "Name", "Type", "Owner", "Size", "Description"] + +# note: technically, this is the database encoding, not the client +# locale but for the purpose of testing we can assume the server +# and the client are using the same locale +# note 2: locale.getlocale() does not return the raw values, +# in particular it transforms C into en_US, so we use .setlocale() +# instead as that matches the C library function +LC_COLLATE = locale.setlocale(locale.LC_COLLATE, None) +LC_CTYPE = locale.setlocale(locale.LC_CTYPE, None) + + +@dbtest +def test_slash_l(executor): + results = executor(r"\l") + row = ("_test_db", "postgres", "UTF8", LC_COLLATE, LC_CTYPE, None) + headers = ["Name", "Owner", "Encoding", "Collate", "Ctype", "Access privileges"] + assert row in results[1] + assert headers == results[2] + + +@dbtest +def test_slash_l_pattern(executor): + results = executor(r"\l _test*") + row = [("_test_db", "postgres", "UTF8", LC_COLLATE, LC_CTYPE, None)] + headers = ["Name", "Owner", "Encoding", "Collate", "Ctype", "Access privileges"] + assert row == results[1] + assert headers == results[2] + + +@dbtest +def test_slash_l_verbose(executor): + results = executor(r"\l+") + headers = [ + "Name", + "Owner", + "Encoding", + "Collate", + "Ctype", + "Access privileges", + "Size", + "Tablespace", + "Description", + ] + assert headers == results[2] + + +@dbtest +def test_slash_du(executor): + results = executor(r"\du") + row = ("postgres", True, True, True, True, True, -1, None, [], True) + headers = [ + "rolname", + "rolsuper", + "rolinherit", + "rolcreaterole", + "rolcreatedb", + "rolcanlogin", + "rolconnlimit", + "rolvaliduntil", + "memberof", + "rolreplication", + ] + assert headers == results[2] + assert row in results[1] + + +@dbtest +def test_slash_du_pattern(executor): + results = executor(r"\du post*") + row = [("postgres", True, True, True, True, True, -1, None, [], True)] + headers = [ + "rolname", + "rolsuper", + "rolinherit", + "rolcreaterole", + "rolcreatedb", + "rolcanlogin", + "rolconnlimit", + "rolvaliduntil", + "memberof", + "rolreplication", + ] + assert headers == results[2] + assert row == results[1] + + +@dbtest +def test_slash_du_verbose(executor): + results = executor(r"\du+") + row = ("postgres", True, True, True, True, True, -1, None, [], None, True) + headers = [ + "rolname", + "rolsuper", + "rolinherit", + "rolcreaterole", + "rolcreatedb", + "rolcanlogin", + "rolconnlimit", + "rolvaliduntil", + "memberof", + "description", + "rolreplication", + ] + assert headers == results[2] + assert row in results[1] + + +@dbtest +def test_slash_d(executor): + results = executor(r"\d") + title = None + rows = [ + ("public", "Inh1", "table", POSTGRES_USER), + ("public", "inh2", "table", POSTGRES_USER), + ("public", "mvw1", "materialized view", POSTGRES_USER), + ("public", "tbl1", "table", POSTGRES_USER), + ("public", "tbl2", "table", POSTGRES_USER), + ("public", "tbl2_id2_seq", "sequence", POSTGRES_USER), + ("public", "tbl3", "table", POSTGRES_USER), + ("public", "vw1", "view", POSTGRES_USER), + ] + headers = objects_listing_headers[:-2] + status = "SELECT 8" + expected = [title, rows, headers, status] + + assert results == expected + + +@dbtest +def test_slash_d_verbose(executor): + results = executor(r"\d+") + title = None + rows = [ + ("public", "Inh1", "table", POSTGRES_USER, "8192 bytes", None), + ("public", "inh2", "table", POSTGRES_USER, "8192 bytes", None), + ("public", "mvw1", "materialized view", POSTGRES_USER, "8192 bytes", None), + ("public", "tbl1", "table", POSTGRES_USER, "8192 bytes", None), + ("public", "tbl2", "table", POSTGRES_USER, "8192 bytes", None), + ("public", "tbl2_id2_seq", "sequence", POSTGRES_USER, "8192 bytes", None), + ("public", "tbl3", "table", POSTGRES_USER, "0 bytes", None), + ("public", "vw1", "view", POSTGRES_USER, "0 bytes", None), + ] + headers = objects_listing_headers + status = "SELECT 8" + expected = [title, rows, headers, status] + + assert results == expected + + +@dbtest +def test_slash_d_table_1(executor): + results = executor(r"\d tbl1") + title = None + rows = [ + ["id1", "integer", " not null"], + ["txt1", "text", " not null"], + ] + headers = ["Column", "Type", "Modifiers"] + status = ( + 'Indexes:\n "id_text" PRIMARY KEY, btree (id1, txt1)\n' + "Number of child tables: 2 (Use \\d+ to list them.)\n" + ) + expected = [title, rows, headers, status] + assert results == expected + + +@dbtest +def test_slash_d_table_2(executor): + results = executor(r"\d tbl2") + title = None + rows = [ + ["id2", "integer", " not null default nextval('tbl2_id2_seq'::regclass)"], + ["txt2", "text", ""], + ] + headers = ["Column", "Type", "Modifiers"] + status = "Number of child tables: 1 (Use \\d+ to list them.)\n" + expected = [title, rows, headers, status] + assert results == expected + + +@dbtest +def test_slash_d_test_generated_default(executor): + results = executor(r"\d schema3.test_generated_default") + headers = ["Column", "Type", "Modifiers"] + status = 'Indexes:\n "test_generated_default_pkey" PRIMARY KEY, btree (id)\n' + rows = [ + ["id", "integer", " not null generated by default as identity"], + ["some_stuff", "text", ""], + ] + assert rows == results[1] + assert headers == results[2] + assert status == results[3] + + +@dbtest +def test_slash_d_table_verbose_1(executor): + title = None + headers = ["Column", "Type", "Modifiers", "Storage", "Stats target", "Description"] + + results = executor(r"\d+ tbl1") + rows = [ + ["id1", "integer", " not null", "plain", None, None], + ["txt1", "text", " not null", "extended", None, None], + ] + status = ( + 'Indexes:\n "id_text" PRIMARY KEY, btree (id1, txt1)\n' + 'Child tables: "Inh1",\n' + " inh2\n" + "Has OIDs: no\n" + ) + expected = [title, rows, headers, status] + assert results == expected + + results = executor(r'\d+ "Inh1"') + rows = [ + ["id1", "integer", " not null", "plain", None, None], + ["txt1", "text", " not null", "extended", None, None], + ["value1", "integer", "", "plain", None, None], + ] + status = "Inherits: tbl1\n" "Has OIDs: no\n" + expected = [title, rows, headers, status] + assert results == expected + + +@dbtest +def test_slash_d_table_verbose_2(executor): + title = None + headers = ["Column", "Type", "Modifiers", "Storage", "Stats target", "Description"] + + results = executor(r"\d+ tbl2") + rows = [ + [ + "id2", + "integer", + " not null default nextval('tbl2_id2_seq'::regclass)", + "plain", + None, + None, + ], + ["txt2", "text", "", "extended", None, None], + ] + status = "Child tables: inh2\n" "Has OIDs: no\n" + expected = [title, rows, headers, status] + assert results == expected + + results = executor(r"\d+ inh2") + rows = [ + ["id1", "integer", " not null", "plain", None, None], + ["txt1", "text", " not null", "extended", None, None], + [ + "id2", + "integer", + " not null default nextval('tbl2_id2_seq'::regclass)", + "plain", + None, + None, + ], + ["txt2", "text", "", "extended", None, None], + ["value2", "integer", "", "plain", None, None], + ] + status = "Inherits: tbl1,\n" " tbl2\n" "Has OIDs: no\n" + expected = [title, rows, headers, status] + assert results == expected + + +@dbtest +def test_slash_d_view_verbose(executor): + title = None + headers = ["Column", "Type", "Modifiers", "Storage", "Description"] + + results = executor(r"\d+ vw1") + rows = [ + ["id1", "integer", "", "plain", None], + ["txt1", "text", "", "extended", None], + ] + status = "View definition:\n SELECT tbl1.id1,\n tbl1.txt1\n FROM tbl1; \n" + + expected = [title, rows, headers, status] + assert results == expected + + +@dbtest +def test_slash_d_table_with_exclusion(executor): + results = executor(r"\d tbl3") + title = None + rows = [["c3", "circle", ""]] + headers = ["Column", "Type", "Modifiers"] + status = 'Indexes:\n "tbl3_c3_excl" EXCLUDE USING gist (c3 WITH &&)\n' + expected = [title, rows, headers, status] + assert results == expected + + +@dbtest +def test_slash_d_table_2_in_schema(executor): + results = executor(r"\d schema2.tbl2") + title = None + rows = [ + [ + "id2", + "integer", + " not null default nextval('schema2.tbl2_id2_seq'::regclass)", + ], + ["txt2", "text", ""], + ] + headers = ["Column", "Type", "Modifiers"] + status = "" + expected = [title, rows, headers, status] + assert results == expected + + +@dbtest +def test_slash_dn(executor): + """List all schemas.""" + results = executor(r"\dn") + title = None + if SERVER_VERSION >= 150001: + rows = [ + ("public", "pg_database_owner"), + ("schema1", POSTGRES_USER), + ("schema2", POSTGRES_USER), + ("schema3", POSTGRES_USER), + ] + else: + rows = [ + ("public", POSTGRES_USER), + ("schema1", POSTGRES_USER), + ("schema2", POSTGRES_USER), + ("schema3", POSTGRES_USER), + ] + + headers = ["Name", "Owner"] + status = "SELECT %s" % len(rows) + expected = [title, rows, headers, status] + assert results == expected + + +@dbtest +def test_slash_dp(executor): + """List all schemas.""" + results = executor(r"\dp") + title = None + rows = [ + ("public", "Inh1", "table", None, "", ""), + ("public", "inh2", "table", None, "", ""), + ("public", "mvw1", "materialized view", None, "", ""), + ("public", "tbl1", "table", None, "", ""), + ("public", "tbl2", "table", None, "", ""), + ("public", "tbl2_id2_seq", "sequence", None, "", ""), + ("public", "tbl3", "table", None, "", ""), + ("public", "vw1", "view", None, "", ""), + ] + + headers = [ + "Schema", + "Name", + "Type", + "Access privileges", + "Column privileges", + "Policies", + ] + status = "SELECT %s" % len(rows) + expected = [title, rows, headers, status] + assert results == expected + + +@dbtest +def test_slash_dp_pattern_table(executor): + """List all schemas.""" + results = executor(r"\dp i*2") + title = None + rows = [("public", "inh2", "table", None, "", "")] + headers = [ + "Schema", + "Name", + "Type", + "Access privileges", + "Column privileges", + "Policies", + ] + status = "SELECT %s" % len(rows) + expected = [title, rows, headers, status] + assert results == expected + + +@dbtest +def test_slash_dp_pattern_schema(executor): + """List all schemas.""" + results = executor(r"\dp schema2.*") + title = None + rows = [ + ("schema2", "tbl2", "table", None, "", ""), + ("schema2", "tbl2_id2_seq", "sequence", None, "", ""), + ] + headers = [ + "Schema", + "Name", + "Type", + "Access privileges", + "Column privileges", + "Policies", + ] + status = "SELECT %s" % len(rows) + expected = [title, rows, headers, status] + assert results == expected + + +@dbtest +def test_slash_dp_pattern_alias(executor): + """List all schemas.""" + results = executor(r"\z i*2") + title = None + rows = [("public", "inh2", "table", None, "", "")] + headers = [ + "Schema", + "Name", + "Type", + "Access privileges", + "Column privileges", + "Policies", + ] + status = "SELECT %s" % len(rows) + expected = [title, rows, headers, status] + assert results == expected + + +@dbtest +def test_slash_ddp(executor): + """List all schemas.""" + results = executor(r"\ddp") + title = None + rows = [ + ("postgres", "schema1", "table", "test_role=r/postgres"), + ("postgres", "schema2", "table", "test_role=arwdDxt/postgres"), + ] + + headers = [ + "Owner", + "Schema", + "Type", + "Access privileges", + ] + status = "SELECT %s" % len(rows) + expected = [title, rows, headers, status] + assert results == expected + + +@dbtest +def test_slash_ddp_pattern(executor): + """List all schemas.""" + results = executor(r"\ddp schema2") + title = None + rows = [("postgres", "schema2", "table", "test_role=arwdDxt/postgres")] + headers = [ + "Owner", + "Schema", + "Type", + "Access privileges", + ] + status = "SELECT %s" % len(rows) + expected = [title, rows, headers, status] + assert results == expected + + +@dbtest +def test_slash_dt(executor): + """List all tables in public schema.""" + results = executor(r"\dt") + title = None + rows = [ + ("public", "Inh1", "table", POSTGRES_USER), + ("public", "inh2", "table", POSTGRES_USER), + ("public", "tbl1", "table", POSTGRES_USER), + ("public", "tbl2", "table", POSTGRES_USER), + ("public", "tbl3", "table", POSTGRES_USER), + ] + headers = objects_listing_headers[:-2] + status = "SELECT %s" % len(rows) + expected = [title, rows, headers, status] + assert results == expected + + +@dbtest +def test_slash_dt_verbose(executor): + """List all tables in public schema in verbose mode.""" + results = executor(r"\dt+") + title = None + rows = [ + ("public", "Inh1", "table", POSTGRES_USER, "8192 bytes", None), + ("public", "inh2", "table", POSTGRES_USER, "8192 bytes", None), + ("public", "tbl1", "table", POSTGRES_USER, "8192 bytes", None), + ("public", "tbl2", "table", POSTGRES_USER, "8192 bytes", None), + ("public", "tbl3", "table", POSTGRES_USER, "0 bytes", None), + ] + headers = objects_listing_headers + status = "SELECT %s" % len(rows) + expected = [title, rows, headers, status] + assert results == expected + + +@dbtest +def test_slash_dv(executor): + """List all views in public schema.""" + results = executor(r"\dv") + title = None + row = [("public", "vw1", "view", POSTGRES_USER)] + headers = objects_listing_headers[:-2] + status = "SELECT 1" + expected = [title, row, headers, status] + assert results == expected + + +@dbtest +def test_slash_dv_verbose(executor): + """List all views in s1 schema in verbose mode.""" + results = executor(r"\dv+ schema1.*") + title = None + row = [("schema1", "s1_vw1", "view", POSTGRES_USER, "0 bytes", None)] + headers = objects_listing_headers + status = "SELECT 1" + expected = [title, row, headers, status] + assert results == expected + + +@dbtest +def test_slash_dm(executor): + """List all materialized views in schema1.""" + results = executor(r"\dm schema1.*") + title = None + row = [("schema1", "s1_mvw1", "materialized view", POSTGRES_USER)] + headers = objects_listing_headers[:-2] + status = "SELECT 1" + expected = [title, row, headers, status] + assert results == expected + + +@dbtest +def test_slash_dm_verbose(executor): + """List all materialized views in public schema in verbose mode.""" + results = executor(r"\dm+") + title = None + row = [("public", "mvw1", "materialized view", POSTGRES_USER, "8192 bytes", None)] + headers = objects_listing_headers + status = "SELECT 1" + expected = [title, row, headers, status] + assert results == expected + + +@dbtest +def test_slash_ds(executor): + """List all sequences in public schema.""" + results = executor(r"\ds") + title = None + row = [("public", "tbl2_id2_seq", "sequence", POSTGRES_USER)] + headers = objects_listing_headers[:-2] + status = "SELECT 1" + expected = [title, row, headers, status] + assert results == expected + + +@dbtest +def test_slash_ds_verbose(executor): + """List all sequences in public schema in verbose mode.""" + results = executor(r"\ds+") + title = None + row = [("public", "tbl2_id2_seq", "sequence", POSTGRES_USER, "8192 bytes", None)] + headers = objects_listing_headers + status = "SELECT 1" + expected = [title, row, headers, status] + assert results == expected + + +@dbtest +def test_slash_di(executor): + """List all indexes in public schema.""" + results = executor(r"\di") + title = None + row = [ + ("public", "id_text", "index", POSTGRES_USER), + ("public", "tbl3_c3_excl", "index", POSTGRES_USER), + ] + headers = objects_listing_headers[:-2] + status = "SELECT %s" % len(row) + expected = [title, row, headers, status] + assert results == expected + + +@dbtest +def test_slash_di_verbose(executor): + """List all indexes in public schema in verbose mode.""" + results = executor(r"\di+") + title = None + row = [ + ("public", "id_text", "index", POSTGRES_USER, "8192 bytes", None), + ("public", "tbl3_c3_excl", "index", POSTGRES_USER, "8192 bytes", None), + ] + headers = objects_listing_headers + status = "SELECT 2" + expected = [title, row, headers, status] + assert results == expected + + +@dbtest +def test_slash_dx(executor): + """List all extensions.""" + results = executor(r"\dx") + title = None + row = [("plpgsql", "1.0", "pg_catalog", "PL/pgSQL procedural language")] + headers = ["Name", "Version", "Schema", "Description"] + status = "SELECT 1" + expected = [title, row, headers, status] + assert results == expected + + +@dbtest +def test_slash_dx_verbose(executor): + """List all extensions in verbose mode.""" + results = executor(r"\dx+") + title = '\nObjects in extension "plpgsql"' + row = [ + ("function plpgsql_call_handler()",), + ("function plpgsql_inline_handler(internal)",), + ("function plpgsql_validator(oid)",), + ("language plpgsql",), + ] + headers = ["Object description"] + status = "SELECT %s" % len(row) + expected = [title, row, headers, status] + assert results == expected + + +@dbtest +def test_slash_dT(executor): + """List all datatypes.""" + results = executor(r"\dT") + title = None + rows = [("public", "foo", None), ("public", "gender_t", None)] + headers = ["Schema", "Name", "Description"] + status = "SELECT %s" % len(rows) + expected = [title, rows, headers, status] + assert results == expected + + +@dbtest +def test_slash_dD(executor): + title = None + headers = ["Schema", "Name", "Type", "Modifier", "Check"] + results = executor(r"\dD") + rows = [ + ( + "public", + "gender_t", + "character(1)", + "", + "CHECK (VALUE = ANY (ARRAY['F'::bpchar, 'M'::bpchar]))", + ) + ] + status = "SELECT 1" + expected = [title, rows, headers, status] + assert results == expected + + results = executor(r"\dD schema1.*") + rows = [ + ("schema1", "bigint_t", "bigint", "", ""), + ("schema1", "smallint_t", "smallint", "", ""), + ] + status = "SELECT %s" % len(rows) + expected = [title, rows, headers, status] + assert results == expected + + +@dbtest +def test_slash_dD_verbose(executor): + title = None + headers = [ + "Schema", + "Name", + "Type", + "Modifier", + "Check", + "Access privileges", + "Description", + ] + results = executor(r"\dD+") + rows = [ + ( + "public", + "gender_t", + "character(1)", + "", + "CHECK (VALUE = ANY (ARRAY['F'::bpchar, 'M'::bpchar]))", + None, + None, + ) + ] + status = "SELECT 1" + expected = [title, rows, headers, status] + assert results == expected + + results = executor(r"\dD+ schema1.bigint_t") + rows = [("schema1", "bigint_t", "bigint", "", "", None, "a really large integer")] + status = "SELECT 1" + expected = [title, rows, headers, status] + assert results == expected + + +@dbtest +def test_slash_dF(executor): + title, rows, header, status = executor(r"\dF") + assert title is None + assert header == ["Schema", "Name", "Description"] + assert ("pg_catalog", "spanish", "configuration for spanish language") in rows + + results = executor(r"\dD *ian") + assert title is None + assert header == ["Schema", "Name", "Description"] + assert ("pg_catalog", "russian", "configuration for russian language") in rows + + results = executor(r"\dD ge*") + assert title is None + assert header == ["Schema", "Name", "Description"] + assert ("pg_catalog", "german", "configuration for german language") in rows + + +@dbtest +def test_slash_dF_verbose(executor): + results = executor(r"\dF+")[1] + assert ("asciihword", "simple") in results + + results = executor(r"\dF+ *panish")[1] + assert ("asciihword", "spanish_stem") in results + + results = executor(r"\dF+ swed*")[1] + assert ("asciihword", "swedish_stem") in results + + results = executor(r"\dF+ jap") + assert results == [None, None, None, 'Did not find any results for pattern "jap".'] + + +@dbtest +def test_slash_db(executor): + """List all tablespaces.""" + title, rows, header, status = executor(r"\db") + assert title is None + assert header == ["Name", "Owner", "Location"] + assert "pg_default" in rows[0] + + +@dbtest +def test_slash_db_name(executor): + """List tablespace by name.""" + title, rows, header, status = executor(r"\db pg_default") + assert title is None + assert header == ["Name", "Owner", "Location"] + assert "pg_default" in rows[0] + assert status == "SELECT 1" + + +@dbtest +def test_slash_df(executor): + results = executor(r"\df") + title = None + rows = [("public", "func1", "integer", "", "normal")] + headers = ["Schema", "Name", "Result data type", "Argument data types", "Type"] + status = "SELECT 1" + expected = [title, rows, headers, status] + assert results == expected + + +help_rows = [ + [ + "ABORT", + "ALTER AGGREGATE", + "ALTER COLLATION", + "ALTER CONVERSION", + "ALTER DATABASE", + "ALTER DEFAULT PRIVILEGES", + ], + [ + "ALTER DOMAIN", + "ALTER EVENT TRIGGER", + "ALTER EXTENSION", + "ALTER FOREIGN DATA WRAPPER", + "ALTER FOREIGN TABLE", + "ALTER FUNCTION", + ], + [ + "ALTER GROUP", + "ALTER INDEX", + "ALTER LANGUAGE", + "ALTER LARGE OBJECT", + "ALTER MATERIALIZED VIEW", + "ALTER OPCLASS", + ], + [ + "ALTER OPERATOR", + "ALTER OPFAMILY", + "ALTER POLICY", + "ALTER ROLE", + "ALTER RULE", + "ALTER SCHEMA", + ], + [ + "ALTER SEQUENCE", + "ALTER SERVER", + "ALTER SYSTEM", + "ALTER TABLE", + "ALTER TABLESPACE", + "ALTER TRIGGER", + ], + [ + "ALTER TSCONFIG", + "ALTER TSDICTIONARY", + "ALTER TSPARSER", + "ALTER TSTEMPLATE", + "ALTER TYPE", + "ALTER USER", + ], + ["ALTER USER MAPPING", "ALTER VIEW", "ANALYZE", "BEGIN", "CHECKPOINT", "CLOSE"], + ["CLUSTER", "COMMENT", "COMMIT", "COMMIT PREPARED", "COPY", "CREATE AGGREGATE"], + [ + "CREATE CAST", + "CREATE COLLATION", + "CREATE CONVERSION", + "CREATE DATABASE", + "CREATE DOMAIN", + "CREATE EVENT TRIGGER", + ], + [ + "CREATE EXTENSION", + "CREATE FOREIGN DATA WRAPPER", + "CREATE FOREIGN TABLE", + "CREATE FUNCTION", + "CREATE GROUP", + "CREATE INDEX", + ], + [ + "CREATE LANGUAGE", + "CREATE MATERIALIZED VIEW", + "CREATE OPCLASS", + "CREATE OPERATOR", + "CREATE OPFAMILY", + "CREATE POLICY", + ], + [ + "CREATE ROLE", + "CREATE RULE", + "CREATE SCHEMA", + "CREATE SEQUENCE", + "CREATE SERVER", + "CREATE TABLE", + ], + [ + "CREATE TABLE AS", + "CREATE TABLESPACE", + "CREATE TRANSFORM", + "CREATE TRIGGER", + "CREATE TSCONFIG", + "CREATE TSDICTIONARY", + ], + [ + "CREATE TSPARSER", + "CREATE TSTEMPLATE", + "CREATE TYPE", + "CREATE USER", + "CREATE USER MAPPING", + "CREATE VIEW", + ], + ["DEALLOCATE", "DECLARE", "DELETE", "DISCARD", "DO", "DROP AGGREGATE"], + [ + "DROP CAST", + "DROP COLLATION", + "DROP CONVERSION", + "DROP DATABASE", + "DROP DOMAIN", + "DROP EVENT TRIGGER", + ], + [ + "DROP EXTENSION", + "DROP FOREIGN DATA WRAPPER", + "DROP FOREIGN TABLE", + "DROP FUNCTION", + "DROP GROUP", + "DROP INDEX", + ], + [ + "DROP LANGUAGE", + "DROP MATERIALIZED VIEW", + "DROP OPCLASS", + "DROP OPERATOR", + "DROP OPFAMILY", + "DROP OWNED", + ], + [ + "DROP POLICY", + "DROP ROLE", + "DROP RULE", + "DROP SCHEMA", + "DROP SEQUENCE", + "DROP SERVER", + ], + [ + "DROP TABLE", + "DROP TABLESPACE", + "DROP TRANSFORM", + "DROP TRIGGER", + "DROP TSCONFIG", + "DROP TSDICTIONARY", + ], + [ + "DROP TSPARSER", + "DROP TSTEMPLATE", + "DROP TYPE", + "DROP USER", + "DROP USER MAPPING", + "DROP VIEW", + ], + ["END", "EXECUTE", "EXPLAIN", "FETCH", "GRANT", "IMPORT FOREIGN SCHEMA"], + ["INSERT", "LISTEN", "LOAD", "LOCK", "MOVE", "NOTIFY"], + [ + "PGBENCH", + "PREPARE", + "PREPARE TRANSACTION", + "REASSIGN OWNED", + "REFRESH MATERIALIZED VIEW", + "REINDEX", + ], + [ + "RELEASE SAVEPOINT", + "RESET", + "REVOKE", + "ROLLBACK", + "ROLLBACK PREPARED", + "ROLLBACK TO", + ], + ["SAVEPOINT", "SECURITY LABEL", "SELECT", "SELECT INTO", "SET", "SET CONSTRAINTS"], + [ + "SET ROLE", + "SET SESSION AUTH", + "SET TRANSACTION", + "SHOW", + "START TRANSACTION", + "TRUNCATE", + ], + ["UNLISTEN", "UPDATE", "VACUUM", "VALUES"], +] + + +@dbtest +def test_slash_h(executor): + """List all commands.""" + results = executor(r"\h") + expected = [None, help_rows, [], None] + assert results == expected + + +@dbtest +def test_slash_h_command(executor): + """Check help is returned for all commands""" + for command in itertools.chain(*help_rows): + results = executor(r"\h %s" % command) + assert results[3].startswith("Description\n") + assert "Syntax" in results[3] + + +@dbtest +def test_slash_h_alias(executor): + r"""\? is properly aliased to \h""" + h_results = executor(r"\h SELECT") + results = executor(r"\? SELECT") + assert results[3] == h_results[3] + + +@dbtest +def test_slash_copy_to_tsv(executor, tmpdir): + filepath = tmpdir.join("pycons.tsv") + executor( + r"\copy (SELECT 'Montréal', 'Portland', 'Cleveland') TO '{0}' ".format(filepath) + ) + infile = filepath.open(encoding="utf-8") + contents = infile.read() + assert len(contents.splitlines()) == 1 + assert "Montréal" in contents + + +@dbtest +def test_slash_copy_throws_error_without_TO_or_FROM(executor): + with pytest.raises(Exception) as exc_info: + executor("\copy (SELECT 'Montréal', 'Portland', 'Cleveland') INTO stdout ") + assert ( + str(exc_info.value) + == "Missing keyword in \\copy command. Either TO or FROM is required." + ) + + +@dbtest +def test_slash_copy_to_stdout(executor, capsys): + executor(r"\copy (SELECT 'Montréal', 'Portland', 'Cleveland') TO stdout") + (out, err) = capsys.readouterr() + assert out == "Montréal\tPortland\tCleveland\n" + + +@dbtest +def test_slash_copy_to_csv(executor, tmpdir): + filepath = tmpdir.join("pycons.tsv") + executor( + r"\copy (SELECT 'Montréal', 'Portland', 'Cleveland') TO '{0}' WITH csv".format( + filepath + ) + ) + infile = filepath.open(encoding="utf-8") + contents = infile.read() + assert len(contents.splitlines()) == 1 + assert "Montréal" in contents + assert "," in contents + + +@dbtest +def test_slash_copy_from_csv(executor, connection, tmpdir): + filepath = tmpdir.join("tbl1.csv") + executor(r"\copy (SELECT 22, 'elephant') TO '{0}' WITH csv".format(filepath)) + executor(r"\copy tbl1 FROM '{0}' WITH csv".format(filepath)) + cur = connection.cursor() + cur.execute("SELECT * FROM tbl1 WHERE id1 = 22") + row = cur.fetchone() + assert row[1] == "elephant" + + +@dbtest +def test_slash_sf(executor): + results = executor(r"\sf func1") + title = None + rows = [ + ( + "CREATE OR REPLACE FUNCTION public.func1()\n" + " RETURNS integer\n" + " LANGUAGE sql\n" + "AS $function$select 1$function$\n", + ), + ] + headers = ["Source"] + status = None + expected = [title, rows, headers, status] + assert results == expected + + +@dbtest +def test_slash_sf_unknown(executor): + try: + executor(r"\sf non_existing") + except Exception as e: + assert "non_existing" in str(e) + else: + assert False, "Expected an exception" + + +@dbtest +def test_slash_sf_parens(executor): + results = executor(r"\sf func1()") + title = None + rows = [ + ( + "CREATE OR REPLACE FUNCTION public.func1()\n" + " RETURNS integer\n" + " LANGUAGE sql\n" + "AS $function$select 1$function$\n", + ), + ] + headers = ["Source"] + status = None + expected = [title, rows, headers, status] + assert results == expected + + +@dbtest +def test_slash_sf_verbose(executor): + results = executor(r"\sf+ schema1.s1_func1") + title = None + rows = [ + ( + " CREATE OR REPLACE FUNCTION schema1.s1_func1()\n" + " RETURNS integer\n" + " LANGUAGE sql\n" + "1 AS $function$select 2$function$\n", + ), + ] + headers = ["Source"] + status = None + expected = [title, rows, headers, status] + assert results == expected + + +@fdw_test +def test_slash_dE(executor): + with foreign_db_environ(): + results = executor(r"\dE") + title = None + rows = [("public", "foreign_foo", "foreign table", "postgres")] + headers = ["Schema", "Name", "Type", "Owner"] + status = "SELECT 1" + expected = [title, rows, headers, status] + assert results == expected + + +@fdw_test +def test_slash_dE_with_pattern(executor): + with foreign_db_environ(): + results = executor(r"\dE foreign_foo") + title = None + rows = [("public", "foreign_foo", "foreign table", "postgres")] + headers = ["Schema", "Name", "Type", "Owner"] + status = "SELECT 1" + expected = [title, rows, headers, status] + assert results == expected + + results = executor(r"\dE *_foo") + assert results == expected + + results = executor(r"\dE no_such_table") + rows = [] + status = "SELECT 0" + expected = [title, rows, headers, status] + assert results == expected + + +@fdw_test +def test_slash_dE_verbose(executor): + with foreign_db_environ(): + results = executor(r"\dE+") + title = None + rows = [("public", "foreign_foo", "foreign table", "postgres", "0 bytes", None)] + headers = ["Schema", "Name", "Type", "Owner", "Size", "Description"] + status = "SELECT 1" + expected = [title, rows, headers, status] + assert results == expected @@ -0,0 +1,6 @@ +[tox] +envlist = py37, py38, py39, py310, py311 +[testenv] +deps = pytest + configobj +commands = py.test {posargs} |