PostgreSQL 9.2官方英文手册指南

需积分: 10 65 浏览量更新于2024-07-26 收藏 6.05MB PDF 举报

PostgreSQL 9.2英文手册是由全球PostgreSQL开发团队编写的官方文档，它涵盖了PostgreSQL 9.2版本的所有重要特性、系统架构、安装、配置、数据模型、SQL语言、存储过程、事务处理、并发控制、索引、视图、数据库管理、备份恢复以及高级主题如扩展和安全性等。此手册对于深入理解并操作PostgreSQL 9.2数据库管理系统至关重要。在9.2版本中，PostgreSQL强调了其作为开源关系型数据库系统的强大功能。它采用对象-关系模型，支持复杂查询和高级数据类型，包括数组和JSON支持。用户可以利用该手册学习如何创建和管理表，处理各种数据类型，以及如何编写高效的SQL语句来满足特定业务需求。在安全性方面，手册提供了详细的权限管理和加密策略，帮助用户保护敏感数据不被未经授权的访问。同时，9.2版引入了新的安全特性，如角色和SSL/TLS连接，以增强系统的安全性。关于事务处理，PostgreSQL 9.2提供了ACID（原子性、一致性、隔离性、持久性）事务保障，确保数据的一致性和完整性。它还支持并发控制技术，如MVCC（多版本并发控制），以解决多用户环境中的并发问题。此外，手册还涵盖了如何进行有效的数据库备份与恢复，这对于保持数据的可用性和灾难恢复非常重要。用户可以了解如何设置定期备份策略，并在发生故障时快速恢复到稳定状态。值得注意的是，虽然PostgreSQL 9.2具有广泛的功能，但手册也强调了法律责任声明，指出软件的使用可能带来的风险和责任。用户在使用过程中应遵循版权协议，理解和接受其使用限制，特别是关于损害赔偿的免责声明。 PostgreSQL 9.2英文手册是一份详尽的资源，无论是对初学者还是经验丰富的开发者，都是深入了解和掌握PostgreSQL 9.2数据库系统不可或缺的参考资料。通过阅读和实践手册中的内容，用户能够熟练地设计、部署和维护高效、安全的数据库应用。

31.15. The Password File ..............................................................................................660

31.16. The Connection Service File ..............................................................................661

31.17. LDAP Lookup of Connection Parameters..........................................................661

31.18. SSL Support........................................................................................................662

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

31.18.2. Client Certiﬁcates...................................................................................663

31.18.3. Protection Provided in Different Modes ................................................664

31.18.4. SSL Client File Usage............................................................................665

31.18.5. SSL Library Initialization ......................................................................666

31.19. Behavior in Threaded Programs......................................................................... 666

31.20. Building libpq Programs.....................................................................................667

31.21. Example Programs.............................................................................................. 668

32. Large Objects ....................................................................................................................677

32.1. Introduction ..........................................................................................................677

32.2. Implementation Features ......................................................................................677

32.3. Client Interfaces.................................................................................................... 677

32.3.1. Creating a Large Object...........................................................................677

32.3.2. Importing a Large Object......................................................................... 678

32.3.3. Exporting a Large Object......................................................................... 678

32.3.4. Opening an Existing Large Object...........................................................679

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

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

32.3.7. Seeking in a Large Object........................................................................ 680

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

32.3.9. Truncating a Large Object ....................................................................... 680

32.3.10. Closing a Large Object Descriptor ........................................................680

32.3.11. Removing a Large Object ......................................................................680

32.4. Server-side Functions ...........................................................................................681

32.5. Example Program ................................................................................................. 681

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

33.1. The Concept.......................................................................................................... 687

33.2. Managing Database Connections .........................................................................687

33.2.1. Connecting to the Database Server..........................................................687

33.2.2. Choosing a Connection............................................................................688

33.2.3. Closing a Connection...............................................................................690

33.3. Running SQL Commands.....................................................................................690

33.3.1. Executing SQL Statements ......................................................................690

33.3.2. Using Cursors...........................................................................................691

33.3.3. Managing Transactions............................................................................691

33.3.4. Prepared Statements................................................................................. 692

33.4. Using Host Variables ............................................................................................693

33.4.1. Overview..................................................................................................693

33.4.2. Declare Sections.......................................................................................693

33.4.3. Retrieving Query Results.........................................................................694

33.4.4. Type Mapping ..........................................................................................695

33.4.4.1. Handling Character Strings ......................................................... 696

33.4.4.2. Accessing Special Data Types..................................................... 697

33.4.4.2.1. timestamp, date...............................................................697

33.4.4.2.2. interval ............................................................................ 697

33.4.4.2.3. numeric, decimal.............................................................698

33.4.4.3. Host Variables with Nonprimitive Types .................................... 699

33.4.4.3.1. Arrays .............................................................................699

xvi

33.4.4.3.2. Structures ........................................................................ 700

33.4.4.3.3. Typedefs..........................................................................702

33.4.4.3.4. Pointers ...........................................................................702

33.4.5. Handling Nonprimitive SQL Data Types.................................................702

33.4.5.1. Arrays.......................................................................................... 702

33.4.5.2. Composite Types .........................................................................704

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

33.4.6. Indicators..................................................................................................707

33.5. Dynamic SQL....................................................................................................... 707

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

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

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

33.6. pgtypes Library.....................................................................................................709

33.6.1. The numeric Type ....................................................................................709

33.6.2. The date Type...........................................................................................712

33.6.3. The timestamp Type.................................................................................715

33.6.4. The interval Type .....................................................................................719

33.6.5. The decimal Type.....................................................................................720

33.6.6. errno Values of pgtypeslib .......................................................................720

33.6.7. Special Constants of pgtypeslib...............................................................721

33.7. Using Descriptor Areas ........................................................................................721

33.7.1. Named SQL Descriptor Areas .................................................................722

33.7.2. SQLDA Descriptor Areas ........................................................................724

33.7.2.1. SQLDA Data Structure................................................................724

33.7.2.1.1. sqlda_t Structure ............................................................. 725

33.7.2.1.2. sqlvar_t Structure............................................................ 725

33.7.2.1.3. struct sqlname Structure .................................................726

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

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

33.7.2.4. A Sample Application Using SQLDA ........................................729

33.8. Error Handling...................................................................................................... 734

33.8.1. Setting Callbacks ..................................................................................... 735

33.8.2. sqlca ......................................................................................................... 736

33.8.3. SQLSTATE vs. SQLCODE........................................................................... 738

33.9. Preprocessor Directives ........................................................................................ 741

33.9.1. Including Files ......................................................................................... 741

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

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

33.10. Processing Embedded SQL Programs................................................................ 743

33.11. Library Functions ...............................................................................................744

33.12. Large Objects...................................................................................................... 745

33.13. C++ Applications ...............................................................................................747

33.13.1. Scope for Host Variables........................................................................747

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

33.14. Embedded SQL Commands ...............................................................................750

ALLOCATE DESCRIPTOR ...............................................................................750

CONNECT...........................................................................................................752

DEALLOCATE DESCRIPTOR .......................................................................... 755

DECLARE ...........................................................................................................756

DESCRIBE ..........................................................................................................758

DISCONNECT ....................................................................................................759

EXECUTE IMMEDIATE.................................................................................... 761

xvii

GET DESCRIPTOR ............................................................................................ 762

OPEN ...................................................................................................................765

PREPARE ............................................................................................................767

SET AUTOCOMMIT .......................................................................................... 768

SET CONNECTION ........................................................................................... 769

SET DESCRIPTOR.............................................................................................770

TYPE....................................................................................................................772

VAR...................................................................................................................... 774

WHENEVER .......................................................................................................775

33.15. Informix Compatibility Mode ............................................................................777

33.15.1. Additional Types.................................................................................... 777

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

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

33.15.4. Additional Functions..............................................................................781

33.15.5. Additional Constants..............................................................................789

33.16. Internals .............................................................................................................. 790

34. The Information Schema...................................................................................................793

34.1. The Schema ..........................................................................................................793

34.2. Data Types ............................................................................................................793

34.3. information_schema_catalog_name ...........................................................794

34.4. administrable_role_authorizations.......................................................794

34.5. applicable_roles............................................................................................794

34.6. attributes.........................................................................................................795

34.7. character_sets................................................................................................799

34.8. check_constraint_routine_usage .............................................................800

34.9. check_constraints ......................................................................................... 800

34.10. collations.......................................................................................................801

34.11. collation_character_set_applicability ............................................801

34.12. column_domain_usage ...................................................................................802

34.13. column_options..............................................................................................802

34.14. column_privileges ....................................................................................... 803

34.15. column_udt_usage..........................................................................................803

34.16. columns .............................................................................................................804

34.17. constraint_column_usage .......................................................................... 809

34.18. constraint_table_usage............................................................................. 810

34.19. data_type_privileges................................................................................. 810

34.20. domain_constraints .....................................................................................811

34.21. domain_udt_usage..........................................................................................812

34.22. domains .............................................................................................................812

34.23. element_types ................................................................................................ 815

34.24. enabled_roles ................................................................................................ 818

34.25. foreign_data_wrapper_options................................................................819

34.26. foreign_data_wrappers............................................................................... 819

34.27. foreign_server_options............................................................................. 819

34.28. foreign_servers............................................................................................820

34.29. foreign_table_options............................................................................... 820

34.30. foreign_tables..............................................................................................821

34.31. key_column_usage..........................................................................................821

34.32. parameters.......................................................................................................822

34.33. referential_constraints .......................................................................... 825

34.34. role_column_grants .....................................................................................826

34.35. role_routine_grants ...................................................................................826

xviii

34.36. role_table_grants ....................................................................................... 827

34.37. role_udt_grants............................................................................................828

34.38. role_usage_grants ....................................................................................... 828

34.39. routine_privileges .....................................................................................829

34.40. routines...........................................................................................................830

34.41. schemata...........................................................................................................836

34.42. sequences.........................................................................................................836

34.43. sql_features ..................................................................................................837

34.44. sql_implementation_info .......................................................................... 838

34.45. sql_languages ................................................................................................ 839

34.46. sql_packages ..................................................................................................839

34.47. sql_parts.........................................................................................................840

34.48. sql_sizing.......................................................................................................840

34.49. sql_sizing_profiles ...................................................................................841

34.50. table_constraints ....................................................................................... 841

34.51. table_privileges..........................................................................................842

34.52. tables ...............................................................................................................843

34.53. triggered_update_columns ........................................................................ 844

34.54. triggers...........................................................................................................844

34.55. udt_privileges..............................................................................................846

34.56. usage_privileges..........................................................................................847

34.57. user_defined_types .....................................................................................847

34.58. user_mapping_options................................................................................. 849

34.59. user_mappings ................................................................................................ 850

34.60. view_column_usage ....................................................................................... 850

34.61. view_routine_usage .....................................................................................851

34.62. view_table_usage..........................................................................................851

34.63. views .................................................................................................................852

V. Server Programming ................................................................................................................. 854

35. Extending SQL..................................................................................................................856

35.1. How Extensibility Works......................................................................................856

35.2. The PostgreSQL Type System.............................................................................. 856

35.2.1. Base Types ...............................................................................................856

35.2.2. Composite Types......................................................................................856

35.2.3. Domains ...................................................................................................857

35.2.4. Pseudo-Types ...........................................................................................857

35.2.5. Polymorphic Types ..................................................................................857

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

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

35.4.1. Arguments for SQL Functions................................................................. 859

35.4.2. SQL Functions on Base Types................................................................. 860

35.4.3. SQL Functions on Composite Types .......................................................861

35.4.4. SQL Functions with Output Parameters ..................................................864

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

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

35.4.7. SQL Functions as Table Sources .............................................................867

35.4.8. SQL Functions Returning Sets ................................................................868

35.4.9. SQL Functions Returning TABLE ............................................................869

35.4.10. Polymorphic SQL Functions .................................................................870

35.4.11. SQL Functions with Collations..............................................................871

35.5. Function Overloading........................................................................................... 872

xix

35.6. Function Volatility Categories.............................................................................. 873

35.7. Procedural Language Functions ...........................................................................874

35.8. Internal Functions................................................................................................. 874

35.9. C-Language Functions.......................................................................................... 875

35.9.1. Dynamic Loading.....................................................................................875

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

35.9.3. Version 0 Calling Conventions ................................................................879

35.9.4. Version 1 Calling Conventions ................................................................881

35.9.5. Writing Code............................................................................................884

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

35.9.7. Composite-type Arguments .....................................................................887

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

35.9.9. Returning Sets..........................................................................................890

35.9.10. Polymorphic Arguments and Return Types........................................... 895

35.9.11. Transform Functions..............................................................................896

35.9.12. Shared Memory and LWLocks .............................................................. 897

35.9.13. Using C++ for Extensibility...................................................................897

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

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

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

35.13. Operator Optimization Information....................................................................904

35.13.1. COMMUTATOR.......................................................................................... 904

35.13.2. NEGATOR ................................................................................................ 905

35.13.3. RESTRICT ..............................................................................................906

35.13.4. JOIN.......................................................................................................906

35.13.5. HASHES...................................................................................................907

35.13.6. MERGES...................................................................................................908

35.14. Interfacing Extensions To Indexes......................................................................908

35.14.1. Index Methods and Operator Classes ....................................................909

35.14.2. Index Method Strategies ........................................................................ 909

35.14.3. Index Method Support Routines ............................................................911

35.14.4. An Example ........................................................................................... 913

35.14.5. Operator Classes and Operator Families................................................916

35.14.6. System Dependencies on Operator Classes ...........................................918

35.14.7. Ordering Operators ................................................................................ 919

35.14.8. Special Features of Operator Classes.....................................................920

35.15. Packaging Related Objects into an Extension.................................................... 920

35.15.1. Extension Files....................................................................................... 921

35.15.2. Extension Relocatability ........................................................................923

35.15.3. Extension Conﬁguration Tables............................................................. 924

35.15.4. Extension Updates ................................................................................. 924

35.15.5. Extension Example ................................................................................ 925

35.16. Extension Building Infrastructure ......................................................................926

36. Triggers ............................................................................................................................. 930

36.1. Overview of Trigger Behavior..............................................................................930

36.2. Visibility of Data Changes....................................................................................932

36.3. Writing Trigger Functions in C ............................................................................933

36.4. A Complete Trigger Example............................................................................... 935

37. The Rule System ...............................................................................................................939

37.1. The Query Tree.....................................................................................................939

37.2. Views and the Rule System ..................................................................................941

37.2.1. How SELECT Rules Work........................................................................ 941

剩余2670页未读，继续阅读

闪电侠风行

粉丝: 74
资源: 57

PostgreSQL 9.2官方英文手册指南

PostgreSQL中文手册9.2

postgresql-9.2-官方手册

edb安装目录中的详细信息

[PostgreSQL：Up.and.Running(2012.7)].Regina.Obe.文字版

MySQL中文参考手册

spring security 参考手册中文版

MySQL中文参考手册.chm

Pgsql 9.2.24版本安装包及dll下载指南

PostgreSQL 9.2.1 官方文档详解

JSON数据分析实战手册：SQL和NoSQL工具数据挖掘

最新资源