1from __future__ import unicode_literals 2import logging 3import shlex 4import subprocess 5from collections import namedtuple 6 7from .main import special_command, RAW_QUERY 8 9TableInfo = namedtuple( 10 "TableInfo", 11 [ 12 "checks", 13 "relkind", 14 "hasindex", 15 "hasrules", 16 "hastriggers", 17 "hasoids", 18 "tablespace", 19 "reloptions", 20 "reloftype", 21 "relpersistence", 22 "relispartition", 23 ], 24) 25 26log = logging.getLogger(__name__) 27 28 29@special_command("\\l", "\\l[+] [pattern]", "List databases.", aliases=("\\list",)) 30def list_databases(cur, pattern, verbose): 31 query = '''SELECT d.datname as "Name", 32 pg_catalog.pg_get_userbyid(d.datdba) as "Owner", 33 pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding", 34 d.datcollate as "Collate", 35 d.datctype as "Ctype", 36 pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"''' 37 if verbose: 38 query += ''', 39 CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT') 40 THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname)) 41 ELSE 'No Access' 42 END as "Size", 43 t.spcname as "Tablespace", 44 pg_catalog.shobj_description(d.oid, 'pg_database') as "Description"''' 45 query += """ 46 FROM pg_catalog.pg_database d 47 """ 48 if verbose: 49 query += """ 50 JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid 51 """ 52 params = [] 53 if pattern: 54 query += """ 55 WHERE d.datname ~ %s 56 """ 57 _, schema = sql_name_pattern(pattern) 58 params.append(schema) 59 query = cur.mogrify(query + " ORDER BY 1", params) 60 cur.execute(query) 61 if cur.description: 62 headers = [x[0] for x in cur.description] 63 return [(None, cur, headers, cur.statusmessage)] 64 else: 65 return [(None, None, None, cur.statusmessage)] 66 67 68@special_command("\\du", "\\du[+] [pattern]", "List roles.") 69def list_roles(cur, pattern, verbose): 70 """ 71 Returns (title, rows, headers, status) 72 """ 73 74 if cur.connection.server_version > 90000: 75 sql = """ 76 SELECT r.rolname, 77 r.rolsuper, 78 r.rolinherit, 79 r.rolcreaterole, 80 r.rolcreatedb, 81 r.rolcanlogin, 82 r.rolconnlimit, 83 r.rolvaliduntil, 84 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, 85 """ 86 if verbose: 87 sql += """ 88 pg_catalog.shobj_description(r.oid, 'pg_authid') AS description, 89 """ 90 sql += """ 91 r.rolreplication 92 FROM pg_catalog.pg_roles r 93 """ 94 else: 95 sql = """ 96 SELECT u.usename AS rolname, 97 u.usesuper AS rolsuper, 98 true AS rolinherit, 99 false AS rolcreaterole, 100 u.usecreatedb AS rolcreatedb, 101 true AS rolcanlogin, 102 -1 AS rolconnlimit, 103 u.valuntil as rolvaliduntil, 104 ARRAY(SELECT g.groname FROM pg_catalog.pg_group g WHERE u.usesysid = ANY(g.grolist)) as memberof 105 FROM pg_catalog.pg_user u 106 """ 107 108 params = [] 109 if pattern: 110 _, schema = sql_name_pattern(pattern) 111 sql += "WHERE r.rolname ~ %s" 112 params.append(schema) 113 sql = cur.mogrify(sql + " ORDER BY 1", params) 114 115 log.debug(sql) 116 cur.execute(sql) 117 if cur.description: 118 headers = [x[0] for x in cur.description] 119 return [(None, cur, headers, cur.statusmessage)] 120 121 122@special_command("\\dp", "\\dp [pattern]", "List roles.", aliases=("\\z",)) 123def list_privileges(cur, pattern, verbose): 124 """Returns (title, rows, headers, status)""" 125 sql = """ 126 SELECT n.nspname as "Schema", 127 c.relname as "Name", 128 CASE c.relkind WHEN 'r' THEN 'table' 129 WHEN 'v' THEN 'view' 130 WHEN 'm' THEN 'materialized view' 131 WHEN 'S' THEN 'sequence' 132 WHEN 'f' THEN 'foreign table' 133 WHEN 'p' THEN 'partitioned table' END as "Type", 134 pg_catalog.array_to_string(c.relacl, E'\n') AS "Access privileges", 135 pg_catalog.array_to_string(ARRAY( 136 SELECT attname || E':\n ' || pg_catalog.array_to_string(attacl, E'\n ') 137 FROM pg_catalog.pg_attribute a 138 WHERE attrelid = c.oid AND NOT attisdropped AND attacl IS NOT NULL 139 ), E'\n') AS "Column privileges", 140 pg_catalog.array_to_string(ARRAY( 141 SELECT polname 142 || CASE WHEN NOT polpermissive THEN 143 E' (RESTRICTIVE)' 144 ELSE '' END 145 || CASE WHEN polcmd != '*' THEN 146 E' (' || polcmd || E'):' 147 ELSE E':' 148 END 149 || CASE WHEN polqual IS NOT NULL THEN 150 E'\n (u): ' || pg_catalog.pg_get_expr(polqual, polrelid) 151 ELSE E'' 152 END 153 || CASE WHEN polwithcheck IS NOT NULL THEN 154 E'\n (c): ' || pg_catalog.pg_get_expr(polwithcheck, polrelid) 155 ELSE E'' 156 END || CASE WHEN polroles <> '{0}' THEN 157 E'\n to: ' || pg_catalog.array_to_string( 158 ARRAY( 159 SELECT rolname 160 FROM pg_catalog.pg_roles 161 WHERE oid = ANY (polroles) 162 ORDER BY 1 163 ), E', ') 164 ELSE E'' 165 END 166 FROM pg_catalog.pg_policy pol 167 WHERE polrelid = c.oid), E'\n') 168 AS "Policies" 169 FROM pg_catalog.pg_class c 170 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 171 """ 172 173 where_clause = """ 174 WHERE c.relkind IN ('r','v','m','S','f','p') 175 {pattern} 176 AND n.nspname !~ '^pg_' AND pg_catalog.pg_table_is_visible(c.oid) 177 """ 178 179 params = [] 180 if pattern: 181 _, schema = sql_name_pattern(pattern) 182 where_clause = where_clause.format( 183 pattern=" AND c.relname OPERATOR(pg_catalog.~) %s COLLATE pg_catalog.default " 184 ) 185 params.append(schema) 186 else: 187 where_clause = where_clause.format(pattern="") 188 sql = cur.mogrify(sql + where_clause + " ORDER BY 1, 2", params) 189 190 log.debug(sql) 191 cur.execute(sql) 192 if cur.description: 193 headers = [x[0] for x in cur.description] 194 return [(None, cur, headers, cur.statusmessage)] 195 196 197@special_command("\\ddp", "\\ddp [pattern]", "Lists default access privilege settings.") 198def list_default_privileges(cur, pattern, verbose): 199 """Returns (title, rows, headers, status)""" 200 sql = """ 201 SELECT pg_catalog.pg_get_userbyid(d.defaclrole) AS "Owner", 202 n.nspname AS "Schema", 203 CASE d.defaclobjtype WHEN 'r' THEN 'table' 204 WHEN 'S' THEN 'sequence' 205 WHEN 'f' THEN 'function' 206 WHEN 'T' THEN 'type' 207 WHEN 'n' THEN 'schema' END AS "Type", 208 pg_catalog.array_to_string(d.defaclacl, E'\n') AS "Access privileges" 209 FROM pg_catalog.pg_default_acl d 210 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = d.defaclnamespace 211 """ 212 213 where_clause = """ 214 WHERE (n.nspname OPERATOR(pg_catalog.~) '^({pattern})$' COLLATE pg_catalog.default 215 OR pg_catalog.pg_get_userbyid(d.defaclrole) OPERATOR(pg_catalog.~) '^({pattern})$' COLLATE pg_catalog.default) 216 """ 217 218 params = [] 219 if pattern: 220 _, schema = sql_name_pattern(pattern) 221 where_clause = where_clause.format(pattern=pattern) 222 sql += where_clause 223 params.append(schema) 224 225 sql = cur.mogrify(sql + " ORDER BY 1, 2, 3", params) 226 log.debug(sql) 227 cur.execute(sql) 228 if cur.description: 229 headers = [x[0] for x in cur.description] 230 return [(None, cur, headers, cur.statusmessage)] 231 232 233@special_command("\\db", "\\db[+] [pattern]", "List tablespaces.") 234def list_tablespaces(cur, pattern, **_): 235 """ 236 Returns (title, rows, headers, status) 237 """ 238 239 cur.execute( 240 "SELECT EXISTS(SELECT * FROM pg_proc WHERE proname = 'pg_tablespace_location')" 241 ) 242 (is_location,) = cur.fetchone() 243 244 sql = """SELECT n.spcname AS "Name", 245 pg_catalog.pg_get_userbyid(n.spcowner) AS "Owner",""" 246 247 sql += ( 248 " pg_catalog.pg_tablespace_location(n.oid)" 249 if is_location 250 else " 'Not supported'" 251 ) 252 sql += """ AS "Location" 253 FROM pg_catalog.pg_tablespace n""" 254 255 params = [] 256 if pattern: 257 _, tbsp = sql_name_pattern(pattern) 258 sql += " WHERE n.spcname ~ %s" 259 params.append(tbsp) 260 261 sql = cur.mogrify(sql + " ORDER BY 1", params) 262 log.debug(sql) 263 cur.execute(sql) 264 265 headers = [x[0] for x in cur.description] if cur.description else None 266 return [(None, cur, headers, cur.statusmessage)] 267 268 269@special_command("\\dn", "\\dn[+] [pattern]", "List schemas.") 270def list_schemas(cur, pattern, verbose): 271 """ 272 Returns (title, rows, headers, status) 273 """ 274 275 sql = ( 276 '''SELECT n.nspname AS "Name", 277 pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"''' 278 + ( 279 ''', 280 pg_catalog.array_to_string(n.nspacl, E'\\n') AS "Access privileges", 281 pg_catalog.obj_description(n.oid, 'pg_namespace') AS "Description"''' 282 if verbose 283 else "" 284 ) 285 + """ 286 FROM pg_catalog.pg_namespace n WHERE n.nspname """ 287 ) 288 289 params = [] 290 if pattern: 291 _, schema = sql_name_pattern(pattern) 292 sql += "~ %s" 293 params.append(schema) 294 else: 295 sql += "!~ '^pg_' AND n.nspname <> 'information_schema'" 296 sql = cur.mogrify(sql + " ORDER BY 1", params) 297 298 log.debug(sql) 299 cur.execute(sql) 300 if cur.description: 301 headers = [x[0] for x in cur.description] 302 return [(None, cur, headers, cur.statusmessage)] 303 304 305# https://github.com/postgres/postgres/blob/master/src/bin/psql/describe.c#L5471-L5638 306@special_command("\\dx", "\\dx[+] [pattern]", "List extensions.") 307def list_extensions(cur, pattern, verbose): 308 def _find_extensions(cur, pattern): 309 sql = """ 310 SELECT e.extname, e.oid FROM pg_catalog.pg_extension e 311 """ 312 313 params = [] 314 if pattern: 315 _, schema = sql_name_pattern(pattern) 316 sql += "WHERE e.extname ~ %s" 317 params.append(schema) 318 319 sql = cur.mogrify(sql + "ORDER BY 1, 2;", params) 320 log.debug(sql) 321 cur.execute(sql) 322 return cur.fetchall() 323 324 def _describe_extension(cur, oid): 325 sql = """ 326 SELECT pg_catalog.pg_describe_object(classid, objid, 0) 327 AS "Object Description" 328 FROM pg_catalog.pg_depend 329 WHERE refclassid = 'pg_catalog.pg_extension'::pg_catalog.regclass 330 AND refobjid = %s 331 AND deptype = 'e' 332 ORDER BY 1""" 333 sql = cur.mogrify(sql, [oid]) 334 log.debug(sql) 335 cur.execute(sql) 336 337 headers = [x[0] for x in cur.description] 338 return cur, headers, cur.statusmessage 339 340 if cur.connection.server_version < 90100: 341 not_supported = "Server versions below 9.1 do not support extensions." 342 cur, headers = [], [] 343 yield None, cur, None, not_supported 344 return 345 346 if verbose: 347 extensions = _find_extensions(cur, pattern) 348 349 if extensions: 350 for ext_name, oid in extensions: 351 title = '\nObjects in extension "%s"' % ext_name 352 cur, headers, status = _describe_extension(cur, oid) 353 yield title, cur, headers, status 354 else: 355 yield None, None, None, 'Did not find any extension named "{}".'.format( 356 pattern 357 ) 358 return 359 360 sql = """ 361 SELECT e.extname AS "Name", 362 e.extversion AS "Version", 363 n.nspname AS "Schema", 364 c.description AS "Description" 365 FROM pg_catalog.pg_extension e 366 LEFT JOIN pg_catalog.pg_namespace n 367 ON n.oid = e.extnamespace 368 LEFT JOIN pg_catalog.pg_description c 369 ON c.objoid = e.oid 370 AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass 371 """ 372 373 params = [] 374 if pattern: 375 _, schema = sql_name_pattern(pattern) 376 sql += "WHERE e.extname ~ %s" 377 params.append(schema) 378 379 sql = cur.mogrify(sql + "ORDER BY 1, 2", params) 380 log.debug(sql) 381 cur.execute(sql) 382 if cur.description: 383 headers = [x[0] for x in cur.description] 384 yield None, cur, headers, cur.statusmessage 385 386 387def list_objects(cur, pattern, verbose, relkinds): 388 """ 389 Returns (title, rows, header, status) 390 391 This method is used by list_tables, list_views, list_materialized views 392 and list_indexes 393 394 relkinds is a list of strings to filter pg_class.relkind 395 396 """ 397 schema_pattern, table_pattern = sql_name_pattern(pattern) 398 399 if verbose: 400 verbose_columns = """ 401 ,pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size", 402 pg_catalog.obj_description(c.oid, 'pg_class') as "Description" """ 403 else: 404 verbose_columns = "" 405 406 sql = ( 407 """SELECT n.nspname as "Schema", 408 c.relname as "Name", 409 CASE c.relkind 410 WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' 411 WHEN 'p' THEN 'partitioned table' 412 WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' 413 WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' 414 WHEN 'f' THEN 'foreign table' END 415 as "Type", 416 pg_catalog.pg_get_userbyid(c.relowner) as "Owner" 417 """ 418 + verbose_columns 419 + """ 420 FROM pg_catalog.pg_class c 421 LEFT JOIN pg_catalog.pg_namespace n 422 ON n.oid = c.relnamespace 423 WHERE c.relkind = ANY(%s) """ 424 ) 425 426 params = [relkinds] 427 428 if schema_pattern: 429 sql += " AND n.nspname ~ %s" 430 params.append(schema_pattern) 431 else: 432 sql += """ 433 AND n.nspname <> 'pg_catalog' 434 AND n.nspname <> 'information_schema' 435 AND n.nspname !~ '^pg_toast' 436 AND pg_catalog.pg_table_is_visible(c.oid) """ 437 438 if table_pattern: 439 sql += " AND c.relname ~ %s" 440 params.append(table_pattern) 441 442 sql = cur.mogrify(sql + " ORDER BY 1, 2", params) 443 444 log.debug(sql) 445 cur.execute(sql) 446 447 if cur.description: 448 headers = [x[0] for x in cur.description] 449 return [(None, cur, headers, cur.statusmessage)] 450 451 452@special_command("\\dt", "\\dt[+] [pattern]", "List tables.") 453def list_tables(cur, pattern, verbose): 454 return list_objects(cur, pattern, verbose, ["r", "p", ""]) 455 456 457@special_command("\\dv", "\\dv[+] [pattern]", "List views.") 458def list_views(cur, pattern, verbose): 459 return list_objects(cur, pattern, verbose, ["v", "s", ""]) 460 461 462@special_command("\\dm", "\\dm[+] [pattern]", "List materialized views.") 463def list_materialized_views(cur, pattern, verbose): 464 return list_objects(cur, pattern, verbose, ["m", "s", ""]) 465 466 467@special_command("\\ds", "\\ds[+] [pattern]", "List sequences.") 468def list_sequences(cur, pattern, verbose): 469 return list_objects(cur, pattern, verbose, ["S", "s", ""]) 470 471 472@special_command("\\di", "\\di[+] [pattern]", "List indexes.") 473def list_indexes(cur, pattern, verbose): 474 return list_objects(cur, pattern, verbose, ["i", "s", ""]) 475 476 477@special_command("\\df", "\\df[+] [pattern]", "List functions.") 478def list_functions(cur, pattern, verbose): 479 480 if verbose: 481 verbose_columns = """ 482 ,CASE 483 WHEN p.provolatile = 'i' THEN 'immutable' 484 WHEN p.provolatile = 's' THEN 'stable' 485 WHEN p.provolatile = 'v' THEN 'volatile' 486 END as "Volatility", 487 pg_catalog.pg_get_userbyid(p.proowner) as "Owner", 488 l.lanname as "Language", 489 p.prosrc as "Source code", 490 pg_catalog.obj_description(p.oid, 'pg_proc') as "Description" """ 491 492 verbose_table = """ LEFT JOIN pg_catalog.pg_language l 493 ON l.oid = p.prolang""" 494 else: 495 verbose_columns = verbose_table = "" 496 497 if cur.connection.server_version >= 110000: 498 sql = ( 499 """ 500 SELECT n.nspname as "Schema", 501 p.proname as "Name", 502 pg_catalog.pg_get_function_result(p.oid) 503 as "Result data type", 504 pg_catalog.pg_get_function_arguments(p.oid) 505 as "Argument data types", 506 CASE 507 WHEN p.prokind = 'a' THEN 'agg' 508 WHEN p.prokind = 'w' THEN 'window' 509 WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype 510 THEN 'trigger' 511 ELSE 'normal' 512 END as "Type" """ 513 + verbose_columns 514 + """ 515 FROM pg_catalog.pg_proc p 516 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace 517 """ 518 + verbose_table 519 + """ 520 WHERE """ 521 ) 522 elif cur.connection.server_version > 90000: 523 sql = ( 524 """ 525 SELECT n.nspname as "Schema", 526 p.proname as "Name", 527 pg_catalog.pg_get_function_result(p.oid) 528 as "Result data type", 529 pg_catalog.pg_get_function_arguments(p.oid) 530 as "Argument data types", 531 CASE 532 WHEN p.proisagg THEN 'agg' 533 WHEN p.proiswindow THEN 'window' 534 WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype 535 THEN 'trigger' 536 ELSE 'normal' 537 END as "Type" """ 538 + verbose_columns 539 + """ 540 FROM pg_catalog.pg_proc p 541 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace 542 """ 543 + verbose_table 544 + """ 545 WHERE """ 546 ) 547 else: 548 sql = ( 549 """ 550 SELECT n.nspname as "Schema", 551 p.proname as "Name", 552 pg_catalog.format_type(p.prorettype, NULL) as "Result data type", 553 pg_catalog.oidvectortypes(p.proargtypes) as "Argument data types", 554 CASE 555 WHEN p.proisagg THEN 'agg' 556 WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger' 557 ELSE 'normal' 558 END as "Type" """ 559 + verbose_columns 560 + """ 561 FROM pg_catalog.pg_proc p 562 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace 563 """ 564 + verbose_table 565 + """ 566 WHERE """ 567 ) 568 569 schema_pattern, func_pattern = sql_name_pattern(pattern) 570 params = [] 571 572 if schema_pattern: 573 sql += " n.nspname ~ %s " 574 params.append(schema_pattern) 575 else: 576 sql += " pg_catalog.pg_function_is_visible(p.oid) " 577 578 if func_pattern: 579 sql += " AND p.proname ~ %s " 580 params.append(func_pattern) 581 582 if not (schema_pattern or func_pattern): 583 sql += """ AND n.nspname <> 'pg_catalog' 584 AND n.nspname <> 'information_schema' """ 585 586 sql = cur.mogrify(sql + " ORDER BY 1, 2, 4", params) 587 588 log.debug(sql) 589 cur.execute(sql) 590 591 if cur.description: 592 headers = [x[0] for x in cur.description] 593 return [(None, cur, headers, cur.statusmessage)] 594 595 596@special_command("\\dT", "\\dT[S+] [pattern]", "List data types") 597def list_datatypes(cur, pattern, verbose): 598 sql = """SELECT n.nspname as "Schema", 599 pg_catalog.format_type(t.oid, NULL) AS "Name", """ 600 601 if verbose: 602 sql += r''' t.typname AS "Internal name", 603 CASE 604 WHEN t.typrelid != 0 605 THEN CAST('tuple' AS pg_catalog.text) 606 WHEN t.typlen < 0 607 THEN CAST('var' AS pg_catalog.text) 608 ELSE CAST(t.typlen AS pg_catalog.text) 609 END AS "Size", 610 pg_catalog.array_to_string( 611 ARRAY( 612 SELECT e.enumlabel 613 FROM pg_catalog.pg_enum e 614 WHERE e.enumtypid = t.oid 615 ORDER BY e.enumsortorder 616 ), E'\n') AS "Elements", 617 pg_catalog.array_to_string(t.typacl, E'\n') 618 AS "Access privileges", 619 pg_catalog.obj_description(t.oid, 'pg_type') 620 AS "Description"''' 621 else: 622 sql += """ pg_catalog.obj_description(t.oid, 'pg_type') 623 as "Description" """ 624 625 if cur.connection.server_version > 90000: 626 sql += """ FROM pg_catalog.pg_type t 627 LEFT JOIN pg_catalog.pg_namespace n 628 ON n.oid = t.typnamespace 629 WHERE (t.typrelid = 0 OR 630 ( SELECT c.relkind = 'c' 631 FROM pg_catalog.pg_class c 632 WHERE c.oid = t.typrelid)) 633 AND NOT EXISTS( 634 SELECT 1 635 FROM pg_catalog.pg_type el 636 WHERE el.oid = t.typelem 637 AND el.typarray = t.oid) """ 638 else: 639 sql += """ FROM pg_catalog.pg_type t 640 LEFT JOIN pg_catalog.pg_namespace n 641 ON n.oid = t.typnamespace 642 WHERE (t.typrelid = 0 OR 643 ( SELECT c.relkind = 'c' 644 FROM pg_catalog.pg_class c 645 WHERE c.oid = t.typrelid)) """ 646 647 schema_pattern, type_pattern = sql_name_pattern(pattern) 648 params = [] 649 650 if schema_pattern: 651 sql += " AND n.nspname ~ %s " 652 params.append(schema_pattern) 653 else: 654 sql += " AND pg_catalog.pg_type_is_visible(t.oid) " 655 656 if type_pattern: 657 sql += """ AND (t.typname ~ %s 658 OR pg_catalog.format_type(t.oid, NULL) ~ %s) """ 659 params.extend(2 * [type_pattern]) 660 661 if not (schema_pattern or type_pattern): 662 sql += """ AND n.nspname <> 'pg_catalog' 663 AND n.nspname <> 'information_schema' """ 664 665 sql = cur.mogrify(sql + " ORDER BY 1, 2", params) 666 log.debug(sql) 667 cur.execute(sql) 668 if cur.description: 669 headers = [x[0] for x in cur.description] 670 return [(None, cur, headers, cur.statusmessage)] 671 672 673@special_command("\\dD", "\\dD[+] [pattern]", "List or describe domains.") 674def list_domains(cur, pattern, verbose): 675 if verbose: 676 extra_cols = r''', 677 pg_catalog.array_to_string(t.typacl, E'\n') AS "Access privileges", 678 d.description as "Description"''' 679 extra_joins = """ 680 LEFT JOIN pg_catalog.pg_description d ON d.classoid = t.tableoid 681 AND d.objoid = t.oid AND d.objsubid = 0""" 682 else: 683 extra_cols = extra_joins = "" 684 685 sql = """\ 686 SELECT n.nspname AS "Schema", 687 t.typname AS "Name", 688 pg_catalog.format_type(t.typbasetype, t.typtypmod) AS "Type", 689 pg_catalog.ltrim((COALESCE((SELECT (' collate ' || c.collname) 690 FROM pg_catalog.pg_collation AS c, 691 pg_catalog.pg_type AS bt 692 WHERE c.oid = t.typcollation 693 AND bt.oid = t.typbasetype 694 AND t.typcollation <> bt.typcollation) , '') 695 || CASE 696 WHEN t.typnotnull 697 THEN ' not null' 698 ELSE '' 699 END) || CASE 700 WHEN t.typdefault IS NOT NULL 701 THEN(' default ' || t.typdefault) 702 ELSE '' 703 END) AS "Modifier", 704 pg_catalog.array_to_string(ARRAY( 705 SELECT pg_catalog.pg_get_constraintdef(r.oid, TRUE) 706 FROM pg_catalog.pg_constraint AS r 707 WHERE t.oid = r.contypid), ' ') AS "Check"%s 708 FROM pg_catalog.pg_type AS t 709 LEFT JOIN pg_catalog.pg_namespace AS n ON n.oid = t.typnamespace%s 710 WHERE t.typtype = 'd' """ % ( 711 extra_cols, 712 extra_joins, 713 ) 714 715 schema_pattern, name_pattern = sql_name_pattern(pattern) 716 params = [] 717 if schema_pattern or name_pattern: 718 if schema_pattern: 719 sql += " AND n.nspname ~ %s" 720 params.append(schema_pattern) 721 if name_pattern: 722 sql += " AND t.typname ~ %s" 723 params.append(name_pattern) 724 else: 725 sql += """ 726 AND (n.nspname <> 'pg_catalog') 727 AND (n.nspname <> 'information_schema') 728 AND pg_catalog.pg_type_is_visible(t.oid)""" 729 730 sql = cur.mogrify(sql + " ORDER BY 1, 2", params) 731 log.debug(sql) 732 cur.execute(sql) 733 if cur.description: 734 headers = [x[0] for x in cur.description] 735 return [(None, cur, headers, cur.statusmessage)] 736 737 738@special_command("\\dF", "\\dF[+] [pattern]", "List text search configurations.") 739def list_text_search_configurations(cur, pattern, verbose): 740 def _find_text_search_configs(cur, pattern): 741 sql = """ 742 SELECT c.oid, 743 c.cfgname, 744 n.nspname, 745 p.prsname, 746 np.nspname AS pnspname 747 FROM pg_catalog.pg_ts_config c 748 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.cfgnamespace, 749 pg_catalog.pg_ts_parser p 750 LEFT JOIN pg_catalog.pg_namespace np ON np.oid = p.prsnamespace 751 WHERE p.oid = c.cfgparser 752 """ 753 754 params = [] 755 if pattern: 756 _, schema = sql_name_pattern(pattern) 757 sql += "AND c.cfgname ~ %s" 758 params.append(schema) 759 760 sql = cur.mogrify(sql + "ORDER BY 1, 2;", params) 761 log.debug(sql) 762 cur.execute(sql) 763 return cur.fetchall() 764 765 def _fetch_oid_details(cur, oid): 766 sql = """ 767 SELECT 768 (SELECT t.alias 769 FROM pg_catalog.ts_token_type(c.cfgparser) AS t 770 WHERE t.tokid = m.maptokentype ) AS "Token", 771 pg_catalog.btrim(ARRAY 772 (SELECT mm.mapdict::pg_catalog.regdictionary 773 FROM pg_catalog.pg_ts_config_map AS mm 774 WHERE mm.mapcfg = m.mapcfg 775 AND mm.maptokentype = m.maptokentype 776 ORDER BY mapcfg, maptokentype, mapseqno) :: pg_catalog.text, '{}') AS "Dictionaries" 777 FROM pg_catalog.pg_ts_config AS c, 778 pg_catalog.pg_ts_config_map AS m 779 WHERE c.oid = %s 780 AND m.mapcfg = c.oid 781 GROUP BY m.mapcfg, 782 m.maptokentype, 783 c.cfgparser 784 ORDER BY 1; 785 """ 786 787 sql = cur.mogrify(sql, [oid]) 788 log.debug(sql) 789 cur.execute(sql) 790 791 headers = [x[0] for x in cur.description] 792 return cur, headers, cur.statusmessage 793 794 if cur.connection.server_version < 80300: 795 not_supported = "Server versions below 8.3 do not support full text search." 796 cur, headers = [], [] 797 yield None, cur, None, not_supported 798 return 799 800 if verbose: 801 configs = _find_text_search_configs(cur, pattern) 802 803 if configs: 804 for oid, cfgname, nspname, prsname, pnspname in configs: 805 extension = '\nText search configuration "%s.%s"' % (nspname, cfgname) 806 parser = '\nParser: "%s.%s"' % (pnspname, prsname) 807 title = extension + parser 808 cur, headers, status = _fetch_oid_details(cur, oid) 809 yield title, cur, headers, status 810 else: 811 yield None, None, None, 'Did not find any results for pattern "{}".'.format( 812 pattern 813 ) 814 return 815 816 sql = """ 817 SELECT n.nspname AS "Schema", 818 c.cfgname AS "Name", 819 pg_catalog.obj_description(c.oid, 'pg_ts_config') AS "Description" 820 FROM pg_catalog.pg_ts_config c 821 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.cfgnamespace 822 """ 823 824 params = [] 825 if pattern: 826 _, schema = sql_name_pattern(pattern) 827 sql += "WHERE c.cfgname ~ %s" 828 params.append(schema) 829 830 sql = cur.mogrify(sql + "ORDER BY 1, 2", params) 831 log.debug(sql) 832 cur.execute(sql) 833 if cur.description: 834 headers = [x[0] for x in cur.description] 835 yield None, cur, headers, cur.statusmessage 836 837 838@special_command( 839 "describe", "DESCRIBE [pattern]", "", hidden=True, case_sensitive=False 840) 841@special_command( 842 "\\d", "\\d[+] [pattern]", "List or describe tables, views and sequences." 843) 844def describe_table_details(cur, pattern, verbose): 845 """ 846 Returns (title, rows, headers, status) 847 """ 848 849 # This is a simple \d[+] command. No table name to follow. 850 if not pattern: 851 return list_objects(cur, pattern, verbose, ["r", "p", "v", "m", "S", "f", ""]) 852 853 # This is a \d <tablename> command. A royal pain in the ass. 854 schema, relname = sql_name_pattern(pattern) 855 where = [] 856 params = [] 857 858 if schema: 859 where.append("n.nspname ~ %s") 860 params.append(schema) 861 else: 862 where.append("pg_catalog.pg_table_is_visible(c.oid)") 863 864 if relname: 865 where.append("c.relname OPERATOR(pg_catalog.~) %s") 866 params.append(relname) 867 868 sql = ( 869 """SELECT c.oid, n.nspname, c.relname 870 FROM pg_catalog.pg_class c 871 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 872 """ 873 + ("WHERE " + " AND ".join(where) if where else "") 874 + """ 875 ORDER BY 2,3""" 876 ) 877 sql = cur.mogrify(sql, params) 878 879 # Execute the sql, get the results and call describe_one_table_details on each table. 880 881 log.debug(sql) 882 cur.execute(sql) 883 if not (cur.rowcount > 0): 884 return [(None, None, None, "Did not find any relation named %s." % pattern)] 885 886 results = [] 887 for oid, nspname, relname in cur.fetchall(): 888 results.append(describe_one_table_details(cur, nspname, relname, oid, verbose)) 889 890 return results 891 892 893def describe_one_table_details(cur, schema_name, relation_name, oid, verbose): 894 if verbose and cur.connection.server_version >= 80200: 895 suffix = """pg_catalog.array_to_string(c.reloptions || array(select 896 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')""" 897 else: 898 suffix = "''" 899 900 if cur.connection.server_version >= 120000: 901 relhasoids = "false as relhasoids" 902 else: 903 relhasoids = "c.relhasoids" 904 905 if cur.connection.server_version >= 100000: 906 sql = """SELECT c.relchecks, c.relkind, c.relhasindex, 907 c.relhasrules, c.relhastriggers, %s, 908 %s, 909 c.reltablespace, 910 CASE WHEN c.reloftype = 0 THEN '' 911 ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text 912 END, 913 c.relpersistence, 914 c.relispartition 915 FROM pg_catalog.pg_class c 916 LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid) 917 WHERE c.oid = '%s'""" % ( 918 relhasoids, 919 suffix, 920 oid, 921 ) 922 elif cur.connection.server_version > 90000: 923 sql = """SELECT c.relchecks, c.relkind, c.relhasindex, 924 c.relhasrules, c.relhastriggers, c.relhasoids, 925 %s, 926 c.reltablespace, 927 CASE WHEN c.reloftype = 0 THEN '' 928 ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text 929 END, 930 c.relpersistence, 931 false as relispartition 932 FROM pg_catalog.pg_class c 933 LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid) 934 WHERE c.oid = '%s'""" % ( 935 suffix, 936 oid, 937 ) 938 elif cur.connection.server_version >= 80400: 939 sql = """SELECT c.relchecks, 940 c.relkind, 941 c.relhasindex, 942 c.relhasrules, 943 c.relhastriggers, 944 c.relhasoids, 945 %s, 946 c.reltablespace, 947 0 AS reloftype, 948 'p' AS relpersistence, 949 false as relispartition 950 FROM pg_catalog.pg_class c 951 LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid) 952 WHERE c.oid = '%s'""" % ( 953 suffix, 954 oid, 955 ) 956 else: 957 sql = """SELECT c.relchecks, 958 c.relkind, 959 c.relhasindex, 960 c.relhasrules, 961 c.reltriggers > 0 AS relhastriggers, 962 c.relhasoids, 963 %s, 964 c.reltablespace, 965 0 AS reloftype, 966 'p' AS relpersistence, 967 false as relispartition 968 FROM pg_catalog.pg_class c 969 LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid) 970 WHERE c.oid = '%s'""" % ( 971 suffix, 972 oid, 973 ) 974 975 # Create a namedtuple called tableinfo and match what's in describe.c 976 977 log.debug(sql) 978 cur.execute(sql) 979 if cur.rowcount > 0: 980 tableinfo = TableInfo._make(cur.fetchone()) 981 else: 982 return (None, None, None, "Did not find any relation with OID %s." % oid) 983 984 # If it's a seq, fetch it's value and store it for later. 985 if tableinfo.relkind == "S": 986 # Do stuff here. 987 sql = '''SELECT * FROM "%s"."%s"''' % (schema_name, relation_name) 988 log.debug(sql) 989 cur.execute(sql) 990 if not (cur.rowcount > 0): 991 return (None, None, None, "Something went wrong.") 992 993 seq_values = cur.fetchone() 994 995 # Get column info 996 cols = 0 997 att_cols = {} 998 sql = """SELECT a.attname, 999 pg_catalog.format_type(a.atttypid, a.atttypmod) 1000 , (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid, true) for 128) 1001 FROM pg_catalog.pg_attrdef d 1002 WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) 1003 , a.attnotnull""" 1004 att_cols["attname"] = cols 1005 cols += 1 1006 att_cols["atttype"] = cols 1007 cols += 1 1008 att_cols["attrdef"] = cols 1009 cols += 1 1010 att_cols["attnotnull"] = cols 1011 cols += 1 1012 if cur.connection.server_version >= 90100: 1013 sql += """,\n(SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t 1014 WHERE c.oid = a.attcollation 1015 AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation""" 1016 else: 1017 sql += ",\n NULL AS attcollation" 1018 att_cols["attcollation"] = cols 1019 cols += 1 1020 if cur.connection.server_version >= 100000: 1021 sql += ",\n a.attidentity" 1022 else: 1023 sql += ",\n ''::pg_catalog.char AS attidentity" 1024 att_cols["attidentity"] = cols 1025 cols += 1 1026 if cur.connection.server_version >= 120000: 1027 sql += ",\n a.attgenerated" 1028 else: 1029 sql += ",\n ''::pg_catalog.char AS attgenerated" 1030 att_cols["attgenerated"] = cols 1031 cols += 1 1032 # index, or partitioned index 1033 if tableinfo.relkind == "i" or tableinfo.relkind == "I": 1034 if cur.connection.server_version >= 110000: 1035 sql += ( 1036 ",\n CASE WHEN a.attnum <= (SELECT i.indnkeyatts FROM pg_catalog.pg_index i " 1037 "WHERE i.indexrelid = '%s') THEN 'yes' ELSE 'no' END AS is_key" % oid 1038 ) 1039 att_cols["indexkey"] = cols 1040 cols += 1 1041 sql += ",\n pg_catalog.pg_get_indexdef(a.attrelid, a.attnum, TRUE) AS indexdef" 1042 else: 1043 sql += """,\n NULL AS indexdef""" 1044 att_cols["indexdef"] = cols 1045 cols += 1 1046 if tableinfo.relkind == "f" and cur.connection.server_version >= 90200: 1047 sql += """, CASE WHEN attfdwoptions IS NULL THEN '' ELSE '(' || 1048 array_to_string(ARRAY(SELECT quote_ident(option_name) || ' ' 1049 || quote_literal(option_value) FROM 1050 pg_options_to_table(attfdwoptions)), ', ') || ')' END AS attfdwoptions""" 1051 else: 1052 sql += """, NULL AS attfdwoptions""" 1053 att_cols["attfdwoptions"] = cols 1054 cols += 1 1055 if verbose: 1056 sql += """, a.attstorage""" 1057 att_cols["attstorage"] = cols 1058 cols += 1 1059 if ( 1060 tableinfo.relkind == "r" 1061 or tableinfo.relkind == "i" 1062 or tableinfo.relkind == "I" 1063 or tableinfo.relkind == "m" 1064 or tableinfo.relkind == "f" 1065 or tableinfo.relkind == "p" 1066 ): 1067 sql += ( 1068 ",\n CASE WHEN a.attstattarget=-1 THEN " 1069 "NULL ELSE a.attstattarget END AS attstattarget" 1070 ) 1071 att_cols["attstattarget"] = cols 1072 cols += 1 1073 if ( 1074 tableinfo.relkind == "r" 1075 or tableinfo.relkind == "v" 1076 or tableinfo.relkind == "m" 1077 or tableinfo.relkind == "f" 1078 or tableinfo.relkind == "p" 1079 or tableinfo.relkind == "c" 1080 ): 1081 sql += ",\n pg_catalog.col_description(a.attrelid, a.attnum)" 1082 att_cols["attdescr"] = cols 1083 cols += 1 1084 1085 sql += ( 1086 """ FROM pg_catalog.pg_attribute a WHERE a.attrelid = '%s' AND 1087 a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum; """ 1088 % oid 1089 ) 1090 1091 log.debug(sql) 1092 cur.execute(sql) 1093 res = cur.fetchall() 1094 1095 # Set the column names. 1096 headers = ["Column", "Type"] 1097 1098 show_modifiers = False 1099 if ( 1100 tableinfo.relkind == "r" 1101 or tableinfo.relkind == "p" 1102 or tableinfo.relkind == "v" 1103 or tableinfo.relkind == "m" 1104 or tableinfo.relkind == "f" 1105 or tableinfo.relkind == "c" 1106 ): 1107 headers.append("Modifiers") 1108 show_modifiers = True 1109 1110 if tableinfo.relkind == "S": 1111 headers.append("Value") 1112 1113 if tableinfo.relkind == "i": 1114 headers.append("Definition") 1115 1116 if tableinfo.relkind == "f": 1117 headers.append("FDW Options") 1118 1119 if verbose: 1120 headers.append("Storage") 1121 if ( 1122 tableinfo.relkind == "r" 1123 or tableinfo.relkind == "m" 1124 or tableinfo.relkind == "f" 1125 ): 1126 headers.append("Stats target") 1127 # Column comments, if the relkind supports this feature. */ 1128 if ( 1129 tableinfo.relkind == "r" 1130 or tableinfo.relkind == "v" 1131 or tableinfo.relkind == "m" 1132 or tableinfo.relkind == "c" 1133 or tableinfo.relkind == "f" 1134 ): 1135 headers.append("Description") 1136 1137 view_def = "" 1138 # /* Check if table is a view or materialized view */ 1139 if (tableinfo.relkind == "v" or tableinfo.relkind == "m") and verbose: 1140 sql = """SELECT pg_catalog.pg_get_viewdef('%s'::pg_catalog.oid, true)""" % oid 1141 log.debug(sql) 1142 cur.execute(sql) 1143 if cur.rowcount > 0: 1144 view_def = cur.fetchone() 1145 1146 # Prepare the cells of the table to print. 1147 cells = [] 1148 for i, row in enumerate(res): 1149 cell = [] 1150 cell.append(row[att_cols["attname"]]) # Column 1151 cell.append(row[att_cols["atttype"]]) # Type 1152 1153 if show_modifiers: 1154 modifier = "" 1155 if row[att_cols["attcollation"]]: 1156 modifier += " collate %s" % row[att_cols["attcollation"]] 1157 if row[att_cols["attnotnull"]]: 1158 modifier += " not null" 1159 if row[att_cols["attrdef"]]: 1160 modifier += " default %s" % row[att_cols["attrdef"]] 1161 if row[att_cols["attidentity"]] == "a": 1162 modifier += " generated always as identity" 1163 elif row[att_cols["attidentity"]] == "d": 1164 modifier += " generated by default as identity" 1165 elif row[att_cols["attgenerated"]] == "s": 1166 modifier += ( 1167 " generated always as (%s) stored" % row[att_cols["attrdef"]] 1168 ) 1169 cell.append(modifier) 1170 1171 # Sequence 1172 if tableinfo.relkind == "S": 1173 cell.append(seq_values[i]) 1174 1175 # Index column 1176 if tableinfo.relkind == "i": 1177 cell.append(row[att_cols["indexdef"]]) 1178 1179 # /* FDW options for foreign table column, only for 9.2 or later */ 1180 if tableinfo.relkind == "f": 1181 cell.append(att_cols["attfdwoptions"]) 1182 1183 if verbose: 1184 storage = row[att_cols["attstorage"]] 1185 1186 if storage[0] == "p": 1187 cell.append("plain") 1188 elif storage[0] == "m": 1189 cell.append("main") 1190 elif storage[0] == "x": 1191 cell.append("extended") 1192 elif storage[0] == "e": 1193 cell.append("external") 1194 else: 1195 cell.append("???") 1196 1197 if ( 1198 tableinfo.relkind == "r" 1199 or tableinfo.relkind == "m" 1200 or tableinfo.relkind == "f" 1201 ): 1202 cell.append(row[att_cols["attstattarget"]]) 1203 1204 # /* Column comments, if the relkind supports this feature. */ 1205 if ( 1206 tableinfo.relkind == "r" 1207 or tableinfo.relkind == "v" 1208 or tableinfo.relkind == "m" 1209 or tableinfo.relkind == "c" 1210 or tableinfo.relkind == "f" 1211 ): 1212 cell.append(row[att_cols["attdescr"]]) 1213 cells.append(cell) 1214 1215 # Make Footers 1216 1217 status = [] 1218 if tableinfo.relkind == "i": 1219 # /* Footer information about an index */ 1220 1221 if cur.connection.server_version > 90000: 1222 sql = ( 1223 """SELECT i.indisunique, 1224 i.indisprimary, 1225 i.indisclustered, 1226 i.indisvalid, 1227 (NOT i.indimmediate) AND EXISTS ( 1228 SELECT 1 1229 FROM pg_catalog.pg_constraint 1230 WHERE conrelid = i.indrelid 1231 AND conindid = i.indexrelid 1232 AND contype IN ('p','u','x') 1233 AND condeferrable 1234 ) AS condeferrable, 1235 (NOT i.indimmediate) AND EXISTS ( 1236 SELECT 1 1237 FROM pg_catalog.pg_constraint 1238 WHERE conrelid = i.indrelid 1239 AND conindid = i.indexrelid 1240 AND contype IN ('p','u','x') 1241 AND condeferred 1242 ) AS condeferred, 1243 a.amname, 1244 c2.relname, 1245 pg_catalog.pg_get_expr(i.indpred, i.indrelid, true) 1246 FROM pg_catalog.pg_index i, 1247 pg_catalog.pg_class c, 1248 pg_catalog.pg_class c2, 1249 pg_catalog.pg_am a 1250 WHERE i.indexrelid = c.oid 1251 AND c.oid = '%s' 1252 AND c.relam = a.oid 1253 AND i.indrelid = c2.oid; 1254 """ 1255 % oid 1256 ) 1257 else: 1258 sql = ( 1259 """SELECT i.indisunique, 1260 i.indisprimary, 1261 i.indisclustered, 1262 't' AS indisvalid, 1263 'f' AS condeferrable, 1264 'f' AS condeferred, 1265 a.amname, 1266 c2.relname, 1267 pg_catalog.pg_get_expr(i.indpred, i.indrelid, true) 1268 FROM pg_catalog.pg_index i, 1269 pg_catalog.pg_class c, 1270 pg_catalog.pg_class c2, 1271 pg_catalog.pg_am a 1272 WHERE i.indexrelid = c.oid 1273 AND c.oid = '%s' 1274 AND c.relam = a.oid 1275 AND i.indrelid = c2.oid; 1276 """ 1277 % oid 1278 ) 1279 1280 log.debug(sql) 1281 cur.execute(sql) 1282 1283 ( 1284 indisunique, 1285 indisprimary, 1286 indisclustered, 1287 indisvalid, 1288 deferrable, 1289 deferred, 1290 indamname, 1291 indtable, 1292 indpred, 1293 ) = cur.fetchone() 1294 1295 if indisprimary: 1296 status.append("primary key, ") 1297 elif indisunique: 1298 status.append("unique, ") 1299 status.append("%s, " % indamname) 1300 1301 # /* we assume here that index and table are in same schema */ 1302 status.append('for table "%s.%s"' % (schema_name, indtable)) 1303 1304 if indpred: 1305 status.append(", predicate (%s)" % indpred) 1306 1307 if indisclustered: 1308 status.append(", clustered") 1309 1310 if not indisvalid: 1311 status.append(", invalid") 1312 1313 if deferrable: 1314 status.append(", deferrable") 1315 1316 if deferred: 1317 status.append(", initially deferred") 1318 1319 status.append("\n") 1320 # add_tablespace_footer(&cont, tableinfo.relkind, 1321 # tableinfo.tablespace, true); 1322 1323 elif tableinfo.relkind == "S": 1324 # /* Footer information about a sequence */ 1325 # /* Get the column that owns this sequence */ 1326 sql = ( 1327 "SELECT pg_catalog.quote_ident(nspname) || '.' ||" 1328 "\n pg_catalog.quote_ident(relname) || '.' ||" 1329 "\n pg_catalog.quote_ident(attname)" 1330 "\nFROM pg_catalog.pg_class c" 1331 "\nINNER JOIN pg_catalog.pg_depend d ON c.oid=d.refobjid" 1332 "\nINNER JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace" 1333 "\nINNER JOIN pg_catalog.pg_attribute a ON (" 1334 "\n a.attrelid=c.oid AND" 1335 "\n a.attnum=d.refobjsubid)" 1336 "\nWHERE d.classid='pg_catalog.pg_class'::pg_catalog.regclass" 1337 "\n AND d.refclassid='pg_catalog.pg_class'::pg_catalog.regclass" 1338 "\n AND d.objid=%s \n AND d.deptype='a'" % oid 1339 ) 1340 1341 log.debug(sql) 1342 cur.execute(sql) 1343 result = cur.fetchone() 1344 if result: 1345 status.append("Owned by: %s" % result[0]) 1346 1347 # /* 1348 # * If we get no rows back, don't show anything (obviously). We should 1349 # * never get more than one row back, but if we do, just ignore it and 1350 # * don't print anything. 1351 # */ 1352 1353 elif ( 1354 tableinfo.relkind == "r" 1355 or tableinfo.relkind == "p" 1356 or tableinfo.relkind == "m" 1357 or tableinfo.relkind == "f" 1358 ): 1359 # /* Footer information about a table */ 1360 1361 if tableinfo.hasindex: 1362 if cur.connection.server_version > 90000: 1363 sql = ( 1364 """SELECT c2.relname, 1365 i.indisprimary, 1366 i.indisunique, 1367 i.indisclustered, 1368 i.indisvalid, 1369 pg_catalog.pg_get_indexdef(i.indexrelid, 0, true), 1370 pg_catalog.pg_get_constraintdef(con.oid, true), 1371 contype, 1372 condeferrable, 1373 condeferred, 1374 c2.reltablespace 1375 FROM pg_catalog.pg_class c, 1376 pg_catalog.pg_class c2, 1377 pg_catalog.pg_index i 1378 LEFT JOIN pg_catalog.pg_constraint con 1379 ON conrelid = i.indrelid 1380 AND conindid = i.indexrelid 1381 AND contype IN ('p','u','x') 1382 WHERE c.oid = '%s' 1383 AND c.oid = i.indrelid 1384 AND i.indexrelid = c2.oid 1385 ORDER BY i.indisprimary DESC, 1386 i.indisunique DESC, 1387 c2.relname; 1388 """ 1389 % oid 1390 ) 1391 else: 1392 sql = ( 1393 """SELECT c2.relname, 1394 i.indisprimary, 1395 i.indisunique, 1396 i.indisclustered, 1397 't' AS indisvalid, 1398 pg_catalog.pg_get_indexdef(i.indexrelid, 0, true), 1399 pg_catalog.pg_get_constraintdef(con.oid, true), 1400 contype, 1401 condeferrable, 1402 condeferred, 1403 c2.reltablespace 1404 FROM pg_catalog.pg_class c, 1405 pg_catalog.pg_class c2, 1406 pg_catalog.pg_index i 1407 LEFT JOIN pg_catalog.pg_constraint con 1408 ON conrelid = i.indrelid 1409 AND contype IN ('p','u','x') 1410 WHERE c.oid = '%s' 1411 AND c.oid = i.indrelid 1412 AND i.indexrelid = c2.oid 1413 ORDER BY i.indisprimary DESC, 1414 i.indisunique DESC, 1415 c2.relname; 1416 """ 1417 % oid 1418 ) 1419 1420 log.debug(sql) 1421 result = cur.execute(sql) 1422 1423 if cur.rowcount > 0: 1424 status.append("Indexes:\n") 1425 for row in cur: 1426 1427 # /* untranslated index name */ 1428 status.append(' "%s"' % row[0]) 1429 1430 # /* If exclusion constraint, print the constraintdef */ 1431 if row[7] == "x": 1432 status.append(" ") 1433 status.append(row[6]) 1434 else: 1435 # /* Label as primary key or unique (but not both) */ 1436 if row[1]: 1437 status.append(" PRIMARY KEY,") 1438 elif row[2]: 1439 if row[7] == "u": 1440 status.append(" UNIQUE CONSTRAINT,") 1441 else: 1442 status.append(" UNIQUE,") 1443 1444 # /* Everything after "USING" is echoed verbatim */ 1445 indexdef = row[5] 1446 usingpos = indexdef.find(" USING ") 1447 if usingpos >= 0: 1448 indexdef = indexdef[(usingpos + 7) :] 1449 status.append(" %s" % indexdef) 1450 1451 # /* Need these for deferrable PK/UNIQUE indexes */ 1452 if row[8]: 1453 status.append(" DEFERRABLE") 1454 1455 if row[9]: 1456 status.append(" INITIALLY DEFERRED") 1457 1458 # /* Add these for all cases */ 1459 if row[3]: 1460 status.append(" CLUSTER") 1461 1462 if not row[4]: 1463 status.append(" INVALID") 1464 1465 status.append("\n") 1466 # printTableAddFooter(&cont, buf.data); 1467 1468 # /* Print tablespace of the index on the same line */ 1469 # add_tablespace_footer(&cont, 'i', 1470 # atooid(PQgetvalue(result, i, 10)), 1471 # false); 1472 1473 # /* print table (and column) check constraints */ 1474 if tableinfo.checks: 1475 sql = ( 1476 "SELECT r.conname, " 1477 "pg_catalog.pg_get_constraintdef(r.oid, true)\n" 1478 "FROM pg_catalog.pg_constraint r\n" 1479 "WHERE r.conrelid = '%s' AND r.contype = 'c'\n" 1480 "ORDER BY 1;" % oid 1481 ) 1482 log.debug(sql) 1483 cur.execute(sql) 1484 if cur.rowcount > 0: 1485 status.append("Check constraints:\n") 1486 for row in cur: 1487 # /* untranslated contraint name and def */ 1488 status.append(' "%s" %s' % tuple(row)) 1489 status.append("\n") 1490 1491 # /* print foreign-key constraints (there are none if no triggers) */ 1492 if tableinfo.hastriggers: 1493 sql = ( 1494 "SELECT conname,\n" 1495 " pg_catalog.pg_get_constraintdef(r.oid, true) as condef\n" 1496 "FROM pg_catalog.pg_constraint r\n" 1497 "WHERE r.conrelid = '%s' AND r.contype = 'f' ORDER BY 1;" % oid 1498 ) 1499 log.debug(sql) 1500 cur.execute(sql) 1501 if cur.rowcount > 0: 1502 status.append("Foreign-key constraints:\n") 1503 for row in cur: 1504 # /* untranslated constraint name and def */ 1505 status.append(' "%s" %s\n' % tuple(row)) 1506 1507 # /* print incoming foreign-key references (none if no triggers) */ 1508 if tableinfo.hastriggers: 1509 sql = ( 1510 "SELECT conrelid::pg_catalog.regclass, conname,\n" 1511 " pg_catalog.pg_get_constraintdef(c.oid, true) as condef\n" 1512 "FROM pg_catalog.pg_constraint c\n" 1513 "WHERE c.confrelid = '%s' AND c.contype = 'f' ORDER BY 1;" % oid 1514 ) 1515 log.debug(sql) 1516 cur.execute(sql) 1517 if cur.rowcount > 0: 1518 status.append("Referenced by:\n") 1519 for row in cur: 1520 status.append(' TABLE "%s" CONSTRAINT "%s" %s\n' % tuple(row)) 1521 1522 # /* print rules */ 1523 if tableinfo.hasrules and tableinfo.relkind != "m": 1524 sql = ( 1525 "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true)), " 1526 "ev_enabled\n" 1527 "FROM pg_catalog.pg_rewrite r\n" 1528 "WHERE r.ev_class = '%s' ORDER BY 1;" % oid 1529 ) 1530 log.debug(sql) 1531 cur.execute(sql) 1532 if cur.rowcount > 0: 1533 for category in range(4): 1534 have_heading = False 1535 for row in cur: 1536 if category == 0 and row[2] == "O": 1537 list_rule = True 1538 elif category == 1 and row[2] == "D": 1539 list_rule = True 1540 elif category == 2 and row[2] == "A": 1541 list_rule = True 1542 elif category == 3 and row[2] == "R": 1543 list_rule = True 1544 1545 if not list_rule: 1546 continue 1547 1548 if not have_heading: 1549 if category == 0: 1550 status.append("Rules:") 1551 if category == 1: 1552 status.append("Disabled rules:") 1553 if category == 2: 1554 status.append("Rules firing always:") 1555 if category == 3: 1556 status.append("Rules firing on replica only:") 1557 have_heading = True 1558 1559 # /* Everything after "CREATE RULE" is echoed verbatim */ 1560 ruledef = row[1] 1561 status.append(" %s" % ruledef) 1562 1563 # /* print partition info */ 1564 if tableinfo.relispartition: 1565 sql = ( 1566 "select quote_ident(np.nspname) || '.' ||\n" 1567 " quote_ident(cp.relname) || ' ' ||\n" 1568 " pg_get_expr(cc.relpartbound, cc.oid, true) as partition_of,\n" 1569 " pg_get_partition_constraintdef(cc.oid) as partition_constraint\n" 1570 "from pg_inherits i\n" 1571 "inner join pg_class cp\n" 1572 "on cp.oid = i.inhparent\n" 1573 "inner join pg_namespace np\n" 1574 "on np.oid = cp.relnamespace\n" 1575 "inner join pg_class cc\n" 1576 "on cc.oid = i.inhrelid\n" 1577 "inner join pg_namespace nc\n" 1578 "on nc.oid = cc.relnamespace\n" 1579 "where cc.oid = %s" % oid 1580 ) 1581 log.debug(sql) 1582 cur.execute(sql) 1583 for row in cur: 1584 status.append("Partition of: %s\n" % row[0]) 1585 status.append("Partition constraint: %s\n" % row[1]) 1586 1587 if tableinfo.relkind == "p": 1588 # /* print partition key */ 1589 sql = "select pg_get_partkeydef(%s)" % oid 1590 log.debug(sql) 1591 cur.execute(sql) 1592 for row in cur: 1593 status.append("Partition key: %s\n" % row[0]) 1594 # /* print list of partitions */ 1595 sql = ( 1596 "select quote_ident(n.nspname) || '.' ||\n" 1597 " quote_ident(c.relname) || ' ' ||\n" 1598 " pg_get_expr(c.relpartbound, c.oid, true)\n" 1599 "from pg_inherits i\n" 1600 "inner join pg_class c\n" 1601 "on c.oid = i.inhrelid\n" 1602 "inner join pg_namespace n\n" 1603 "on n.oid = c.relnamespace\n" 1604 "where i.inhparent = %s order by 1" % oid 1605 ) 1606 log.debug(sql) 1607 cur.execute(sql) 1608 if cur.rowcount > 0: 1609 if verbose: 1610 first = True 1611 for row in cur: 1612 if first: 1613 status.append("Partitions: %s\n" % row[0]) 1614 first = False 1615 else: 1616 status.append(" %s\n" % row[0]) 1617 else: 1618 status.append( 1619 "Number of partitions %i: (Use \\d+ to list them.)\n" 1620 % cur.rowcount 1621 ) 1622 1623 if view_def: 1624 # /* Footer information about a view */ 1625 status.append("View definition:\n") 1626 status.append("%s \n" % view_def) 1627 1628 # /* print rules */ 1629 if tableinfo.hasrules: 1630 sql = ( 1631 "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true))\n" 1632 "FROM pg_catalog.pg_rewrite r\n" 1633 "WHERE r.ev_class = '%s' AND r.rulename != '_RETURN' ORDER BY 1;" % oid 1634 ) 1635 1636 log.debug(sql) 1637 cur.execute(sql) 1638 if cur.rowcount > 0: 1639 status.append("Rules:\n") 1640 for row in cur: 1641 # /* Everything after "CREATE RULE" is echoed verbatim */ 1642 ruledef = row[1] 1643 status.append(" %s\n" % ruledef) 1644 1645 # /* 1646 # * Print triggers next, if any (but only user-defined triggers). This 1647 # * could apply to either a table or a view. 1648 # */ 1649 if tableinfo.hastriggers: 1650 if cur.connection.server_version > 90000: 1651 sql = ( 1652 """SELECT t.tgname, 1653 pg_catalog.pg_get_triggerdef(t.oid, true), 1654 t.tgenabled 1655 FROM pg_catalog.pg_trigger t 1656 WHERE t.tgrelid = '%s' AND NOT t.tgisinternal 1657 ORDER BY 1 1658 """ 1659 % oid 1660 ) 1661 else: 1662 sql = ( 1663 """SELECT t.tgname, 1664 pg_catalog.pg_get_triggerdef(t.oid), 1665 t.tgenabled 1666 FROM pg_catalog.pg_trigger t 1667 WHERE t.tgrelid = '%s' 1668 ORDER BY 1 1669 """ 1670 % oid 1671 ) 1672 1673 log.debug(sql) 1674 cur.execute(sql) 1675 if cur.rowcount > 0: 1676 # /* 1677 # * split the output into 4 different categories. Enabled triggers, 1678 # * disabled triggers and the two special ALWAYS and REPLICA 1679 # * configurations. 1680 # */ 1681 for category in range(4): 1682 have_heading = False 1683 list_trigger = False 1684 for row in cur: 1685 # /* 1686 # * Check if this trigger falls into the current category 1687 # */ 1688 tgenabled = row[2] 1689 if category == 0: 1690 if tgenabled == "O" or tgenabled == True: 1691 list_trigger = True 1692 elif category == 1: 1693 if tgenabled == "D" or tgenabled == False: 1694 list_trigger = True 1695 elif category == 2: 1696 if tgenabled == "A": 1697 list_trigger = True 1698 elif category == 3: 1699 if tgenabled == "R": 1700 list_trigger = True 1701 if list_trigger == False: 1702 continue 1703 1704 # /* Print the category heading once */ 1705 if not have_heading: 1706 if category == 0: 1707 status.append("Triggers:") 1708 elif category == 1: 1709 status.append("Disabled triggers:") 1710 elif category == 2: 1711 status.append("Triggers firing always:") 1712 elif category == 3: 1713 status.append("Triggers firing on replica only:") 1714 status.append("\n") 1715 have_heading = True 1716 1717 # /* Everything after "TRIGGER" is echoed verbatim */ 1718 tgdef = row[1] 1719 triggerpos = tgdef.find(" TRIGGER ") 1720 if triggerpos >= 0: 1721 tgdef = triggerpos + 9 1722 1723 status.append(" %s\n" % row[1][tgdef:]) 1724 1725 # /* 1726 # * Finish printing the footer information about a table. 1727 # */ 1728 if tableinfo.relkind == "r" or tableinfo.relkind == "m" or tableinfo.relkind == "f": 1729 # /* print foreign server name */ 1730 if tableinfo.relkind == "f": 1731 # /* Footer information about foreign table */ 1732 sql = ( 1733 """SELECT s.srvname,\n 1734 array_to_string(ARRAY(SELECT 1735 quote_ident(option_name) || ' ' || 1736 quote_literal(option_value) FROM 1737 pg_options_to_table(ftoptions)), ', ') 1738 FROM pg_catalog.pg_foreign_table f,\n 1739 pg_catalog.pg_foreign_server s\n 1740 WHERE f.ftrelid = %s AND s.oid = f.ftserver;""" 1741 % oid 1742 ) 1743 log.debug(sql) 1744 cur.execute(sql) 1745 row = cur.fetchone() 1746 1747 # /* Print server name */ 1748 status.append("Server: %s\n" % row[0]) 1749 1750 # /* Print per-table FDW options, if any */ 1751 if row[1]: 1752 status.append("FDW Options: (%s)\n" % row[1]) 1753 1754 # /* print inherited tables */ 1755 if not tableinfo.relispartition: 1756 sql = ( 1757 "SELECT c.oid::pg_catalog.regclass\n" 1758 "FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i\n" 1759 "WHERE c.oid = i.inhparent\n" 1760 " AND i.inhrelid = '%s'\n" 1761 "ORDER BY inhseqno" % oid 1762 ) 1763 log.debug(sql) 1764 cur.execute(sql) 1765 spacer = "" 1766 if cur.rowcount > 0: 1767 status.append("Inherits") 1768 spacer = ":" 1769 trailer = ",\n" 1770 for idx, row in enumerate(cur, 1): 1771 if idx == 2: 1772 spacer = " " * (len("Inherits") + 1) 1773 if idx == cur.rowcount: 1774 trailer = "\n" 1775 status.append("%s %s%s" % (spacer, row[0], trailer)) 1776 1777 # /* print child tables */ 1778 if cur.connection.server_version > 90000: 1779 sql = ( 1780 """SELECT c.oid::pg_catalog.regclass 1781 FROM pg_catalog.pg_class c, 1782 pg_catalog.pg_inherits i 1783 WHERE c.oid = i.inhrelid 1784 AND i.inhparent = '%s' 1785 ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text; 1786 """ 1787 % oid 1788 ) 1789 else: 1790 sql = ( 1791 """SELECT c.oid::pg_catalog.regclass 1792 FROM pg_catalog.pg_class c, 1793 pg_catalog.pg_inherits i 1794 WHERE c.oid = i.inhrelid 1795 AND i.inhparent = '%s' 1796 ORDER BY c.oid; 1797 """ 1798 % oid 1799 ) 1800 1801 log.debug(sql) 1802 cur.execute(sql) 1803 1804 if not verbose: 1805 # /* print the number of child tables, if any */ 1806 if cur.rowcount > 0: 1807 status.append( 1808 "Number of child tables: %d (Use \\d+ to list" 1809 " them.)\n" % cur.rowcount 1810 ) 1811 else: 1812 if cur.rowcount > 0: 1813 status.append("Child tables") 1814 1815 spacer = ":" 1816 trailer = ",\n" 1817 # /* display the list of child tables */ 1818 for idx, row in enumerate(cur, 1): 1819 if idx == 2: 1820 spacer = " " * (len("Child tables") + 1) 1821 if idx == cur.rowcount: 1822 trailer = "\n" 1823 status.append("%s %s%s" % (spacer, row[0], trailer)) 1824 1825 # /* Table type */ 1826 if tableinfo.reloftype: 1827 status.append("Typed table of type: %s\n" % tableinfo.reloftype) 1828 1829 # /* OIDs, if verbose and not a materialized view */ 1830 if verbose and tableinfo.relkind != "m": 1831 status.append("Has OIDs: %s\n" % ("yes" if tableinfo.hasoids else "no")) 1832 1833 # /* Tablespace info */ 1834 # add_tablespace_footer(&cont, tableinfo.relkind, tableinfo.tablespace, 1835 # true); 1836 1837 # /* reloptions, if verbose */ 1838 if verbose and tableinfo.reloptions: 1839 status.append("Options: %s\n" % tableinfo.reloptions) 1840 1841 return (None, cells, headers, "".join(status)) 1842 1843 1844def sql_name_pattern(pattern): 1845 """ 1846 Takes a wildcard-pattern and converts to an appropriate SQL pattern to be 1847 used in a WHERE clause. 1848 1849 Returns: schema_pattern, table_pattern 1850 1851 >>> sql_name_pattern('foo*."b""$ar*"') 1852 ('^(foo.*)$', '^(b"\\\\$ar\\\\*)$') 1853 """ 1854 1855 inquotes = False 1856 relname = "" 1857 schema = None 1858 pattern_len = len(pattern) 1859 i = 0 1860 1861 while i < pattern_len: 1862 c = pattern[i] 1863 if c == '"': 1864 if inquotes and i + 1 < pattern_len and pattern[i + 1] == '"': 1865 relname += '"' 1866 i += 1 1867 else: 1868 inquotes = not inquotes 1869 elif not inquotes and c.isupper(): 1870 relname += c.lower() 1871 elif not inquotes and c == "*": 1872 relname += ".*" 1873 elif not inquotes and c == "?": 1874 relname += "." 1875 elif not inquotes and c == ".": 1876 # Found schema/name separator, move current pattern to schema 1877 schema = relname 1878 relname = "" 1879 else: 1880 # Dollar is always quoted, whether inside quotes or not. 1881 if c == "$" or inquotes and c in "|*+?()[]{}.^\\": 1882 relname += "\\" 1883 relname += c 1884 i += 1 1885 1886 if relname: 1887 relname = "^(" + relname + ")$" 1888 1889 if schema: 1890 schema = "^(" + schema + ")$" 1891 1892 return schema, relname 1893 1894 1895class _FakeCursor(list): 1896 "Minimalistic wrapper simulating a real cursor, as far as pgcli is concerned." 1897 1898 def rowcount(self): 1899 return len(self) 1900 1901 1902@special_command("\\sf", "\\sf[+] FUNCNAME", "Show a function's definition.") 1903def show_function_definition(cur, pattern, verbose): 1904 if "(" in pattern: 1905 sql = cur.mogrify( 1906 "SELECT %s::pg_catalog.regprocedure::pg_catalog.oid", [pattern] 1907 ) 1908 else: 1909 sql = cur.mogrify("SELECT %s::pg_catalog.regproc::pg_catalog.oid", [pattern]) 1910 log.debug(sql) 1911 cur.execute(sql) 1912 (foid,) = cur.fetchone() 1913 1914 sql = cur.mogrify("SELECT pg_catalog.pg_get_functiondef(%s) as source", [foid]) 1915 log.debug(sql) 1916 cur.execute(sql) 1917 if cur.description: 1918 headers = [x[0] for x in cur.description] 1919 if verbose: 1920 (source,) = cur.fetchone() 1921 rows = _FakeCursor() 1922 rown = None 1923 for row in source.splitlines(): 1924 if rown is None: 1925 if row.startswith("AS "): 1926 rown = 1 1927 else: 1928 rown += 1 1929 rows.append("%-7s %s" % ("" if rown is None else rown, row)) 1930 cur = [("\n".join(rows) + "\n",)] 1931 else: 1932 headers = None 1933 return [(None, cur, headers, None)] 1934 1935 1936@special_command("\\!", "\\! [command]", "Pass commands to shell.") 1937def shell_command(cur, pattern, verbose): 1938 cur, headers = [], [] 1939 params = shlex.split(pattern) 1940 return [(None, cur, headers, subprocess.call(params))] 1941 1942 1943@special_command("\\dE", "\\dE[+] [pattern]", "List foreign tables.", aliases=()) 1944def list_foreign_tables(cur, pattern, verbose): 1945 1946 if verbose: 1947 verbose_cols = """ 1948 , pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size", 1949 pg_catalog.obj_description(c.oid, 'pg_class') as "Description" """ 1950 else: 1951 verbose_cols = "" 1952 1953 if pattern: 1954 _, tbl_name = sql_name_pattern(pattern) 1955 filter = " AND c.relname OPERATOR(pg_catalog.~) '^(%s)$' " % tbl_name 1956 else: 1957 filter = "" 1958 1959 query = """ 1960 SELECT n.nspname as "Schema", 1961 c.relname as "Name", 1962 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", 1963 pg_catalog.pg_get_userbyid(c.relowner) as "Owner" 1964 %s 1965 FROM pg_catalog.pg_class c 1966 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 1967 WHERE c.relkind IN ('f','') 1968 AND n.nspname <> 'pg_catalog' 1969 AND n.nspname <> 'information_schema' 1970 AND n.nspname !~ '^pg_toast' 1971 AND pg_catalog.pg_table_is_visible(c.oid) 1972 %s 1973 ORDER BY 1,2; 1974 """ % ( 1975 verbose_cols, 1976 filter, 1977 ) 1978 1979 cur.execute(query) 1980 if cur.description: 1981 headers = [x[0] for x in cur.description] 1982 return [(None, cur, headers, cur.statusmessage)] 1983 else: 1984 return [(None, None, None, cur.statusmessage)] 1985