Statistics
50
Views
0
Downloads
0
Donations
Support
Share
Uploader

高宏飞

Shared on 2025-11-21

AuthorBaji Shaik, Dinesh Kumar Chemuduru

Learn the fundamentals of PL/PGSQL, the programming language of PostgreSQL which is most robust Open Source Relational Database. This book provides practical insights into developing database code objects such as functions and procedures, with a focus on effectively handling strings, numbers, and arrays to achieve desired outcomes, and transaction management. The unique approach to handling Triggers in PostgreSQL ensures that both functionality and performance are maintained without compromise. You'll gain proficiency in writing inline/anonymous server-side code within the limitations, along with learning essential debugging and profiling techniques. Additionally, the book delves into statistical analysis of PL/PGSQL code and offers valuable knowledge on managing exceptions while writing code blocks. Finally, you'll explore the installation and configuration of extensions to enhance the performance of stored procedures and functions. What You'll Learn Understand the PL/PGSQL concepts Learn to debug, profile, and optimize PL/PGSQL code Study linting PL/PGSQL code Review transaction management within PL/PGSQL code Work with developer friendly features like operators, casts, and aggregators Who Is This Book For App developers, database migration consultants, and database administrators.

Tags
No tags
ISBN: 148429839X
Publish Year: 2023
Language: 英文
Pages: 325
File Format: PDF
File Size: 4.6 MB
Support Statistics
¥.00 · 0times
Text Preview (First 20 pages)
Registered users can read the full content for free

Register as a Gaohf Library member to read the complete e-book online for free and enjoy a better reading experience.

DAT ABA SE SQL STRUCTURED QUERY LANGUAGE DAT ABA SE DAT Procedural Programming with PostgreSQL PL/pgSQL Design Complex Database-Centric Applications with PL/pgSQL — Baji Shaik Dinesh Kumar Chemuduru
Procedural Programming with PostgreSQL PL/pgSQL: Design Complex Database- Centric Applications with PL/pgSQL ISBN-13 (pbk): 978-1-4842-9839-8 ISBN-13 (electronic): 978-1-4842-9840-4 https://doi.org/10.1007/978-1-4842-9840-4 Copyright © 2023 by Baji Shaik and Dinesh Kumar Chemuduru This work is subject to copyright. All rights are reserved by the Publisher, whether the whole or part of the material is concerned, specifically the rights of translation, reprinting, reuse of illustrations, recitation, broadcasting, reproduction on microfilms or in any other physical way, and transmission or information storage and retrieval, electronic adaptation, computer software, or by similar or dissimilar methodology now known or hereafter developed. Trademarked names, logos, and images may appear in this book. Rather than use a trademark symbol with every occurrence of a trademarked name, logo, or image we use the names, logos, and images only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark. The use in this publication of trade names, trademarks, service marks, and similar terms, even if they are not identified as such, is not to be taken as an expression of opinion as to whether or not they are subject to proprietary rights. While the advice and information in this book are believed to be true and accurate at the date of publication, neither the authors nor the editors nor the publisher can accept any legal responsibility for any errors or omissions that may be made. The publisher makes no warranty, express or implied, with respect to the material contained herein. Managing Director, Apress Media LLC: Welmoed Spahr Acquisitions Editor: Divya Modi Development Editor: James Markham Cover designed by eStudioCalamar Cover image designed by Freepik (www.freepik.com) Distributed to the book trade worldwide by Apress Media, LLC, 1 New York Plaza, New York, NY 10004, U.S.A. Phone 1-800-SPRINGER, fax (201) 348-4505, e-mail orders-ny@springer-sbm.com, or visit www.springeronline.com. Apress Media, LLC is a California LLC and the sole member (owner) is Springer Science + Business Media Finance Inc (SSBM Finance Inc). SSBM Finance Inc is a Delaware corporation. For information on translations, please e-mail booktranslations@springernature.com; for reprint, paperback, or audio rights, please e-mail bookpermissions@springernature.com. Apress titles may be purchased in bulk for academic, corporate, or promotional use. eBook versions and licenses are also available for most titles. For more information, reference our Print and eBook Bulk Sales web page at http://www.apress.com/bulk-sales. Any source code or other supplementary material referenced by the author in this book is available to readers on GitHub (https://github.com/Apress). For more detailed information, please visit https://www.apress.com/gp/services/source-code. Paper in this product is recyclable Baji Shaik Texas, TX, USA Dinesh Kumar Chemuduru Andhra Pradesh, India
I extend this dedication to Afrah Razzak, my exceptional wife. Her enduring support and remarkable patience during the extended writing sessions have been invaluable to me. —Baji Shaik I lovingly extend this dedication to my dear friend, Baji Shaik. Your unwavering support and encouragement have been my guiding light, especially in the most challenging moments. Your belief in me has been a constant source of inspiration, and I am grateful for your presence in my journey. This book is as much a tribute to our friendship as it is a testament to the power of steadfast camaraderie. Thank you for always being there. —Dinesh Kumar Chemuduru
v About the Authors �������������������������������������������������������������������������������������������������� xiii About the Technical Reviewer ���������������������������������������������������������������������������������xv Acknowledgments �������������������������������������������������������������������������������������������������xvii Introduction ������������������������������������������������������������������������������������������������������������xix Chapter 1: Introduction to PL/pgSQL ������������������������������������������������������������������������ 1 A Closer Look at PL/pgSQL 1 PL/pgSQL Installation  2 PL/pgSQL Execution Flow  4 PL/pgSQL Blocks  6 Anonymous or Unnamed Blocks  6 Named Blocks  10 Summary 12 What’s Next  12 Chapter 2: PL/pgSQL Variables ������������������������������������������������������������������������������� 13 What Are Variables in PL/pgSQL?  13 Declaring Variables  13 Variable Scope  15 Constant Variables  17 Variable Alias  18 Scalar Variables  19 Array Variables  21 Record Variables  22 Cursor Variables  23 Table of Contents
vi Summary 25 What’s Next  25 Chapter 3: PL/pgSQL Data Types ���������������������������������������������������������������������������� 27 Data Types  27 Declaring Variables with Data Types  28 Supported Types  30 Base Type  32 Composite Type  33 Domain Type  35 Pseudo-Type  37 Range Type  38 Multirange Types  40 Summary 41 What’s Next  41 Chapter 4: Dealing with Strings, Numbers, and Arrays ������������������������������������������ 43 Strings  43 Function Format  45 Dealing with Null String  47 Numbers  50 Arrays  53 Example Use Cases  54 Strings  54 Numbers  55 Arrays  57 Summary 58 What’s Next  59 Chapter 5: Control Statements ������������������������������������������������������������������������������� 61 IF/ELSE Statement  62 Cascading IF Statements  65 CASE Statement  66 Table of ConTenTs
vii Iterative Statement  68 LOOP Statement  69 WHILE Statement  72 FOR Statement  74 Example Use Cases  78 Example 1  78 Example 2  81 Best Practices of Using Control Statements in PL/pgSQL  84 Summary 85 What’s Next  85 Chapter 6: Handling Arrays ������������������������������������������������������������������������������������ 87 Array Index  88 Array Length 89 Iterate Array  91 Find Duplicate Elements in Array  92 Append Elements to Array  93 Array Merge  94 Multidimensional Arrays  94 Summary 96 What’s Next  96 Chapter 7: Handling JSON �������������������������������������������������������������������������������������� 97 What Is JSON?  97 Use Cases  100 Advantages and Disadvantages  104 Build PL/pgSQL Functions for JSON  105 Indexing JSON Data  109 Other Useful JSON Functions  111 Summary 111 What’s Next  112 Table of ConTenTs
viii Chapter 8: Cursors ������������������������������������������������������������������������������������������������ 113 What Are Cursors?  113 CURSOR Attributes  115 ISOPEN Attribute  115 FOUND Attribute  117 NOTFOUND Attribute  119 ROWCOUNT Attribute  120 Monitor Cursors  122 SCROLL Cursor  123 NO SCROLL Cursor  125 WITH HOLD Cursors  127 Refcursors  128 Summary 130 What’s Next  130 Chapter 9: Custom Operators ������������������������������������������������������������������������������� 131 Built-In Operators  131 Creating a Custom Operator  135 Simple Example  136 SCENARIO 1: Case-Insensitive Comparison  140 SCENARIO 2: Custom Data Type Math 142 SCENARIO 3: Date Differentiate Operator  144 SCENARIO 4: Custom Operator for Data Classification  146 Advantages  148 Disadvantages 149 Summary 149 What’s Next  149 Chapter 10: Custom Casting ��������������������������������������������������������������������������������� 151 Built-In Casts  151 Custom Casts  160 Table of ConTenTs
ix Creating a Custom Cast  160 Simple Example  161 SCENARIO 1: Converting Custom Data Types  163 SCENARIO 2: Custom Data Type to JSONB  165 Summary 168 What’s Next  168 Chapter 11: Dynamic SQL ������������������������������������������������������������������������������������� 169 What Is Dynamic SQL?  169 Syntax of Dynamic SQL in PL/pgSQL  169 Simple Example  170 Use Cases of Dynamic SQL  171 Best Practices and Considerations for Dynamic SQL  178 1 Preventing SQL Injection  179 2 Sanitizing and Validating Inputs  179 3 Security Concerns  180 4 Performance Optimization  180 Summary 181 What’s Next  181 Chapter 12: Building Functions and Procedures �������������������������������������������������� 183 Functions  183 Defining Functions  184 Calling Functions  184 Categories  185 Immutable Functions  186 STABLE Functions  188 VOLATILE Functions  191 Procedures  194 Temporary Functions/Procedures  195 VARIADIC Functions/Procedures  196 Best Practices  198 Table of ConTenTs
x Summary 199 What’s Next  199 Chapter 13: Return Values and Parameters ���������������������������������������������������������� 201 Return Values  201 Simple Example  202 Different Ways to Return Values  203 RETURNS  203 RETURNS SETOF  204 RETURNS TABLE  204 OUT  205 Simple Difference Matrix  206 Different Examples for Each RETURN Type  206 Using SELECT Statements  207 Using RETURNS TABLE 208 Using RETURN NEXT  209 Using RETURNS SETOF TABLE  210 Using RETURNS SETOF Data Type  210 Using RETURNS RECORD  211 Using RETURNS SETOF RECORD  212 Using OUT Parameters  214 Using INOUT Parameter  216 Summary 216 What’s Next  217 Chapter 14: Handling Exceptions �������������������������������������������������������������������������� 219 Exceptions  219 GET DIAGNOSTICS  219 FOUND  223 Exceptions in PL/pgSQL  225 Different Ways to Handle Exceptions in PL/pgSQL  226 Using the BEGIN and END Statements  226 Table of ConTenTs
xi Using the RAISE Statement  231 Custom Exceptions  232 Rethrow Exceptions  233 ASSERT  234 Get Call Stack  235 Using the GET STACKED DIAGNOSTICS Statement  237 Advantages of Using Exceptions  238 Disadvantages of Using Exceptions  239 Summary 239 What’s Next  240 Chapter 15: Triggers ��������������������������������������������������������������������������������������������� 241 What Are Triggers?  241 Syntax  242 Simple Example  243 Types of Triggers in PostgreSQL  245 Row-Level Triggers  246 INSTEAD OF Triggers  253 Statement-Level Triggers  254 Event Triggers  258 Advantages of Triggers  260 Disadvantages of Triggers  261 DROP Triggers  261 Summary 262 What’s Next  262 Chapter 16: Transaction Management ������������������������������������������������������������������ 263 Nested Transactions  263 Exception Handling  266 Summary 270 What’s Next  270 Table of ConTenTs
xii Chapter 17: Aggregates ���������������������������������������������������������������������������������������� 271 Custom Aggregate  271 Simple Example  272 State Transition Function  273 Final Function  275 Creating Custom Aggregate  276 Create Type  277 Create State Transition Function 277 Create Aggregate  278 Final Function  279 Summary 281 What’s Next  281 Chapter 18: Listen and Notify ������������������������������������������������������������������������������� 283 Simple Example  283 Build Polling in psql  285 TCN Extension  290 Summary 292 What’s Next  292 Chapter 19: PL/pgSQL Essential Extensions ��������������������������������������������������������� 293 plprofiler Extension  293 Installation  295 Usage  296 plpgsql_check Extension  305 Installation  305 Usage  306 Summary 309 Index ��������������������������������������������������������������������������������������������������������������������� 311 Table of ConTenTs
xiii About the Authors Baji Shaik, currently serving as a Senior Database Consultant at AWS Professional Services, embarked on his journey into the world of databases in 2011. Since then, his expertise has encompassed an array of database technologies, including Oracle, PostgreSQL, EDB Postgres, Amazon RDS, Amazon Aurora, Amazon Redshift, and Greenplum. Baji’s extensive background spans both depth and breadth, showcasing his mastery in SQL/NoSQL database technologies. Baji stands out as a Database Migration Expert, having successfully developed numerous database solutions that tackle complex business challenges, particularly in migrating databases from on- premises environments to Amazon RDS and Aurora PostgreSQL/MySQL. His prowess also extends to performance optimization, having fine-tuned RDS/Aurora PostgreSQL/ MySQL databases to achieve remarkable performance benchmarks. With a passion for knowledge sharing, Baji has authored several notable books on PostgreSQL, such as PostgreSQL Configuration, Beginning PostgreSQL on the Cloud, and PostgreSQL Development Essentials. His commitment to education and information dissemination is further evident through his contributions to conferences, workshops, and a multitude of insightful blogs within the AWS blog community. Dinesh Kumar Chemuduru, an accomplished Principal Architect (OSS), brings a wealth of experience to the realm of technology and open source solutions. With a notable background at AWS as a proficient database consultant, Dinesh excelled in orchestrating numerous successful database migrations. His expertise extends to the open source arena, where he has both crafted and augmented solutions around PostgreSQL, showcasing his commitment to collaborative innovation.
xiv A coding enthusiast at heart, Dinesh finds joy in crafting applications using Flutter, Golang, and C++, platforms where his creativity knows no bounds. His proficiency extends to the deployment phase, as he deftly navigates Kubernetes to bring his coding creations to life. In the literary domain, Dinesh stands as a coauthor of the esteemed PostgreSQL High Performance Cookbook, a testament to his mastery of the subject matter. Beyond his own works, he actively engages in the appraisal of fellow authors’ PostgreSQL books, cementing his status as a valued participant in the exchange of knowledge. Dinesh’s impact reverberates through his open source contributions, which include the inception and enrichment of projects such as PTOR – an ingenious RPO/RTO/SLA calculator tailored for PostgreSQL. Another tool, “hammerpost,” sets a benchmark for synthetic parameter evaluation in PostgreSQL, seamlessly integrated with HammerDB. abouT The auThors
xv About the Technical Reviewer Deepak Ramnandan Mahto works as a PostgreSQL Database Engineer at Google Cloud. He has been working with PostgreSQL since 2018, and he also worked as a database migration consultant at AWS. He is also a keen blogger and loves to publish articles on migration, best practices, and on cloud with PostgreSQL. He loves to code and build database-related utilities using PL/pgSQL and SQL.
xvii Acknowledgments I would like to express my gratitude to several individuals who have played a crucial role in making this book a reality. A heartfelt thank-you to Apress Media for providing me with this valuable opportunity. I am especially grateful to my coauthor and mentor, Dinesh Kumar Chemuduru, for his exceptional collaboration. I want to express my gratitude to Divya Modi and Nirmal Selvaraj for being understanding of our hectic schedules and providing us with continuous support throughout the entire process. Special thanks to Deepak Mahto for his thorough review of the book. Lastly, I am profoundly thankful to my parents, Lalu Saheb Shaik and Nasar Bee, whose unwavering support has shaped me into the person I am today. —Baji Shaik I would like to extend my heartfelt gratitude to a remarkable group of individuals who have been instrumental in making this endeavor a reality. My heartfelt thank-you to Apress Media for providing me with this valuable opportunity. A special note of appreciation to my esteemed coauthor, Baji Shaik. Sincere thanks to Deepak Mahto, whose meticulous review and insightful feedback significantly enhanced the quality and depth of this manuscript. To Divya Modi and Nirmal Selvaraj, our project coordinators, your organizational skills and dedication ensured that every aspect of this project fell seamlessly into place. To my parents Vanamma, Sreenivasulu and my dearest children, Yashvi and Isha, and to the future luminaries, Hema Siri K and Rahul Sonu K – your unwavering love and understanding throughout the demanding phases of this project have served as my steadfast anchor. Your continuous support is my driving force. Finally, a heartfelt thank-you to my exceptional team at Tessell. Your commitment to excellence and innovation is awe-inspiring. Together, we are shaping the future of DBaaS, and I am privileged to work alongside such talented individuals. —Dinesh Kumar Chemuduru
xix Introduction The PostgreSQL engine comes with its own dedicated procedural language, similar to procedural languages found in other commercial database engines. This language, known as PL/pgSQL, offers a range of powerful features that developers have long desired. For instance, PL/pgSQL includes certain object-oriented programming capabilities like the ability to define custom operators and types, as well as custom aggregates. In contrast to other programming languages supported by PostgreSQL, PL/pgSQL is intricately linked with the PostgreSQL database engine interface. This tight integration ensures optimal performance and a seamless fit for constructing business logic on the database side. In this book, we not only introduce the fundamentals of PL/pgSQL, but we also dive deep into specific use cases that we’ve implemented for particular scenarios. Our aim is to comprehensively cover the various features, functionalities, and application scenarios of PL/pgSQL, offering assistance in crafting effective server-side objects with ease. Through the content of this book, you will gain an understanding of PL/pgSQL’s design and dive deep into its transaction model, including how commit and rollback operations function. You’ll discover strategies for optimizing PL/pgSQL functions and procedures and explore the mechanics of inline or anonymous server-side code, along with its limitations. Furthermore, you’ll acquire insights into debugging and profiling PL/pgSQL code and learn techniques for conducting statistical analyses on the PL/ pgSQL code you create.
1 CHAPTER 1 Introduction to PL/pgSQL In this chapter, we will start with an introduction of PL/pgSQL, on what is PL/pgSQL and what are the key features of it. We will talk about some common use cases where PL/pgSQL is used. PL/pgSQL comes by default when you install the PostgreSQL server. However, we will provide the steps to install PL/pgSQL. We will explain how PL/pgSQL works with a simple flow diagram. We will show some basic examples of PL/pgSQL code blocks which are called anonymous and named code blocks. A Closer Look at PL/pgSQL PostgreSQL uses SQL (Structured Query Language) as a default query language. SQL is a common domain-specific language for relational databases. PostgreSQL uses some extensions and features to implement the standards of SQL. In addition to SQL, PostgreSQL supports many procedural languages like PL/pgSQL, PL/Java, PLV8, PL/Python, PL/Perl, etc. Using these languages, you can create functions, stored procedures, and triggers which will improve the performance by reducing the multiple iterations to the databases. PL/pgSQL is the most commonly used procedural language in PostgreSQL. It is an extension of SQL. It is similar to Oracle's PL/SQL and supports features like control structures, exception handling, variables, loops, and conditional statements. These features help us to develop complex database applications in an efficient way. When working on designing a complex business logic inside the database, you would need to develop multiple SQLs which are sometimes interdependent. Results of one SQL will be used by other SQLs. In this case, running multiple SQLs increases the data flow between the database and the client application and will cause performance bottlenecks due to high data transfer through the network. To overcome this, you can use stored procedures or functions. © Baji Shaik and Dinesh Kumar Chemuduru 2023 B. Shaik and D. K. Chemuduru, Procedural Programming with PostgreSQL PL/pgSQL, https://doi.org/10.1007/978-1-4842-9840-4_1
2 PL/pgSQL supports stored procedures, functions, and triggers. A stored procedure is a set of precompiled SQL statements which can be executed repeatedly. Stored procedures can help to reduce network traffic and improve performance by reducing the amount of data that needs to be sent between the database and the client application. The common use cases to use stored procedures or functions using PL/pgSQL are 1. Improve data processing speed by using precompiled code through stored procedures which will be faster than raw SQL queries. 2. Write more complex code using features like control structures, exception handling, variables, loops, conditional statements, etc. 3. Using stored procedures or functions, you can create a reusable code to call from the applications to save time and effort. 4. PL/pgSQL is portable across different operating systems and platforms. This makes it easier to migrate code between different environments. 5. Prevent unauthorized access and data breaches by controlling the user authentication on stored procedures or functions. 6. Use triggers to implement constraints of business processes that cannot be expressed as foreign keys or check constraints. PL/pgSQL Installation PL/pgSQL is already included in PostgreSQL, so if you have PostgreSQL installed, you should have PL/pgSQL as well. However, you may need to enable it if it is not already enabled. Here are the steps to enable PL/pgSQL in PostgreSQL: 1. Install PostgreSQL psql client to connect to the database, or you can use the pgAdmin client tool. For Ubuntu, the following are the simple steps to install the client: # Create the file repository configuration: sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' Chapter 1 IntroduCtIon to pL/pgSQL
3 # Import the repository signing key: wget --quiet -O - https://www.postgresql.org/media/keys/ ACCC4CF8.asc | sudo apt-key add - # Update the package lists: sudo apt-get update # Install the latest version of PostgreSQL. If you want a specific version, use 'postgresql-12' or similar instead of 'postgresql': sudo apt-get -y install postgresql-client-15 For Linux (RHEL), you can follow the steps here: www.postgresql.org/download/linux/redhat/ 2. Connect to the database and check if PL/pgSQL is already installed: postgres=# \dx List of installed extensions Name | Version | Schema | Description -----+---------+--------+------------- (0 rows) postgres=# select * from pg_extension where extname='plpgsql'; oid | extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition ----+---------+----------+--------------+----------------+------------+-----------+-------------- (0 rows) 3. Execute the following command to enable PL/pgSQL: postgres=# CREATE EXTENSION plpgsql; CREATE EXTENSION Chapter 1 IntroduCtIon to pL/pgSQL
4 4. Verify that PL/pgSQL is enabled by executing the following command: postgres=# \dx List of installed extensions Name | Version | Schema | Description ------ -+---------+------------+------------------------------ plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (1 row) JavaScript postgres=# select * from pg_extension where extname='plpgsql'; oid | extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition ------+---------+----------+--------------+----------------+------------+-----------+------------- 16388 | plpgsql | 10 | 11 | f | 1.0 | | (1 row) PL/pgSQL Execution Flow PL/pgSQL is like every other “loadable, procedural language.” PL/pgSQL gets loaded through a function manager called fmgr. The fmgr loads the language handler when a procedural language function or procedure is executed and calls it. The execution flow of PL/pgSQL code is similar to that of other procedural programming languages, with parsing, compilation, execution, and cleanup stages. However, PL/pgSQL code is executed on the server side, which means that it has direct access to the database and can perform database operations more efficiently than client-side code. On the first call of a PL/pgSQL function or procedure in a session, the server first parses the code to check for syntax errors. The call handler will “compile” a function statement tree once the code is parsed. When the code is compiled, it turns into an internal form that the server can execute more efficiently. SQL queries in the function are just kept as a string at this point, and the expressions like the following are actually SQL queries: my_var := some_param * 10 Chapter 1 IntroduCtIon to pL/pgSQL