Procedural Programming with PostgreSQL PL/pgSQL : Design Complex Database-Centric Applications with PL/pgSQL

Table of Contents 5 About the Authors 13 About the Technical Reviewer 15 Acknowledgments 16 Introduction 17 Chapter 1: Introduction to PL/pgSQL 18 A Closer Look at PL/pgSQL 18 PL/pgSQL Installation 19 PL/pgSQL Execution Flow 21 PL/pgSQL Blocks 23 Anonymous or Unnamed Blocks 23 Named Blocks 27 Summary 29 What’s Next 29 Chapter 2: PL/pgSQL Variables 30 What Are Variables in PL/pgSQL? 30 Declaring Variables 30 Variable Scope 32 Constant Variables 34 Variable Alias 35 Scalar Variables 36 Array Variables 38 Record Variables 39 Cursor Variables 40 Summary 42 What’s Next 42 Chapter 3: PL/pgSQL Data Types 43 Data Types 43 Declaring Variables with Data Types 44 Supported Types 46 Base Type 48 Composite Type 49 Domain Type 51 Pseudo-Type 53 Range Type 54 Multirange Types 56 Summary 57 What’s Next 57 Chapter 4: Dealing with Strings, Numbers, and Arrays 59 Strings 59 Function Format 61 Dealing with Null String 63 Numbers 66 Arrays 69 Example Use Cases 70 Strings 70 Numbers 71 Arrays 73 Summary 74 What’s Next 75 Chapter 5: Control Statements 76 IF/ELSE Statement 77 Cascading IF Statements 80 CASE Statement 81 Iterative Statement 83 LOOP Statement 84 WHILE Statement 87 FOR Statement 89 Example Use Cases 93 Example 1 93 Example 2 96 Best Practices of Using Control Statements in PL/pgSQL 99 Keep Control Statements Simple 99 Use Comments to Explain Complex Control Statements 99 Test Your Control Statements Thoroughly 99 Use Meaningful Variable Names 99 Don’t Overuse Control Statements 100 Summary 100 What’s Next 100 Chapter 6: Handling Arrays 102 Array Index 103 Array Length 104 Iterate Array 106 Find Duplicate Elements in Array 107 Append Elements to Array 108 Array Merge 109 Multidimensional Arrays 109 Summary 111 What’s Next 111 Chapter 7: Handling JSON 112 What Is JSON? 112 Use Cases 115 Advantages and Disadvantages 119 Build PL/pgSQL Functions for JSON 120 Indexing JSON Data 124 Other Useful JSON Functions 126 Summary 126 What’s Next 127 Chapter 8: Cursors 128 What Are Cursors? 128 CURSOR Attributes 130 ISOPEN Attribute 130 FOUND Attribute 132 NOTFOUND Attribute 134 ROWCOUNT Attribute 135 Monitor Cursors 137 SCROLL Cursor 138 Phase 1 140 Phase 2 140 NO SCROLL Cursor 140 WITH HOLD Cursors 142 Refcursors 143 Summary 145 What’s Next 145 Chapter 9: Custom Operators 146 Built-In Operators 146 Creating a Custom Operator 150 Simple Example 151 SCENARIO 1: Case-Insensitive Comparison 155 Benefits 157 SCENARIO 2: Custom Data Type Math 157 SCENARIO 3: Date Differentiate Operator 159 SCENARIO 4: Custom Operator for Data Classification 161 Advantages 163 Disadvantages 164 Summary 164 What’s Next 164 Chapter 10: Custom Casting 166 Built-In Casts 166 Custom Casts 175 Creating a Custom Cast 175 Simple Example 176 SCENARIO 1: Converting Custom Data Types 178 SCENARIO 2: Custom Data Type to JSONB 180 Summary 183 What’s Next 183 Chapter 11: Dynamic SQL 184 What Is Dynamic SQL? 184 Syntax of Dynamic SQL in PL/pgSQL 184 Simple Example 185 Use Cases of Dynamic SQL 186 Dynamic Table Creation 186 Dynamic Query Building 188 Dynamic Index Creation 190 Dynamic Column Selection 191 Best Practices and Considerations for Dynamic SQL 193 1. Preventing SQL Injection 194 2. Sanitizing and Validating Inputs 194 3. Security Concerns 195 4. Performance Optimization 195 Summary 196 What’s Next 196 Chapter 12: Building Functions and Procedures 197 Functions 197 Defining Functions 198 Calling Functions 198 Categories 199 Immutable Functions 200 STABLE Functions 202 VOLATILE Functions 205 Procedures 208 Temporary Functions/Procedures 209 VARIADIC Functions/Procedures 210 Best Practices 212 Summary 213 What’s Next 213 Chapter 13: Return Values and Parameters 214 Return Values 214 Simple Example 215 Different Ways to Return Values 216 RETURNS 216 RETURNS SETOF 217 RETURNS TABLE 217 OUT 218 Simple Difference Matrix 219 Different Examples for Each RETURN Type 219 Using SELECT Statements 220 Using RETURNS TABLE 221 Using RETURN NEXT 222 Using RETURNS SETOF TABLE 223 Using RETURNS SETOF Data Type 223 Using RETURNS RECORD 224 Using RETURNS SETOF RECORD 225 Using OUT Parameters 227 Using INOUT Parameter 229 Summary 229 What’s Next 230 Chapter 14: Handling Exceptions 231 Exceptions 231 GET DIAGNOSTICS 231 FOUND 235 Exceptions in PL/pgSQL 237 Different Ways to Handle Exceptions in PL/pgSQL 238 Using the BEGIN and END Statements 238 Using the RAISE Statement 243 Custom Exceptions 244 Rethrow Exceptions 245 ASSERT 246 Get Call Stack 247 Using the GET STACKED DIAGNOSTICS Statement 249 Advantages of Using Exceptions 250 Disadvantages of Using Exceptions 251 Summary 251 What’s Next 252 Chapter 15: Triggers 253 What Are Triggers? 253 Syntax 254 Simple Example 255 Types of Triggers in PostgreSQL 257 Row-Level Triggers 258 Creating Row-Level Triggers 258 Example 1: Enforce Data Constraint 258 Example 2: Creating Multiple Triggers on the Same Table 260 Example 3: Prevent Nested Invocations 261 Example 4: Replicating Data Across Tables 262 INSTEAD OF Triggers 265 Statement-Level Triggers 266 Creating Statement-Level Triggers 266 Example: Logging Changes to a Table 267 Event Triggers 270 Creating Event Triggers 270 Example: Log DDL Changes 270 Advantages of Triggers 272 Disadvantages of Triggers 273 DROP Triggers 273 Summary 274 What’s Next 274 Chapter 16: Transaction Management 275 Nested Transactions 275 Exception Handling 278 Summary 282 What’s Next 282 Chapter 17: Aggregates 283 Custom Aggregate 283 Simple Example 284 State Transition Function 285 Final Function 287 Creating Custom Aggregate 288 Create Type 289 Create State Transition Function 289 Create Aggregate 290 Final Function 291 Summary 293 What’s Next 293 Chapter 18: Listen and Notify 294 Simple Example 294 Build Polling in psql 296 TCN Extension 301 Summary 303 What’s Next 303 Chapter 19: PL/pgSQL Essential Extensions 304 plprofiler Extension 304 Installation 306 Usage 307 plpgsql_check Extension 316 Installation 316 Usage 317 Summary 320 Index 321 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.