PostgreSQL 9.5官方文档：DBA与开发者必读

4星 · 超过85%的资源需积分: 10 113 浏览量更新于2024-07-21 1 收藏 7.26MB PDF 举报

PostgreSQL 9.5rc1文档是由全球PostgreSQL开发团队编写的权威参考资料，该版本作为DBA和开发者的首选工具，提供了一系列先进的数据库管理系统功能。PostgreSQL，也被称为Postgres 9.5，起源于1994年的Postgres 95项目，由加州大学的管理者所创立。此版本的版权归属至1996年至2015年的PostgreSQL全球发展集团。文档包含了对PostgreSQL 9.5的重要特性和核心概念的深入解析，包括但不限于SQL语法、数据类型、索引管理、事务处理、并发控制、存储过程、触发器、模式和权限系统等。它不仅涵盖了基本操作，还提供了高级主题，如分区和分片、性能优化、备份恢复策略以及与不同编程语言的接口。在法律条款方面，用户被赋予无费用且无需书面协议的权限来使用、复制、修改和分发该软件及其文档，只要确保在所有副本中保留版权信息、引用许可条款，并包含以下几点： 1. 使用此软件及其文档不会导致加州大学承担任何形式的直接、间接、特殊或附带责任，包括但不限于经济损失，即使加州大学曾被告知此类损害的可能性。 2. 加州大学明确排除了所有默示的保证，包括但不限于商品质量适合特定用途的保证。 PostgreSQL 9.5rc1文档是数据库管理员和开发人员必备的学习和参考资源，它详细记录了PostgreSQL 9.5的最新特性和技术细节，旨在帮助用户充分利用其功能，同时确保遵循严格的版权和法律责任。无论是进行日常维护还是进行深度开发，这份文档都是不可或缺的工具。

IV. Client Interfaces ....................................................................................................................... 674

31. libpq - C Library ...............................................................................................................676

31.1. Database Connection Control Functions.............................................................. 676

31.1.1. Connection Strings...................................................................................682

31.1.1.1. Keyword/Value Connection Strings ............................................ 682

31.1.1.2. Connection URIs......................................................................... 682

31.1.2. Parameter Key Words .............................................................................. 683

31.2. Connection Status Functions ................................................................................687

31.3. Command Execution Functions ...........................................................................692

31.3.1. Main Functions ........................................................................................ 692

31.3.2. Retrieving Query Result Information ...................................................... 699

31.3.3. Retrieving Other Result Information ....................................................... 702

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

31.4. Asynchronous Command Processing................................................................... 706

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

31.6. Canceling Queries in Progress.............................................................................. 711

31.7. The Fast-Path Interface......................................................................................... 712

31.8. Asynchronous Notiﬁcation................................................................................... 713

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

31.9.1. Functions for Sending COPY Data............................................................ 714

31.9.2. Functions for Receiving COPY Data.........................................................715

31.9.3. Obsolete Functions for COPY ................................................................... 716

31.10. Control Functions ...............................................................................................718

31.11. Miscellaneous Functions .................................................................................... 719

31.12. Notice Processing ...............................................................................................721

31.13. Event System...................................................................................................... 722

31.13.1. Event Types............................................................................................ 722

31.13.2. Event Callback Procedure......................................................................724

31.13.3. Event Support Functions........................................................................725

31.13.4. Event Example.......................................................................................726

31.14. Environment Variables ....................................................................................... 728

31.15. The Password File ..............................................................................................730

31.16. The Connection Service File ..............................................................................730

31.17. LDAP Lookup of Connection Parameters.......................................................... 731

31.18. SSL Support........................................................................................................732

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

31.18.2. Client Certiﬁcates...................................................................................733

31.18.3. Protection Provided in Different Modes ................................................733

31.18.4. SSL Client File Usage............................................................................735

31.18.5. SSL Library Initialization ...................................................................... 735

31.19. Behavior in Threaded Programs......................................................................... 736

31.20. Building libpq Programs.....................................................................................737

31.21. Example Programs.............................................................................................. 738

32. Large Objects ....................................................................................................................747

32.1. Introduction ..........................................................................................................747

32.2. Implementation Features ......................................................................................747

32.3. Client Interfaces.................................................................................................... 747

32.3.1. Creating a Large Object...........................................................................748

32.3.2. Importing a Large Object.........................................................................748

32.3.3. Exporting a Large Object.........................................................................749

32.3.4. Opening an Existing Large Object...........................................................749

xvi

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

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

32.3.7. Seeking in a Large Object........................................................................750

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

32.3.9. Truncating a Large Object ....................................................................... 751

32.3.10. Closing a Large Object Descriptor ........................................................751

32.3.11. Removing a Large Object ...................................................................... 751

32.4. Server-side Functions ........................................................................................... 752

32.5. Example Program .................................................................................................753

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

33.1. The Concept.......................................................................................................... 758

33.2. Managing Database Connections .........................................................................758

33.2.1. Connecting to the Database Server..........................................................758

33.2.2. Choosing a Connection............................................................................759

33.2.3. Closing a Connection...............................................................................761

33.3. Running SQL Commands.....................................................................................761

33.3.1. Executing SQL Statements ...................................................................... 761

33.3.2. Using Cursors...........................................................................................762

33.3.3. Managing Transactions............................................................................762

33.3.4. Prepared Statements.................................................................................763

33.4. Using Host Variables ............................................................................................ 764

33.4.1. Overview..................................................................................................764

33.4.2. Declare Sections.......................................................................................764

33.4.3. Retrieving Query Results.........................................................................765

33.4.4. Type Mapping ..........................................................................................766

33.4.4.1. Handling Character Strings .........................................................767

33.4.4.2. Accessing Special Data Types..................................................... 767

33.4.4.2.1. timestamp, date............................................................... 768

33.4.4.2.2. interval ............................................................................768

33.4.4.2.3. numeric, decimal.............................................................769

33.4.4.3. Host Variables with Nonprimitive Types .................................... 770

33.4.4.3.1. Arrays .............................................................................770

33.4.4.3.2. Structures ........................................................................771

33.4.4.3.3. Typedefs..........................................................................772

33.4.4.3.4. Pointers ........................................................................... 773

33.4.5. Handling Nonprimitive SQL Data Types................................................. 773

33.4.5.1. Arrays.......................................................................................... 773

33.4.5.2. Composite Types ......................................................................... 775

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

33.4.6. Indicators..................................................................................................778

33.5. Dynamic SQL....................................................................................................... 778

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

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

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

33.6. pgtypes Library..................................................................................................... 780

33.6.1. The numeric Type ....................................................................................780

33.6.2. The date Type........................................................................................... 783

33.6.3. The timestamp Type................................................................................. 786

33.6.4. The interval Type .....................................................................................790

33.6.5. The decimal Type..................................................................................... 790

33.6.6. errno Values of pgtypeslib .......................................................................791

33.6.7. Special Constants of pgtypeslib...............................................................792

xvii

33.7. Using Descriptor Areas ........................................................................................792

33.7.1. Named SQL Descriptor Areas ................................................................. 792

33.7.2. SQLDA Descriptor Areas ........................................................................794

33.7.2.1. SQLDA Data Structure................................................................ 795

33.7.2.1.1. sqlda_t Structure .............................................................795

33.7.2.1.2. sqlvar_t Structure............................................................796

33.7.2.1.3. struct sqlname Structure .................................................797

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

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

33.7.2.4. A Sample Application Using SQLDA ........................................800

33.8. Error Handling...................................................................................................... 805

33.8.1. Setting Callbacks ..................................................................................... 805

33.8.2. sqlca ......................................................................................................... 807

33.8.3. SQLSTATE vs. SQLCODE........................................................................... 809

33.9. Preprocessor Directives ........................................................................................ 812

33.9.1. Including Files ......................................................................................... 812

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

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

33.10. Processing Embedded SQL Programs................................................................ 814

33.11. Library Functions ...............................................................................................815

33.12. Large Objects...................................................................................................... 816

33.13. C++ Applications ...............................................................................................817

33.13.1. Scope for Host Variables........................................................................ 818

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

33.14. Embedded SQL Commands ............................................................................... 821

ALLOCATE DESCRIPTOR ...............................................................................821

CONNECT...........................................................................................................823

DEALLOCATE DESCRIPTOR ..........................................................................826

DECLARE ...........................................................................................................827

DESCRIBE .......................................................................................................... 829

DISCONNECT .................................................................................................... 830

EXECUTE IMMEDIATE.................................................................................... 832

GET DESCRIPTOR ............................................................................................ 833

OPEN ...................................................................................................................836

PREPARE ............................................................................................................838

SET AUTOCOMMIT..........................................................................................839

SET CONNECTION ........................................................................................... 840

SET DESCRIPTOR.............................................................................................841

TYPE....................................................................................................................843

VAR...................................................................................................................... 845

WHENEVER .......................................................................................................846

33.15. Informix Compatibility Mode ............................................................................848

33.15.1. Additional Types.................................................................................... 848

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

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

33.15.4. Additional Functions..............................................................................852

33.15.5. Additional Constants..............................................................................860

33.16. Internals ..............................................................................................................861

34. The Information Schema...................................................................................................864

34.1. The Schema ..........................................................................................................864

34.2. Data Types ............................................................................................................ 864

34.3. information_schema_catalog_name ...........................................................865

xviii

34.4. administrable_role_authorizations.......................................................865

34.5. applicable_roles............................................................................................865

34.6. attributes.........................................................................................................866

34.7. character_sets................................................................................................ 870

34.8. check_constraint_routine_usage ............................................................. 871

34.9. check_constraints ......................................................................................... 871

34.10. collations.......................................................................................................872

34.11. collation_character_set_applicability ............................................ 872

34.12. column_domain_usage ...................................................................................873

34.13. column_options..............................................................................................873

34.14. column_privileges ....................................................................................... 874

34.15. column_udt_usage..........................................................................................874

34.16. columns ............................................................................................................. 875

34.17. constraint_column_usage .......................................................................... 880

34.18. constraint_table_usage............................................................................. 881

34.19. data_type_privileges................................................................................. 881

34.20. domain_constraints .....................................................................................882

34.21. domain_udt_usage..........................................................................................883

34.22. domains ............................................................................................................. 883

34.23. element_types ................................................................................................886

34.24. enabled_roles ................................................................................................889

34.25. foreign_data_wrapper_options................................................................890

34.26. foreign_data_wrappers...............................................................................890

34.27. foreign_server_options............................................................................. 890

34.28. foreign_servers............................................................................................ 891

34.29. foreign_table_options...............................................................................891

34.30. foreign_tables..............................................................................................892

34.31. key_column_usage..........................................................................................892

34.32. parameters.......................................................................................................893

34.33. referential_constraints .......................................................................... 896

34.34. role_column_grants .....................................................................................897

34.35. role_routine_grants ...................................................................................897

34.36. role_table_grants ....................................................................................... 898

34.37. role_udt_grants............................................................................................ 899

34.38. role_usage_grants ....................................................................................... 900

34.39. routine_privileges .....................................................................................900

34.40. routines...........................................................................................................901

34.41. schemata...........................................................................................................907

34.42. sequences......................................................................................................... 907

34.43. sql_features .................................................................................................. 909

34.44. sql_implementation_info .......................................................................... 909

34.45. sql_languages ................................................................................................910

34.46. sql_packages .................................................................................................. 911

34.47. sql_parts......................................................................................................... 911

34.48. sql_sizing.......................................................................................................912

34.49. sql_sizing_profiles ...................................................................................912

34.50. table_constraints ....................................................................................... 913

34.51. table_privileges..........................................................................................913

34.52. tables ...............................................................................................................914

34.53. transforms.......................................................................................................915

34.54. triggered_update_columns ........................................................................ 916

34.55. triggers...........................................................................................................917

xix

34.56. udt_privileges..............................................................................................918

34.57. usage_privileges..........................................................................................919

34.58. user_defined_types .....................................................................................920

34.59. user_mapping_options................................................................................. 921

34.60. user_mappings ................................................................................................922

34.61. view_column_usage ....................................................................................... 922

34.62. view_routine_usage .....................................................................................923

34.63. view_table_usage..........................................................................................924

34.64. views ................................................................................................................. 924

V. Server Programming ................................................................................................................. 926

35. Extending SQL.................................................................................................................. 928

35.1. How Extensibility Works......................................................................................928

35.2. The PostgreSQL Type System..............................................................................928

35.2.1. Base Types ...............................................................................................928

35.2.2. Composite Types...................................................................................... 928

35.2.3. Domains ...................................................................................................929

35.2.4. Pseudo-Types ...........................................................................................929

35.2.5. Polymorphic Types ..................................................................................929

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

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

35.4.1. Arguments for SQL Functions.................................................................931

35.4.2. SQL Functions on Base Types................................................................. 932

35.4.3. SQL Functions on Composite Types .......................................................934

35.4.4. SQL Functions with Output Parameters .................................................. 937

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

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

35.4.7. SQL Functions as Table Sources .............................................................939

35.4.8. SQL Functions Returning Sets ................................................................940

35.4.9. SQL Functions Returning TABLE ............................................................942

35.4.10. Polymorphic SQL Functions .................................................................943

35.4.11. SQL Functions with Collations..............................................................944

35.5. Function Overloading........................................................................................... 945

35.6. Function Volatility Categories..............................................................................946

35.7. Procedural Language Functions ...........................................................................947

35.8. Internal Functions................................................................................................. 947

35.9. C-Language Functions.......................................................................................... 948

35.9.1. Dynamic Loading.....................................................................................948

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

35.9.3. Version 0 Calling Conventions ................................................................952

35.9.4. Version 1 Calling Conventions ................................................................954

35.9.5. Writing Code............................................................................................957

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

35.9.7. Composite-type Arguments ..................................................................... 960

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

35.9.9. Returning Sets..........................................................................................963

35.9.10. Polymorphic Arguments and Return Types........................................... 968

35.9.11. Transform Functions..............................................................................969

35.9.12. Shared Memory and LWLocks..............................................................970

35.9.13. Using C++ for Extensibility................................................................... 970

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

35.10.1. Moving-Aggregate Mode.......................................................................972

剩余3136页未读，继续阅读

baidu_33842692

粉丝: 0
资源: 1

PostgreSQL 9.5官方文档：DBA与开发者必读

PostgreSQL9.5安装

postgresql9.5软件+postgis2.2扩展下载

postgresql-9.0.5-1-windows_x64.exe

PostgreSQL 9.5文档pdf版

postgresql9.5与postgis2.2扩展包下载指南

PostgreSQL 9.5安全配置全指南：防范黑客攻击

PostgreSQL 9.5到13.3版本升级及主备配置指南

Zabbix 3.0与PostgreSQL 9.5、Nginx的集成安装教程

CentOS 7源码编译PostgreSQL 9.5教程：从下载到安装

在FreeNAS系统中，如何通过Jails部署并优化PostgreSQL 9.5数据库服务器？请提供详细步骤和性能调优建议。

最新资源