Intro
Table of Contents
About the Authors
About the Technical Reviewer
Acknowledgments
Introduction
Chapter 1: Introduction to PL/pgSQL
A Closer Look at PL/pgSQL
PL/pgSQL Installation
PL/pgSQL Execution Flow
PL/pgSQL Blocks
Anonymous or Unnamed Blocks
Named Blocks
Summary
What's Next
Chapter 2: PL/pgSQL Variables
What Are Variables in PL/pgSQL?
Declaring Variables
Variable Scope
Constant Variables
Variable Alias
Scalar Variables
Array Variables
Record Variables
Cursor Variables
Summary
What's Next
Chapter 3: PL/pgSQL Data Types
Data Types
Declaring Variables with Data Types
Supported Types
Base Type
Composite Type
Domain Type
Pseudo-Type
Range Type
Multirange Types
Summary
What's Next
Chapter 4: Dealing with Strings, Numbers, and Arrays
Strings
Function Format
Dealing with Null String
Numbers
Arrays
Example Use Cases
Strings
Numbers
Arrays
Summary
What's Next
Chapter 5: Control Statements
IF/ELSE Statement
Cascading IF Statements
CASE Statement
Iterative Statement
LOOP Statement
WHILE Statement
FOR Statement
Example Use Cases
Example 1
Example 2
Best Practices of Using Control Statements in PL/pgSQL
Keep Control Statements Simple
Use Comments to Explain Complex Control Statements
Test Your Control Statements Thoroughly
Use Meaningful Variable Names
Don't Overuse Control Statements
Summary
What's Next
Chapter 6: Handling Arrays
Array Index
Array Length
Iterate Array
Find Duplicate Elements in Array
Append Elements to Array
Array Merge
Multidimensional Arrays
Summary
What's Next
Chapter 7: Handling JSON
What Is JSON?
Use Cases
Advantages and Disadvantages
Build PL/pgSQL Functions for JSON.

Indexing JSON Data
Other Useful JSON Functions
Summary
What's Next
Chapter 8: Cursors
What Are Cursors?
CURSOR Attributes
ISOPEN Attribute
FOUND Attribute
NOTFOUND Attribute
ROWCOUNT Attribute
Monitor Cursors
SCROLL Cursor
Phase 1
Phase 2
NO SCROLL Cursor
WITH HOLD Cursors
Refcursors
Summary
What's Next
Chapter 9: Custom Operators
Built-In Operators
Creating a Custom Operator
Simple Example
SCENARIO 1: Case-Insensitive Comparison
Benefits
SCENARIO 2: Custom Data Type Math
SCENARIO 3: Date Differentiate Operator
SCENARIO 4: Custom Operator for Data Classification
Advantages
Disadvantages
Summary
What's Next
Chapter 10: Custom Casting
Built-In Casts
Custom Casts
Creating a Custom Cast
Simple Example
SCENARIO 1: Converting Custom Data Types
SCENARIO 2: Custom Data Type to JSONB
Summary
What's Next
Chapter 11: Dynamic SQL
What Is Dynamic SQL?
Syntax of Dynamic SQL in PL/pgSQL
Simple Example
Use Cases of Dynamic SQL
Dynamic Table Creation
Dynamic Query Building
Dynamic Index Creation
Dynamic Column Selection
Best Practices and Considerations for Dynamic SQL
1. Preventing SQL Injection
2. Sanitizing and Validating Inputs
3. Security Concerns
4. Performance Optimization
Summary
What's Next
Chapter 12: Building Functions and Procedures
Functions
Defining Functions
Calling Functions
Categories
Immutable Functions
STABLE Functions
VOLATILE Functions
Procedures
Temporary Functions/Procedures
VARIADIC Functions/Procedures
Best Practices
Summary
What's Next
Chapter 13: Return Values and Parameters
Return Values
Simple Example
Different Ways to Return Values
RETURNS
RETURNS SETOF
RETURNS TABLE
OUT
Simple Difference Matrix.

Different Examples for Each RETURN Type
Using SELECT Statements
Using RETURNS TABLE
Using RETURN NEXT
Using RETURNS SETOF TABLE
Using RETURNS SETOF Data Type
Using RETURNS RECORD
Using RETURNS SETOF RECORD
Using OUT Parameters
Using INOUT Parameter
Summary
What's Next
Chapter 14: Handling Exceptions
Exceptions
GET DIAGNOSTICS
FOUND
Exceptions in PL/pgSQL
Different Ways to Handle Exceptions in PL/pgSQL
Using the BEGIN and END Statements
Using the RAISE Statement
Custom Exceptions
Rethrow Exceptions
ASSERT
Get Call Stack
Using the GET STACKED DIAGNOSTICS Statement
Advantages of Using Exceptions
Disadvantages of Using Exceptions
Summary
What's Next
Chapter 15: Triggers
What Are Triggers?
Syntax
Simple Example
Types of Triggers in PostgreSQL
Row-Level Triggers
Creating Row-Level Triggers
Example 1: Enforce Data Constraint
Example 2: Creating Multiple Triggers on the Same Table
Example 3: Prevent Nested Invocations
Example 4: Replicating Data Across Tables
INSTEAD OF Triggers
Statement-Level Triggers
Creating Statement-Level Triggers
Example: Logging Changes to a Table
Event Triggers
Creating Event Triggers
Example: Log DDL Changes
Advantages of Triggers
Disadvantages of Triggers
DROP Triggers
Summary
What's Next
Chapter 16: Transaction Management
Nested Transactions
Exception Handling
Summary
What's Next
Chapter 17: Aggregates
Custom Aggregate
Simple Example
State Transition Function
Final Function
Creating Custom Aggregate
Create Type
Create State Transition Function
Create Aggregate
Final Function
Summary
What's Next
Chapter 18: Listen and Notify
Simple Example
Build Polling in psql
TCN Extension
Summary
What's Next.

Chapter 19: PL/pgSQL Essential Extensions
plprofiler Extension
Installation
Usage
plpgsql_check Extension
Installation
Usage
Summary
Index.