| Description |
1 online resource |
| Summary |
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. You will: 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. |
| Contents |
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. |
| Subject |
PostgreSQL.
|
|
Procedure calls (Computer programming)
|
|
Relational databases.
|
|
Bases de données relationnelles. |
| Added Author |
Chemuduru, Dinesh Kumar.
|
| Other Form: |
Print version: 148429839X 9781484298398 (OCoLC)1395067520 |
| ISBN |
9781484298404 (electronic bk.) |
|
1484298403 (electronic bk.) |
| Standard No. |
10.1007/978-1-4842-9840-4 doi |
|
More Resources