PostgreSQL 9.4.0 官方文档

需积分: 10 36 浏览量更新于2024-07-21 收藏 7.19MB PDF 举报

"这是一份关于PostgreSQL 9.4版本的官方文档，由PostgreSQL全球开发团队编撰。这份文档详细介绍了PostgreSQL 9.4的功能、使用方法和技术细节，旨在帮助用户理解和操作这个开源关系型数据库管理系统。文档包含了版权信息以及使用许可条款，表明PostgreSQL软件可以免费用于个人和商业用途，但需保留版权信息。同时，该文档还声明了加州大学不承担任何因使用此软件或其文档可能导致的直接或间接损失的责任，并且不提供任何形式的保修。" PostgreSQL是一种强大的开源关系数据库管理系统，以其高度的稳定性和可扩展性而闻名。在PostgreSQL 9.4版本中，它引入了许多新特性和改进，包括： 1. **并行查询**：这是PostgreSQL 9.4中的一个重要增强，允许在执行某些类型的查询时利用多核处理器进行并行处理，显著提高了大型数据集的处理速度。 2. **行级安全策略（Row-Level Security, RLS）**：RLS是9.4引入的一个安全特性，使得数据库管理员能够更细粒度地控制用户对表中数据的访问权限，提供了更强大的数据隔离和保护机制。 3. **窗口函数优化**：9.4版提升了窗口函数的性能，使得在数据分组和排序后的分析查询更加高效。 4. **JSONB支持**：这一版本增加了对JSONB数据类型的支持，这是一个二进制形式的JSON存储，相比传统的JSON类型，它提供了更快的查询速度和更小的存储空间。 5. **索引加速**：PostgreSQL 9.4增强了索引的性能，包括改进的B树索引和Gin索引，以提高查询效率。 6. **复制增强**：9.4版改进了复制功能，包括流复制和逻辑复制，使得数据备份和恢复更为灵活和可靠。 7. **其他改进**：还包括对触发器、函数、表分区、PL/pgSQL语言等多方面的优化和增强，以提升整体的系统性能和用户体验。通过这份文档，用户可以了解到如何安装和配置PostgreSQL 9.4，创建和管理数据库，编写SQL语句，以及如何利用新的特性和功能来优化数据库的性能和安全性。此外，文档还会介绍故障排查、性能调优、备份与恢复等关键操作，帮助用户全面掌握PostgreSQL 9.4的使用。

31.3.2. Retrieving Query Result Information ...................................................... 674

31.3.3. Retrieving Other Result Information .......................................................678

31.3.4. Escaping Strings for Inclusion in SQL Commands .................................678

31.4. Asynchronous Command Processing................................................................... 681

31.5. Retrieving Query Results Row-By-Row .............................................................. 685

31.6. Canceling Queries in Progress.............................................................................. 686

31.7. The Fast-Path Interface.........................................................................................687

31.8. Asynchronous Notiﬁcation................................................................................... 688

31.9. Functions Associated with the COPY Command ..................................................688

31.9.1. Functions for Sending COPY Data............................................................689

31.9.2. Functions for Receiving COPY Data.........................................................690

31.9.3. Obsolete Functions for COPY ...................................................................691

31.10. Control Functions ............................................................................................... 693

31.11. Miscellaneous Functions ....................................................................................694

31.12. Notice Processing ............................................................................................... 696

31.13. Event System...................................................................................................... 697

31.13.1. Event Types............................................................................................697

31.13.2. Event Callback Procedure...................................................................... 699

31.13.3. Event Support Functions........................................................................ 700

31.13.4. Event Example.......................................................................................701

31.14. Environment Variables .......................................................................................703

31.15. The Password File ..............................................................................................705

31.16. The Connection Service File ..............................................................................705

31.17. LDAP Lookup of Connection Parameters.......................................................... 706

31.18. SSL Support........................................................................................................707

31.18.1. Client Veriﬁcation of Server Certiﬁcates ............................................... 707

31.18.2. Client Certiﬁcates...................................................................................708

31.18.3. Protection Provided in Different Modes ................................................708

31.18.4. SSL Client File Usage............................................................................710

31.18.5. SSL Library Initialization ......................................................................710

31.19. Behavior in Threaded Programs......................................................................... 711

31.20. Building libpq Programs.....................................................................................712

31.21. Example Programs.............................................................................................. 713

32. Large Objects ....................................................................................................................722

32.1. Introduction ..........................................................................................................722

32.2. Implementation Features ......................................................................................722

32.3. Client Interfaces.................................................................................................... 722

32.3.1. Creating a Large Object...........................................................................723

32.3.2. Importing a Large Object......................................................................... 723

32.3.3. Exporting a Large Object......................................................................... 724

32.3.4. Opening an Existing Large Object...........................................................724

32.3.5. Writing Data to a Large Object................................................................724

32.3.6. Reading Data from a Large Object ..........................................................725

32.3.7. Seeking in a Large Object........................................................................ 725

32.3.8. Obtaining the Seek Position of a Large Object........................................725

32.3.9. Truncating a Large Object ....................................................................... 726

32.3.10. Closing a Large Object Descriptor ........................................................726

32.3.11. Removing a Large Object ......................................................................726

32.4. Server-side Functions ...........................................................................................727

32.5. Example Program ................................................................................................. 728

33. ECPG - Embedded SQL in C............................................................................................733

33.1. The Concept.......................................................................................................... 733

xvi

33.2. Managing Database Connections .........................................................................733

33.2.1. Connecting to the Database Server..........................................................733

33.2.2. Choosing a Connection............................................................................734

33.2.3. Closing a Connection...............................................................................736

33.3. Running SQL Commands.....................................................................................736

33.3.1. Executing SQL Statements ......................................................................736

33.3.2. Using Cursors...........................................................................................737

33.3.3. Managing Transactions............................................................................737

33.3.4. Prepared Statements................................................................................. 738

33.4. Using Host Variables ............................................................................................739

33.4.1. Overview..................................................................................................739

33.4.2. Declare Sections.......................................................................................739

33.4.3. Retrieving Query Results.........................................................................740

33.4.4. Type Mapping ..........................................................................................741

33.4.4.1. Handling Character Strings ......................................................... 742

33.4.4.2. Accessing Special Data Types..................................................... 742

33.4.4.2.1. timestamp, date...............................................................743

33.4.4.2.2. interval ............................................................................ 743

33.4.4.2.3. numeric, decimal.............................................................744

33.4.4.3. Host Variables with Nonprimitive Types .................................... 745

33.4.4.3.1. Arrays .............................................................................745

33.4.4.3.2. Structures ........................................................................ 746

33.4.4.3.3. Typedefs..........................................................................747

33.4.4.3.4. Pointers ...........................................................................748

33.4.5. Handling Nonprimitive SQL Data Types.................................................748

33.4.5.1. Arrays.......................................................................................... 748

33.4.5.2. Composite Types .........................................................................750

33.4.5.3. User-deﬁned Base Types .............................................................751

33.4.6. Indicators..................................................................................................752

33.5. Dynamic SQL....................................................................................................... 753

33.5.1. Executing Statements without a Result Set ............................................. 753

33.5.2. Executing a Statement with Input Parameters ......................................... 753

33.5.3. Executing a Statement with a Result Set ................................................. 754

33.6. pgtypes Library.....................................................................................................755

33.6.1. The numeric Type ....................................................................................755

33.6.2. The date Type...........................................................................................758

33.6.3. The timestamp Type.................................................................................761

33.6.4. The interval Type .....................................................................................765

33.6.5. The decimal Type.....................................................................................765

33.6.6. errno Values of pgtypeslib .......................................................................766

33.6.7. Special Constants of pgtypeslib...............................................................767

33.7. Using Descriptor Areas ........................................................................................767

33.7.1. Named SQL Descriptor Areas .................................................................767

33.7.2. SQLDA Descriptor Areas ........................................................................769

33.7.2.1. SQLDA Data Structure................................................................770

33.7.2.1.1. sqlda_t Structure ............................................................. 770

33.7.2.1.2. sqlvar_t Structure............................................................ 771

33.7.2.1.3. struct sqlname Structure .................................................772

33.7.2.2. Retrieving a Result Set Using an SQLDA ..................................772

33.7.2.3. Passing Query Parameters Using an SQLDA..............................774

33.7.2.4. A Sample Application Using SQLDA ........................................775

33.8. Error Handling...................................................................................................... 780

xvii

33.8.1. Setting Callbacks ..................................................................................... 780

33.8.2. sqlca ......................................................................................................... 782

33.8.3. SQLSTATE vs. SQLCODE........................................................................... 784

33.9. Preprocessor Directives ........................................................................................ 787

33.9.1. Including Files ......................................................................................... 787

33.9.2. The deﬁne and undef Directives .............................................................. 788

33.9.3. ifdef, ifndef, else, elif, and endif Directives.............................................788

33.10. Processing Embedded SQL Programs................................................................ 789

33.11. Library Functions ...............................................................................................790

33.12. Large Objects...................................................................................................... 791

33.13. C++ Applications ...............................................................................................792

33.13.1. Scope for Host Variables........................................................................793

33.13.2. C++ Application Development with External C Module ......................794

33.14. Embedded SQL Commands ...............................................................................796

ALLOCATE DESCRIPTOR ...............................................................................796

CONNECT...........................................................................................................798

DEALLOCATE DESCRIPTOR .......................................................................... 801

DECLARE ...........................................................................................................802

DESCRIBE ..........................................................................................................804

DISCONNECT ....................................................................................................805

EXECUTE IMMEDIATE.................................................................................... 807

GET DESCRIPTOR ............................................................................................ 808

OPEN ...................................................................................................................811

PREPARE ............................................................................................................813

SET AUTOCOMMIT .......................................................................................... 814

SET CONNECTION ........................................................................................... 815

SET DESCRIPTOR.............................................................................................816

TYPE....................................................................................................................818

VAR...................................................................................................................... 820

WHENEVER .......................................................................................................821

33.15. Informix Compatibility Mode ............................................................................823

33.15.1. Additional Types.................................................................................... 823

33.15.2. Additional/Missing Embedded SQL Statements ...................................823

33.15.3. Informix-compatible SQLDA Descriptor Areas.................................... 824

33.15.4. Additional Functions..............................................................................827

33.15.5. Additional Constants..............................................................................835

33.16. Internals .............................................................................................................. 836

34. The Information Schema...................................................................................................839

34.1. The Schema ..........................................................................................................839

34.2. Data Types ............................................................................................................839

34.3. information_schema_catalog_name ...........................................................840

34.4. administrable_role_authorizations.......................................................840

34.5. applicable_roles............................................................................................840

34.6. attributes.........................................................................................................841

34.7. character_sets................................................................................................ 845

34.8. check_constraint_routine_usage .............................................................846

34.9. check_constraints ......................................................................................... 846

34.10. collations.......................................................................................................847

34.11. collation_character_set_applicability ............................................847

34.12. column_domain_usage ...................................................................................848

34.13. column_options..............................................................................................848

34.14. column_privileges ....................................................................................... 849

xviii

34.15. column_udt_usage..........................................................................................849

34.16. columns ............................................................................................................. 850

34.17. constraint_column_usage .......................................................................... 855

34.18. constraint_table_usage............................................................................. 856

34.19. data_type_privileges................................................................................. 856

34.20. domain_constraints .....................................................................................857

34.21. domain_udt_usage..........................................................................................858

34.22. domains ............................................................................................................. 858

34.23. element_types ................................................................................................ 861

34.24. enabled_roles ................................................................................................ 864

34.25. foreign_data_wrapper_options................................................................865

34.26. foreign_data_wrappers............................................................................... 865

34.27. foreign_server_options............................................................................. 865

34.28. foreign_servers............................................................................................866

34.29. foreign_table_options............................................................................... 866

34.30. foreign_tables..............................................................................................867

34.31. key_column_usage..........................................................................................867

34.32. parameters.......................................................................................................868

34.33. referential_constraints .......................................................................... 871

34.34. role_column_grants .....................................................................................872

34.35. role_routine_grants ...................................................................................872

34.36. role_table_grants ....................................................................................... 873

34.37. role_udt_grants............................................................................................874

34.38. role_usage_grants ....................................................................................... 875

34.39. routine_privileges .....................................................................................875

34.40. routines...........................................................................................................876

34.41. schemata...........................................................................................................882

34.42. sequences.........................................................................................................882

34.43. sql_features ..................................................................................................884

34.44. sql_implementation_info .......................................................................... 884

34.45. sql_languages ................................................................................................ 885

34.46. sql_packages ..................................................................................................886

34.47. sql_parts.........................................................................................................886

34.48. sql_sizing.......................................................................................................887

34.49. sql_sizing_profiles ...................................................................................887

34.50. table_constraints ....................................................................................... 888

34.51. table_privileges..........................................................................................888

34.52. tables ...............................................................................................................889

34.53. triggered_update_columns ........................................................................ 890

34.54. triggers...........................................................................................................891

34.55. udt_privileges..............................................................................................892

34.56. usage_privileges..........................................................................................893

34.57. user_defined_types .....................................................................................894

34.58. user_mapping_options................................................................................. 895

34.59. user_mappings ................................................................................................ 896

34.60. view_column_usage ....................................................................................... 896

34.61. view_routine_usage .....................................................................................897

34.62. view_table_usage..........................................................................................898

34.63. views .................................................................................................................898

xix

V. Server Programming ................................................................................................................. 900

35. Extending SQL..................................................................................................................902

35.1. How Extensibility Works......................................................................................902

35.2. The PostgreSQL Type System.............................................................................. 902

35.2.1. Base Types ...............................................................................................902

35.2.2. Composite Types......................................................................................902

35.2.3. Domains ...................................................................................................903

35.2.4. Pseudo-Types ...........................................................................................903

35.2.5. Polymorphic Types ..................................................................................903

35.3. User-deﬁned Functions.........................................................................................904

35.4. Query Language (SQL) Functions .......................................................................904

35.4.1. Arguments for SQL Functions................................................................. 905

35.4.2. SQL Functions on Base Types................................................................. 906

35.4.3. SQL Functions on Composite Types .......................................................908

35.4.4. SQL Functions with Output Parameters ..................................................911

35.4.5. SQL Functions with Variable Numbers of Arguments............................ 912

35.4.6. SQL Functions with Default Values for Arguments................................ 912

35.4.7. SQL Functions as Table Sources .............................................................913

35.4.8. SQL Functions Returning Sets ................................................................914

35.4.9. SQL Functions Returning TABLE ............................................................916

35.4.10. Polymorphic SQL Functions .................................................................916

35.4.11. SQL Functions with Collations..............................................................918

35.5. Function Overloading........................................................................................... 919

35.6. Function Volatility Categories..............................................................................920

35.7. Procedural Language Functions ...........................................................................921

35.8. Internal Functions................................................................................................. 921

35.9. C-Language Functions.......................................................................................... 922

35.9.1. Dynamic Loading.....................................................................................922

35.9.2. Base Types in C-Language Functions......................................................923

35.9.3. Version 0 Calling Conventions ................................................................926

35.9.4. Version 1 Calling Conventions ................................................................928

35.9.5. Writing Code............................................................................................930

35.9.6. Compiling and Linking Dynamically-loaded Functions..........................931

35.9.7. Composite-type Arguments .....................................................................933

35.9.8. Returning Rows (Composite Types) ........................................................ 935

35.9.9. Returning Sets..........................................................................................936

35.9.10. Polymorphic Arguments and Return Types........................................... 941

35.9.11. Transform Functions..............................................................................943

35.9.12. Shared Memory and LWLocks .............................................................. 943

35.9.13. Using C++ for Extensibility...................................................................944

35.10. User-deﬁned Aggregates ....................................................................................944

35.10.1. Moving-Aggregate Mode.......................................................................946

35.10.2. Polymorphic and Variadic Aggregates................................................... 947

35.10.3. Ordered-Set Aggregates......................................................................... 949

35.10.4. Support Functions for Aggregates .........................................................950

35.11. User-deﬁned Types .............................................................................................950

35.12. User-deﬁned Operators.......................................................................................954

35.13. Operator Optimization Information....................................................................955

35.13.1. COMMUTATOR.......................................................................................... 955

35.13.2. NEGATOR ................................................................................................ 956

35.13.3. RESTRICT ..............................................................................................956

剩余2947页未读，继续阅读

elapse2055

粉丝: 0
资源: 1

PostgreSQL 9.4.0 官方文档

postgresql-9.2-官方手册

postgre使用方法介绍

postgresql-9.4.15-1-linux-x64.run

postgresql-phppgadmin容器和postgresql-postgres区别

windows如何将postgresql-x64-14服务修改为my_postgresql-x64-14

sudo: postgresql-setup: command not found

安装postgresql-devel

postgresql-libs-9.2.24-7.el7_9.x86_64 linux安装

liunx中重启pgsql数据库报错：Job for postgresql-12.service failed because the control process exited with error code. See "systemctl status postgresql-12.service" and "journalctl -xe" for details. 如何解决

最新资源