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
Comments 0
Loading comments...
Reply to Comment
Edit Comment