没有合适的资源?快使用搜索试试~ 我知道了~
首页PostgreSQL 9.0: 功能强大的开源数据库系统
PostgreSQL 9.0: 功能强大的开源数据库系统
需积分: 10 2 下载量 157 浏览量
更新于2024-07-30
收藏 7.2MB PDF 举报
"PostgreSQL 9.0.1 文档"
PostgreSQL 9.0.1 是 PostgreSQL 数据库管理系统的一个版本,它是一个强大的开源对象-关系型数据库系统,以其丰富的特性和高度的复杂性而闻名。这个系统源自伯克利(BSD)的数据库研究计划,发展至今已经成为全球最重要的开源数据库项目之一,拥有庞大的用户群体。PostgreSQL 9.0.1 版本在发布时可能引入了新的特性和改进,以增强其性能、稳定性和安全性。
在功能上,PostgreSQL 支持一系列高级特性,如事务处理,这意味着它能够保证数据的一致性和完整性。它还支持子查询,允许在单个 SQL 查询中嵌套其他查询,增强了查询的灵活性和复杂性。此外,PostgreSQL 实现了多版本并行控制(MVCC),这是一种并发控制机制,使得多个用户可以在同一时间对数据库进行读写操作而不相互冲突。数据完整性检查是另一个关键特性,确保数据的准确性和一致性,符合ACID(原子性、一致性、隔离性、持久性)原则。
PostgreSQL 可以在多种操作系统上运行,包括 Linux、Unix 及 Windows,这体现了它的跨平台兼容性。由于其开源性质,PostgreSQL 的源代码可供全球的开发者查看、修改和分发,推动了社区的持续创新和改进。
提供的文档 "PostgreSQL 9.0.1 Documentation" 由 PostgreSQL 全球开发团队编著,旨在帮助用户理解和使用这个数据库系统。文档涵盖了安装、配置、管理、SQL 语法、性能优化以及故障排查等多个方面,是使用和维护 PostgreSQL 系统的重要参考资料。
根据版权信息,PostgreSQL 的原始版权归属于加州大学,并且遵循特定的许可协议,允许无费用地用于个人和商业目的,但要求在复制或分发时保留版权通知。同时,加州大学不承担因使用该软件及其文档导致的任何直接、间接、特殊、偶然或后果性损害的责任,并明确声明不提供任何保修。
PostgreSQL 9.0.1 是一个强大且灵活的数据库解决方案,适合各种规模的企业和项目使用。通过详细的文档,用户可以学习如何充分利用其功能,确保数据的安全性和可靠性。
32. Large Objects ...........................................................................................................................656
32.1. Introduction .................................................................................................................656
32.2. Implementation Features .............................................................................................656
32.3. Client Interfaces...........................................................................................................656
32.3.1. Creating a Large Object..................................................................................656
32.3.2. Importing a Large Object................................................................................657
32.3.3. Exporting a Large Object................................................................................658
32.3.4. Opening an Existing Large Object..................................................................658
32.3.5. Writing Data to a Large Object.......................................................................658
32.3.6. Reading Data from a Large Object .................................................................659
32.3.7. Seeking in a Large Object...............................................................................659
32.3.8. Obtaining the Seek Position of a Large Object...............................................659
32.3.9. Truncating a Large Object ..............................................................................659
32.3.10. Closing a Large Object Descriptor ...............................................................660
32.3.11. Removing a Large Object .............................................................................660
32.4. Server-Side Functions..................................................................................................660
32.5. Example Program ........................................................................................................661
33. ECPG - Embedded SQL in C...................................................................................................667
33.1. The Concept.................................................................................................................667
33.2. Connecting to the Database Server..............................................................................667
33.3. Closing a Connection ..................................................................................................668
33.4. Running SQL Commands............................................................................................669
33.5. Choosing a Connection................................................................................................670
33.6. Using Host Variables ...................................................................................................670
33.6.1. Overview.........................................................................................................671
33.6.2. Declare Sections..............................................................................................671
33.6.3. Different types of host variables .....................................................................672
33.6.4. SELECT INTO and FETCH INTO ...................................................................673
33.6.5. Indicators.........................................................................................................673
33.7. Dynamic SQL..............................................................................................................674
33.8. pgtypes library .............................................................................................................675
33.8.1. The numeric type ............................................................................................675
33.8.2. The date type...................................................................................................678
33.8.3. The timestamp type.........................................................................................682
33.8.4. The interval type .............................................................................................686
33.8.5. The decimal type.............................................................................................686
33.8.6. errno values of pgtypeslib...............................................................................687
33.8.7. Special constants of pgtypeslib.......................................................................687
33.9. Using Descriptor Areas ...............................................................................................688
33.9.1. Named SQL Descriptor Areas ........................................................................688
33.9.2. SQLDA Descriptor Areas ...............................................................................690
33.10. Informix compatibility mode.....................................................................................693
33.10.1. Additional types............................................................................................693
33.10.2. Additional/missing embedded SQL statements............................................694
33.10.3. Informix-compatible SQLDA Descriptor Areas...........................................694
33.10.4. Additional functions......................................................................................697
33.10.5. Additional constants......................................................................................706
33.11. Error Handling...........................................................................................................707
xvi
33.11.1. Setting Callbacks ..........................................................................................707
33.11.2. sqlca ..............................................................................................................709
33.11.3. SQLSTATE vs SQLCODE.................................................................................710
33.12. Preprocessor directives ..............................................................................................713
33.12.1. Including files................................................................................................713
33.12.2. The #define and #undef directives ................................................................713
33.12.3. ifdef, ifndef, else, elif, and endif directives ..................................................714
33.13. Processing Embedded SQL Programs.......................................................................715
33.14. Library Functions ......................................................................................................716
33.15. Internals .....................................................................................................................716
34. The Information Schema..........................................................................................................719
34.1. The Schema .................................................................................................................719
34.2. Data Types ...................................................................................................................719
34.3. information_schema_catalog_name ..................................................................720
34.4. administrable_role_authorizations..............................................................720
34.5. applicable_roles...................................................................................................720
34.6. attributes................................................................................................................721
34.7. check_constraint_routine_usage ....................................................................724
34.8. check_constraints ................................................................................................724
34.9. column_domain_usage ............................................................................................725
34.10. column_privileges ..............................................................................................725
34.11. column_udt_usage.................................................................................................726
34.12. columns ....................................................................................................................727
34.13. constraint_column_usage .................................................................................731
34.14. constraint_table_usage....................................................................................732
34.15. data_type_privileges........................................................................................733
34.16. domain_constraints ............................................................................................734
34.17. domain_udt_usage.................................................................................................734
34.18. domains ....................................................................................................................735
34.19. element_types .......................................................................................................738
34.20. enabled_roles .......................................................................................................741
34.21. foreign_data_wrapper_options.......................................................................741
34.22. foreign_data_wrappers......................................................................................741
34.23. foreign_server_options....................................................................................742
34.24. foreign_servers...................................................................................................742
34.25. key_column_usage.................................................................................................743
34.26. parameters..............................................................................................................744
34.27. referential_constraints .................................................................................747
34.28. role_column_grants ............................................................................................748
34.29. role_routine_grants ..........................................................................................748
34.30. role_table_grants ..............................................................................................749
34.31. role_usage_grants ..............................................................................................750
34.32. routine_privileges ............................................................................................751
34.33. routines..................................................................................................................751
34.34. schemata..................................................................................................................757
34.35. sequences................................................................................................................758
34.36. sql_features .........................................................................................................759
34.37. sql_implementation_info .................................................................................760
xvii
34.38. sql_languages .......................................................................................................761
34.39. sql_packages .........................................................................................................761
34.40. sql_parts................................................................................................................762
34.41. sql_sizing..............................................................................................................762
34.42. sql_sizing_profiles ..........................................................................................763
34.43. table_constraints ..............................................................................................763
34.44. table_privileges.................................................................................................764
34.45. tables ......................................................................................................................765
34.46. triggered_update_columns ...............................................................................766
34.47. triggers..................................................................................................................767
34.48. usage_privileges.................................................................................................768
34.49. user_mapping_options........................................................................................769
34.50. user_mappings .......................................................................................................770
34.51. view_column_usage ..............................................................................................770
34.52. view_routine_usage ............................................................................................771
34.53. view_table_usage.................................................................................................772
34.54. views ........................................................................................................................772
V. Server Programming ........................................................................................................................774
35. Extending SQL.........................................................................................................................776
35.1. How Extensibility Works.............................................................................................776
35.2. The PostgreSQL Type System.....................................................................................776
35.2.1. Base Types ......................................................................................................776
35.2.2. Composite Types.............................................................................................777
35.2.3. Domains ..........................................................................................................777
35.2.4. Pseudo-Types ..................................................................................................777
35.2.5. Polymorphic Types .........................................................................................777
35.3. User-Defined Functions...............................................................................................778
35.4. Query Language (SQL) Functions ..............................................................................778
35.4.1. SQL Functions on Base Types ........................................................................779
35.4.2. SQL Functions on Composite Types ..............................................................781
35.4.3. SQL Functions with Parameter Names...........................................................784
35.4.4. SQL Functions with Output Parameters .........................................................784
35.4.5. SQL Functions with Variable Numbers of Arguments ...................................786
35.4.6. SQL Functions with Default Values for Arguments .......................................786
35.4.7. SQL Functions as Table Sources ....................................................................787
35.4.8. SQL Functions Returning Sets .......................................................................788
35.4.9. SQL Functions Returning TABLE ...................................................................790
35.4.10. Polymorphic SQL Functions ........................................................................790
35.5. Function Overloading..................................................................................................792
35.6. Function Volatility Categories.....................................................................................793
35.7. Procedural Language Functions ..................................................................................794
35.8. Internal Functions........................................................................................................794
35.9. C-Language Functions.................................................................................................795
35.9.1. Dynamic Loading............................................................................................795
35.9.2. Base Types in C-Language Functions.............................................................796
35.9.3. Version 0 Calling Conventions .......................................................................799
35.9.4. Version 1 Calling Conventions .......................................................................801
xviii
35.9.5. Writing Code...................................................................................................804
35.9.6. Compiling and Linking Dynamically-Loaded Functions ...............................805
35.9.7. Extension Building Infrastructure...................................................................807
35.9.8. Composite-Type Arguments ...........................................................................809
35.9.9. Returning Rows (Composite Types) ...............................................................811
35.9.10. Returning Sets...............................................................................................813
35.9.11. Polymorphic Arguments and Return Types ..................................................818
35.9.12. Shared Memory and LWLocks .....................................................................819
35.10. User-Defined Aggregates ..........................................................................................820
35.11. User-Defined Types ...................................................................................................822
35.12. User-Defined Operators.............................................................................................826
35.13. Operator Optimization Information...........................................................................827
35.13.1. COMMUTATOR.................................................................................................827
35.13.2. NEGATOR .......................................................................................................828
35.13.3. RESTRICT .....................................................................................................828
35.13.4. JOIN..............................................................................................................829
35.13.5. HASHES..........................................................................................................830
35.13.6. MERGES..........................................................................................................831
35.14. Interfacing Extensions To Indexes.............................................................................832
35.14.1. Index Methods and Operator Classes ...........................................................832
35.14.2. Index Method Strategies ...............................................................................832
35.14.3. Index Method Support Routines ...................................................................834
35.14.4. An Example ..................................................................................................836
35.14.5. Operator Classes and Operator Families.......................................................838
35.14.6. System Dependencies on Operator Classes ..................................................841
35.14.7. Special Features of Operator Classes............................................................841
35.15. Using C++ for Extensibility ......................................................................................842
36. Triggers ....................................................................................................................................844
36.1. Overview of Trigger Behavior.....................................................................................844
36.2. Visibility of Data Changes...........................................................................................846
36.3. Writing Trigger Functions in C ...................................................................................846
36.4. A Complete Trigger Example......................................................................................849
37. The Rule System ......................................................................................................................853
37.1. The Query Tree............................................................................................................853
37.2. Views and the Rule System .........................................................................................855
37.2.1. How SELECT Rules Work ...............................................................................855
37.2.2. View Rules in Non-SELECT Statements .........................................................860
37.2.3. The Power of Views in PostgreSQL ...............................................................861
37.2.4. Updating a View..............................................................................................861
37.3. Rules on INSERT, UPDATE, and DELETE ....................................................................862
37.3.1. How Update Rules Work ................................................................................862
37.3.1.1. A First Rule Step by Step...................................................................863
37.3.2. Cooperation with Views..................................................................................866
37.4. Rules and Privileges ....................................................................................................873
37.5. Rules and Command Status.........................................................................................874
37.6. Rules versus Triggers ..................................................................................................874
38. Procedural Languages ..............................................................................................................877
38.1. Installing Procedural Languages .................................................................................877
xix
39. PL/pgSQL - SQL Procedural Language ..................................................................................880
39.1. Overview .....................................................................................................................880
39.1.1. Advantages of Using PL/pgSQL ....................................................................880
39.1.2. Supported Argument and Result Data Types..................................................881
39.2. Structure of PL/pgSQL................................................................................................881
39.3. Declarations.................................................................................................................883
39.3.1. Declaring Function Parameters.......................................................................883
39.3.2. ALIAS..............................................................................................................886
39.3.3. Copying Types ................................................................................................886
39.3.4. Row Types.......................................................................................................887
39.3.5. Record Types ..................................................................................................887
39.4. Expressions..................................................................................................................888
39.5. Basic Statements..........................................................................................................888
39.5.1. Assignment .....................................................................................................888
39.5.2. Executing a Command With No Result ..........................................................889
39.5.3. Executing a Query with a Single-Row Result ................................................890
39.5.4. Executing Dynamic Commands .....................................................................891
39.5.5. Obtaining the Result Status.............................................................................894
39.5.6. Doing Nothing At All .....................................................................................895
39.6. Control Structures........................................................................................................895
39.6.1. Returning From a Function.............................................................................895
39.6.1.1. RETURN...............................................................................................896
39.6.1.2. RETURN NEXT and RETURN QUERY ..................................................896
39.6.2. Conditionals ....................................................................................................897
39.6.2.1. IF-THEN.............................................................................................898
39.6.2.2. IF-THEN-ELSE ..................................................................................898
39.6.2.3. IF-THEN-ELSIF................................................................................899
39.6.2.4. Simple CASE.......................................................................................900
39.6.2.5. Searched CASE....................................................................................900
39.6.3. Simple Loops ..................................................................................................901
39.6.3.1. LOOP ...................................................................................................901
39.6.3.2. EXIT ...................................................................................................901
39.6.3.3. CONTINUE...........................................................................................902
39.6.3.4. WHILE .................................................................................................902
39.6.3.5. FOR (integer variant)...........................................................................903
39.6.4. Looping Through Query Results ....................................................................904
39.6.5. Trapping Errors...............................................................................................905
39.7. Cursors.........................................................................................................................907
39.7.1. Declaring Cursor Variables .............................................................................907
39.7.2. Opening Cursors .............................................................................................908
39.7.2.1. OPEN FOR query ...............................................................................908
39.7.2.2. OPEN FOR EXECUTE .........................................................................908
39.7.2.3. Opening a Bound Cursor....................................................................909
39.7.3. Using Cursors..................................................................................................909
39.7.3.1. FETCH .................................................................................................909
39.7.3.2. MOVE ...................................................................................................910
39.7.3.3. UPDATE/DELETE WHERE CURRENT OF ...........................................910
39.7.3.4. CLOSE .................................................................................................911
xx
剩余2434页未读,继续阅读
2022-03-30 上传
2011-08-07 上传
2022-03-30 上传
2024-01-22 上传
2024-01-12 上传
2024-05-27 上传
2023-05-16 上传
2023-12-01 上传
2024-05-31 上传
2023-06-09 上传
younideshijie
- 粉丝: 0
- 资源: 20
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
最新资源
- Ansys Comsol实现力磁耦合仿真及其在电磁无损检测中的应用
- 西门子数控系统调试与配置实战案例教程
- ELM多输出拟合预测模型:简易Matlab实现指南
- 一维光子晶体的Comsol能带拓扑分析研究
- Borland-5技术资料压缩包分享
- Borland 6 技术资料分享包
- UE5压缩包处理技巧与D文件介绍
- 机器学习笔记:深入探讨中心极限定理
- ProE使用技巧及文件管理方法分享
- 增量式百度图片爬虫程序修复版发布
- Emlog屏蔽用户IP黑名单插件:自定义跳转与评论限制
- 安装Prometheus 2.2.1所需镜像及配置指南
- WinRARChan主题包:个性化你的压缩软件
- Neo4j关系数据映射转换测试样例集
- 安装heapster-grafana-amd64-v5-0-4所需镜像介绍
- DVB-C语言深度解析TS流
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功