PostgreSQL 9.4.4 官方参考手册

postgresql

需积分: 9 120 浏览量更新于2024-07-21 收藏 6.93MB PDF 举报

身份认证购VIP最低享 7 折!

领优惠券(最高得80元）

"这是一份详细的PostgreSQL 9.4.4版本的官方参考文档，由PostgreSQL全球开发团队提供。这份文档对于深入理解和有效利用PostgreSQL这一开源数据库系统至关重要。文档涵盖了从基础到高级的各种功能，包括安装、配置、查询语言、数据类型、表、索引、视图、触发器、存储过程、事务处理、复制、性能优化等多个方面。" PostgreSQL是一种强大的开源关系型数据库管理系统，以其高度的稳定性和丰富的功能而受到广泛赞誉。在PostgreSQL 9.4.4版本中，引入了许多改进和新特性，例如： 1. **JSONB支持**：9.4版本引入了JSONB数据类型，允许更高效地存储和查询JSON格式的数据，提高了处理结构化和半结构化数据的能力。 2. **并行查询**：在9.4中，PostgreSQL实现了并行查询机制，尤其是在聚合和索引扫描操作中，显著提升了大数据集的处理速度。 3. **临时表空间**：允许用户创建临时表空间，提高了多用户并发环境下的性能和资源管理。 4. **窗口函数增强**：扩展了窗口函数的功能，使分析查询更为强大，可以进行复杂的行间计算和排序。 5. **即时分析（Real-time Analytics）**：通过改进的统计信息收集和分析，使得数据库能够更好地适应实时分析的需求。 6. **安全性和权限管理**：包括改进的角色管理和访问控制，确保数据的安全性。 7. **复制和高可用性**：提供了多种复制选项，如流复制和逻辑复制，增强了系统的高可用性和灾难恢复能力。 8. **性能优化**：包括更快的VACUUM操作、优化的索引访问方法等，提升了整体性能。 9. **语言扩展**：支持多种编程语言作为存储过程，如PL/pgSQL、Perl、Python等，增强了数据库的灵活性和可编程性。在阅读这份文档时，读者将深入了解PostgreSQL的体系结构，学习如何创建、管理数据库，编写高效的SQL查询，以及如何进行性能调优。此外，文档还包含错误处理、日志记录、备份与恢复等实用信息，对于DBA和开发者来说是不可或缺的资源。文档中的每个章节都详细解释了各个主题，并且通常配有示例和最佳实践，便于理解和应用。PostgreSQL 9.4.4的官方文档是一份详尽的参考资料，无论你是初学者还是经验丰富的专业人士，都能从中获益匪浅。

资源详情

资源推荐

31.3.2. Retrieving Query Result Information ...................................................... 675

31.3.3. Retrieving Other Result Information .......................................................679

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

31.4. Asynchronous Command Processing...................................................................682

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

31.6. Canceling Queries in Progress.............................................................................. 687

31.7. The Fast-Path Interface.........................................................................................688

31.8. Asynchronous Notiﬁcation...................................................................................689

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

31.9.1. Functions for Sending COPY Data............................................................ 690

31.9.2. Functions for Receiving COPY Data......................................................... 691

31.9.3. Obsolete Functions for COPY ...................................................................692

31.10. Control Functions ............................................................................................... 694

31.11. Miscellaneous Functions ....................................................................................695

31.12. Notice Processing ............................................................................................... 697

31.13. Event System...................................................................................................... 698

31.13.1. Event Types............................................................................................698

31.13.2. Event Callback Procedure......................................................................700

31.13.3. Event Support Functions........................................................................ 701

31.13.4. Event Example....................................................................................... 702

31.14. Environment Variables .......................................................................................704

31.15. The Password File ..............................................................................................706

31.16. The Connection Service File ..............................................................................706

31.17. LDAP Lookup of Connection Parameters..........................................................707

31.18. SSL Support........................................................................................................708

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

31.18.2. Client Certiﬁcates...................................................................................709

31.18.3. Protection Provided in Different Modes ................................................709

31.18.4. SSL Client File Usage............................................................................711

31.18.5. SSL Library Initialization ......................................................................711

31.19. Behavior in Threaded Programs.........................................................................712

31.20. Building libpq Programs.....................................................................................713

31.21. Example Programs.............................................................................................. 714

32. Large Objects ....................................................................................................................723

32.1. Introduction ..........................................................................................................723

32.2. Implementation Features ......................................................................................723

32.3. Client Interfaces.................................................................................................... 723

32.3.1. Creating a Large Object........................................................................... 724

32.3.2. Importing a Large Object.........................................................................724

32.3.3. Exporting a Large Object.........................................................................725

32.3.4. Opening an Existing Large Object...........................................................725

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

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

32.3.7. Seeking in a Large Object........................................................................726

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

32.3.9. Truncating a Large Object ....................................................................... 727

32.3.10. Closing a Large Object Descriptor ........................................................727

32.3.11. Removing a Large Object ......................................................................727

32.4. Server-side Functions ...........................................................................................728

32.5. Example Program ................................................................................................. 729

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

33.1. The Concept.......................................................................................................... 734

xvi

33.2. Managing Database Connections .........................................................................734

33.2.1. Connecting to the Database Server.......................................................... 734

33.2.2. Choosing a Connection............................................................................ 735

33.2.3. Closing a Connection............................................................................... 737

33.3. Running SQL Commands.....................................................................................737

33.3.1. Executing SQL Statements ......................................................................737

33.3.2. Using Cursors...........................................................................................738

33.3.3. Managing Transactions............................................................................ 738

33.3.4. Prepared Statements................................................................................. 739

33.4. Using Host Variables ............................................................................................740

33.4.1. Overview.................................................................................................. 740

33.4.2. Declare Sections.......................................................................................740

33.4.3. Retrieving Query Results......................................................................... 741

33.4.4. Type Mapping ..........................................................................................742

33.4.4.1. Handling Character Strings ......................................................... 743

33.4.4.2. Accessing Special Data Types.....................................................743

33.4.4.2.1. timestamp, date............................................................... 744

33.4.4.2.2. interval ............................................................................ 744

33.4.4.2.3. numeric, decimal.............................................................745

33.4.4.3. Host Variables with Nonprimitive Types .................................... 746

33.4.4.3.1. Arrays .............................................................................746

33.4.4.3.2. Structures ........................................................................ 747

33.4.4.3.3. Typedefs.......................................................................... 748

33.4.4.3.4. Pointers ...........................................................................749

33.4.5. Handling Nonprimitive SQL Data Types................................................. 749

33.4.5.1. Arrays..........................................................................................749

33.4.5.2. Composite Types .........................................................................751

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

33.4.6. Indicators..................................................................................................754

33.5. Dynamic SQL.......................................................................................................754

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

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

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

33.6. pgtypes Library.....................................................................................................756

33.6.1. The numeric Type ....................................................................................756

33.6.2. The date Type........................................................................................... 759

33.6.3. The timestamp Type................................................................................. 762

33.6.4. The interval Type .....................................................................................766

33.6.5. The decimal Type..................................................................................... 766

33.6.6. errno Values of pgtypeslib .......................................................................767

33.6.7. Special Constants of pgtypeslib............................................................... 768

33.7. Using Descriptor Areas ........................................................................................768

33.7.1. Named SQL Descriptor Areas .................................................................768

33.7.2. SQLDA Descriptor Areas ........................................................................770

33.7.2.1. SQLDA Data Structure................................................................771

33.7.2.1.1. sqlda_t Structure ............................................................. 771

33.7.2.1.2. sqlvar_t Structure............................................................772

33.7.2.1.3. struct sqlname Structure .................................................773

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

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

33.7.2.4. A Sample Application Using SQLDA ........................................776

33.8. Error Handling......................................................................................................781

xvii

33.8.1. Setting Callbacks ..................................................................................... 781

33.8.2. sqlca ......................................................................................................... 783

33.8.3. SQLSTATE vs. SQLCODE........................................................................... 785

33.9. Preprocessor Directives ........................................................................................ 788

33.9.1. Including Files ......................................................................................... 788

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

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

33.10. Processing Embedded SQL Programs................................................................790

33.11. Library Functions ...............................................................................................791

33.12. Large Objects...................................................................................................... 792

33.13. C++ Applications ...............................................................................................793

33.13.1. Scope for Host Variables........................................................................ 794

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

33.14. Embedded SQL Commands ...............................................................................797

ALLOCATE DESCRIPTOR ...............................................................................797

CONNECT...........................................................................................................799

DEALLOCATE DESCRIPTOR .......................................................................... 802

DECLARE ...........................................................................................................803

DESCRIBE ..........................................................................................................805

DISCONNECT ....................................................................................................806

EXECUTE IMMEDIATE....................................................................................808

GET DESCRIPTOR ............................................................................................ 809

OPEN ...................................................................................................................812

PREPARE ............................................................................................................814

SET AUTOCOMMIT .......................................................................................... 815

SET CONNECTION ........................................................................................... 816

SET DESCRIPTOR............................................................................................. 817

TYPE....................................................................................................................819

VAR...................................................................................................................... 821

WHENEVER .......................................................................................................822

33.15. Informix Compatibility Mode ............................................................................824

33.15.1. Additional Types....................................................................................824

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

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

33.15.4. Additional Functions..............................................................................828

33.15.5. Additional Constants..............................................................................836

33.16. Internals .............................................................................................................. 837

34. The Information Schema................................................................................................... 840

34.1. The Schema ..........................................................................................................840

34.2. Data Types ............................................................................................................840

34.3. information_schema_catalog_name ...........................................................841

34.4. administrable_role_authorizations....................................................... 841

34.5. applicable_roles............................................................................................841

34.6. attributes.........................................................................................................842

34.7. character_sets................................................................................................846

34.8. check_constraint_routine_usage .............................................................847

34.9. check_constraints ......................................................................................... 847

34.10. collations.......................................................................................................848

34.11. collation_character_set_applicability ............................................848

34.12. column_domain_usage ...................................................................................849

34.13. column_options..............................................................................................849

34.14. column_privileges ....................................................................................... 850

xviii

34.15. column_udt_usage..........................................................................................850

34.16. columns ............................................................................................................. 851

34.17. constraint_column_usage .......................................................................... 856

34.18. constraint_table_usage............................................................................. 857

34.19. data_type_privileges.................................................................................857

34.20. domain_constraints .....................................................................................858

34.21. domain_udt_usage..........................................................................................859

34.22. domains ............................................................................................................. 859

34.23. element_types ................................................................................................862

34.24. enabled_roles ................................................................................................865

34.25. foreign_data_wrapper_options................................................................866

34.26. foreign_data_wrappers...............................................................................866

34.27. foreign_server_options............................................................................. 866

34.28. foreign_servers............................................................................................ 867

34.29. foreign_table_options...............................................................................867

34.30. foreign_tables..............................................................................................868

34.31. key_column_usage..........................................................................................868

34.32. parameters.......................................................................................................869

34.33. referential_constraints .......................................................................... 872

34.34. role_column_grants .....................................................................................873

34.35. role_routine_grants ...................................................................................873

34.36. role_table_grants ....................................................................................... 874

34.37. role_udt_grants............................................................................................ 875

34.38. role_usage_grants ....................................................................................... 876

34.39. routine_privileges .....................................................................................876

34.40. routines........................................................................................................... 877

34.41. schemata........................................................................................................... 883

34.42. sequences.........................................................................................................883

34.43. sql_features ..................................................................................................885

34.44. sql_implementation_info .......................................................................... 885

34.45. sql_languages ................................................................................................886

34.46. sql_packages ..................................................................................................887

34.47. sql_parts.........................................................................................................887

34.48. sql_sizing.......................................................................................................888

34.49. sql_sizing_profiles ...................................................................................888

34.50. table_constraints ....................................................................................... 889

34.51. table_privileges..........................................................................................889

34.52. tables ...............................................................................................................890

34.53. triggered_update_columns ........................................................................ 891

34.54. triggers........................................................................................................... 892

34.55. udt_privileges..............................................................................................893

34.56. usage_privileges..........................................................................................894

34.57. user_defined_types .....................................................................................895

34.58. user_mapping_options.................................................................................896

34.59. user_mappings ................................................................................................897

34.60. view_column_usage ....................................................................................... 897

34.61. view_routine_usage .....................................................................................898

34.62. view_table_usage..........................................................................................899

34.63. views .................................................................................................................899

xix

V. Server Programming ................................................................................................................. 901

35. Extending SQL.................................................................................................................. 903

35.1. How Extensibility Works...................................................................................... 903

35.2. The PostgreSQL Type System.............................................................................. 903

35.2.1. Base Types ...............................................................................................903

35.2.2. Composite Types......................................................................................903

35.2.3. Domains ...................................................................................................904

35.2.4. Pseudo-Types ...........................................................................................904

35.2.5. Polymorphic Types ..................................................................................904

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

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

35.4.1. Arguments for SQL Functions................................................................. 906

35.4.2. SQL Functions on Base Types.................................................................907

35.4.3. SQL Functions on Composite Types .......................................................909

35.4.4. SQL Functions with Output Parameters ..................................................912

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

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

35.4.7. SQL Functions as Table Sources .............................................................914

35.4.8. SQL Functions Returning Sets ................................................................915

35.4.9. SQL Functions Returning TABLE ............................................................917

35.4.10. Polymorphic SQL Functions .................................................................918

35.4.11. SQL Functions with Collations..............................................................919

35.5. Function Overloading...........................................................................................920

35.6. Function Volatility Categories.............................................................................. 921

35.7. Procedural Language Functions ...........................................................................922

35.8. Internal Functions.................................................................................................922

35.9. C-Language Functions.......................................................................................... 923

35.9.1. Dynamic Loading.....................................................................................923

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

35.9.3. Version 0 Calling Conventions ................................................................927

35.9.4. Version 1 Calling Conventions ................................................................929

35.9.5. Writing Code............................................................................................932

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

35.9.7. Composite-type Arguments .....................................................................935

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

35.9.9. Returning Sets.......................................................................................... 938

35.9.10. Polymorphic Arguments and Return Types...........................................943

35.9.11. Transform Functions.............................................................................. 944

35.9.12. Shared Memory and LWLocks .............................................................. 945

35.9.13. Using C++ for Extensibility...................................................................945

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

35.10.1. Moving-Aggregate Mode.......................................................................947

35.10.2. Polymorphic and Variadic Aggregates................................................... 949

35.10.3. Ordered-Set Aggregates.........................................................................950

35.10.4. Support Functions for Aggregates .........................................................951

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

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

35.13. Operator Optimization Information....................................................................956

35.13.1. COMMUTATOR..........................................................................................956

35.13.2. NEGATOR ................................................................................................ 957

35.13.3. RESTRICT ..............................................................................................958

剩余3008页未读，继续阅读

dygs

粉丝: 2
资源: 15

PostgreSQL 9.4.4 官方参考手册

PostgreSQL帮助文档 html

PostgresQL8.3中文文档

pytest-postgresql 怎么用

postgresql离线安装

Failed to start postgresql.service: Unit not found.

window PostgreSQL下载和安装教程

postgresql速查表.pdf

PostgreSQL连接navicat

postgresql 循环遍历语句

postgresql主从

postgresql使用教程 电子书

linux环境 postgresql配置

postgresql配置

postgresql設爲中文

postgresql STdintance 计算经纬度距离用的地球半径是多大

postgresql join

如何深入postgresql

postgresql13安装乌班图

postgresql使用linux

centos7离线安装postgresql

最新资源

postgresql使用教程电子书