diff options
Diffstat (limited to '')
| -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 |
12 files changed, 3571 insertions, 0 deletions
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 |