summaryrefslogtreecommitdiffstats
path: root/pgspecial/dbcommands.py
diff options
context:
space:
mode:
Diffstat (limited to 'pgspecial/dbcommands.py')
-rw-r--r--pgspecial/dbcommands.py1999
1 files changed, 1999 insertions, 0 deletions
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("_"))
generated by cgit v1.2.3 (git 2.39.1) at 2024-12-12 16:14:24 +0000