Procedural Programming with PostgreSQL PL/pgSQL : Design Complex Database-Centric Applications with PL/pgSQL / by Baji Shaik, Dinesh Kumar Chemuduru.

Format:

Book

Author/Creator:

Shaik, Baji.

Contributor:

Chemuduru, Dinesh Kumar.

Series:

Professional and Applied Computing Series

Language:

English

Subjects (All):

Programming languages (Electronic computers).

Open source software.

Database management.

Programming Language.

Open Source.

Database Management System.

Local Subjects:

Programming Language.

Open Source.

Database Management System.

Physical Description:

1 online resource (326 pages)

Edition:

1st ed. 2023.

Place of Publication:

Berkeley, CA : Apress : Imprint: Apress, 2023.

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

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

Chapter 4: Dealing with Strings, Numbers, and Arrays

Strings

Function Format

Dealing with Null String

Numbers

Arrays

Example Use Cases

Chapter 5: Control Statements

IF/ELSE Statement

Cascading IF Statements

CASE Statement

Iterative Statement

LOOP Statement

WHILE Statement

FOR Statement

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

Chapter 6: Handling Arrays

Array Index

Array Length

Iterate Array

Find Duplicate Elements in Array

Append Elements to Array

Array Merge

Multidimensional Arrays

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

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

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

Chapter 10: Custom Casting

Built-In Casts

Custom Casts

Creating a Custom Cast

SCENARIO 1: Converting Custom Data Types

SCENARIO 2: Custom Data Type to JSONB

Chapter 11: Dynamic SQL

What Is Dynamic SQL?

Syntax of Dynamic SQL in PL/pgSQL

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

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

Chapter 13: Return Values and Parameters

Return Values

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

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

Chapter 15: Triggers

What Are Triggers?

Syntax

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

Chapter 16: Transaction Management

Nested Transactions

Exception Handling

Chapter 17: Aggregates

Custom Aggregate

State Transition Function

Final Function

Creating Custom Aggregate

Create Type

Create State Transition Function

Create Aggregate

Chapter 18: Listen and Notify

Build Polling in psql

TCN Extension

What's Next.

Chapter 19: PL/pgSQL Essential Extensions

plprofiler Extension

Installation

Usage

plpgsql_check Extension

Index.

Notes:

Description based on publisher supplied metadata and other sources.

ISBN:

9781484298404

1484298403

OCLC:

1407211994