Complete PL/pgSQL from a Developer’s Perspective
Playlists
- Home
- Program Playlist
- Playlist II
- Developer Roadmap
- What is this?
- 21 Layers Structured PDF Notes
- Macros Lists
- All Macros
- Sitemap
Site Navigation
About Us | Contact Us | Privacy Policy | Disclaimer | Terms & Conditions | Cookies Policy | Return & Refund Policy | EULAComplete PL/pgSQL from a Developer’s Perspective
Part 1
Foundations, Architecture, Syntax, Variables,
Control Flow, and Practical Development Patterns
Table of Contents
1.
Introduction
to PL/pgSQL
2.
Why Developers
Should Learn PL/pgSQL
3.
PostgreSQL
Procedural Language Architecture
4.
PL/pgSQL
Execution Model
5.
Advantages and
Limitations
6.
Setting Up a
Development Environment
7.
PL/pgSQL
Program Structure
8.
Blocks and
Scope
9.
Variables and
Data Types
10.
Constants
11.
Assignment
Operations
12.
SQL Inside
PL/pgSQL
13.
Conditional
Statements
14.
Loops and
Iteration
15.
Labels and
Nested Blocks
16.
Working with
NULL Values
17.
Best Practices
for Writing Maintainable Code
18.
Real-World
Development Patterns
Introduction to PL/pgSQL
PL/pgSQL is PostgreSQL's native
procedural programming language.
While SQL is excellent for
querying and manipulating data, real-world applications often require:
- Conditional logic
- Iterations
- Error handling
- Business rules
- Data validation
- Automation
PL/pgSQL fills this gap by
allowing developers to combine SQL with procedural programming constructs.
Think of it as:
SQL + Programming Logic = PL/pgSQL
It enables developers to build:
- Stored procedures
- Functions
- Triggers
- Data validation systems
- Reporting engines
- ETL processes
- Automation workflows
For PostgreSQL developers,
PL/pgSQL is one of the most important skills because it brings business logic
closer to the data layer.
Why Developers Should Learn
PL/pgSQL
Many teams place all business
logic inside application code.
However, certain operations are
significantly more efficient when executed inside the database.
Examples include:
Data Validation
Application → Database
Instead of validating in
multiple applications, validation can occur once inside PostgreSQL.
Bulk Processing
Updating millions of rows is
often faster inside PostgreSQL than transferring data to external applications.
Data Integrity
PL/pgSQL ensures rules remain
enforced regardless of which application accesses the database.
Reduced Network Traffic
Instead of:
App → Query
App → Process
App → Query
App → Process
You can execute everything in
one database call.
PostgreSQL Procedural
Language Architecture
PostgreSQL supports multiple
procedural languages:
|
Language |
Purpose |
|
PL/pgSQL |
Native PostgreSQL language |
|
PL/Python |
Python integration |
|
PL/Perl |
Perl scripting |
|
PL/Tcl |
Tcl integration |
|
PL/V8 |
JavaScript support |
PL/pgSQL remains the most
widely used because:
- Built-in support
- High performance
- Easy deployment
- Tight SQL integration
Architecture:
Application
|
V
PostgreSQL
|
V
PL/pgSQL Engine
|
V
SQL Executor
|
V
Storage Engine
PL/pgSQL Execution Model
Understanding execution helps
developers write efficient code.
Execution stages:
Compile
↓
Validate
↓
Store
↓
Execute
When a function is first
executed:
1.
PostgreSQL
parses code.
2.
Syntax is
validated.
3.
Execution plan
is generated.
4.
Plan may be
reused.
Example:
CREATE FUNCTION hello()
RETURNS TEXT
LANGUAGE plpgsql
AS $$
BEGIN
RETURN 'Hello World';
END;
$$;
The function becomes a database
object.
Advantages and Limitations
Advantages
Faster Processing
Data stays inside PostgreSQL.
Centralized Logic
Business rules exist in one
place.
Security
Functions can restrict direct
table access.
Reusability
Same logic can be used across
applications.
Reduced Application Complexity
Complex SQL operations move
into PostgreSQL.
Limitations
Vendor Dependency
PL/pgSQL is
PostgreSQL-specific.
Debugging Complexity
Application debugging tools may
not work directly.
Version Compatibility
Features may vary between
PostgreSQL versions.
Poor Design Risk
Overloading the database with
application logic can hurt maintainability.
Setting Up a Development
Environment
Verify PostgreSQL version:
SELECT version();
Check installed procedural
languages:
SELECT * FROM pg_language;
PL/pgSQL is usually installed
automatically.
Verify:
SELECT lanname
FROM pg_language
WHERE lanname='plpgsql';
Expected result:
plpgsql
PL/pgSQL Program Structure
Basic structure:
CREATE FUNCTION function_name()
RETURNS datatype
LANGUAGE plpgsql
AS $$
BEGIN
-- logic
END;
$$;
Example:
CREATE FUNCTION get_message()
RETURNS TEXT
LANGUAGE plpgsql
AS $$
BEGIN
RETURN 'Welcome';
END;
$$;
Execution:
SELECT get_message();
Output:
Welcome
Blocks and Scope
PL/pgSQL uses blocks.
Structure:
DECLARE
BEGIN
EXCEPTION
END;
Example:
DO $$
DECLARE
counter INTEGER := 1;
BEGIN
RAISE NOTICE 'Counter: %', counter;
END;
$$;
Output:
Counter: 1
Block Components
DECLARE Section
Used for variable declarations.
DECLARE
employee_id INTEGER;
BEGIN Section
Contains executable code.
BEGIN
employee_id := 100;
END;
EXCEPTION Section
Handles errors.
EXCEPTION
WHEN OTHERS THEN
RAISE NOTICE 'Error occurred';
Nested Blocks
Example:
DO $$
DECLARE
x INTEGER := 10;
BEGIN
DECLARE
y INTEGER := 20;
BEGIN
RAISE NOTICE '%', y;
END;
END;
$$;
Nested blocks create local
scope.
Variables and Data Types
Variables store temporary
values during execution.
Syntax:
variable_name datatype;
Example:
DECLARE
age INTEGER;
Common Data Types
|
Type |
Example |
|
INTEGER |
100 |
|
BIGINT |
999999 |
|
NUMERIC |
99.99 |
|
TEXT |
Hello |
|
BOOLEAN |
TRUE |
|
DATE |
2025-01-01 |
|
TIMESTAMP |
Current date-time |
|
UUID |
Unique identifier |
Variable Initialization
Example:
DECLARE
count INTEGER := 0;
Alternative:
DECLARE
count INTEGER DEFAULT 0;
Multiple Variables
DECLARE
first_name TEXT;
salary NUMERIC;
hire_date DATE;
Constants
Constants cannot change after
assignment.
Example:
DECLARE
pi CONSTANT NUMERIC := 3.14159;
Invalid:
pi := 5;
This generates an error.
Assignment Operations
Basic assignment:
variable := value;
Example:
DECLARE
total INTEGER;
BEGIN
total := 100;
END;
Expression Assignment
total := quantity * price;
Example:
DECLARE
quantity INTEGER := 5;
price NUMERIC := 20;
total NUMERIC;
BEGIN
total := quantity * price;
END;
Result:
100
SQL Inside PL/pgSQL
A major advantage is direct SQL
execution.
Example:
DECLARE
total_users INTEGER;
BEGIN
SELECT COUNT(*)
INTO total_users
FROM users;
END;
SELECT INTO
Used frequently.
Syntax:
SELECT column
INTO variable
FROM table;
Example:
DECLARE
employee_name TEXT;
BEGIN
SELECT name
INTO employee_name
FROM employees
WHERE id = 1;
END;
Conditional Statements
Conditional logic drives
business rules.
IF Statement
Syntax:
IF condition THEN
statements;
END IF;
Example:
IF age >= 18 THEN
RAISE NOTICE 'Adult';
END IF;
IF ELSE
IF age >= 18 THEN
RAISE NOTICE 'Adult';
ELSE
RAISE NOTICE 'Minor';
END IF;
IF ELSIF
IF score >= 90 THEN
grade := 'A';
ELSIF score >= 80 THEN
grade := 'B';
ELSIF score >= 70 THEN
grade := 'C';
ELSE
grade := 'D';
END IF;
CASE Statement
Alternative to multiple IF
blocks.
CASE status
WHEN 'NEW' THEN
action := 'Create';
WHEN 'ACTIVE' THEN
action := 'Process';
WHEN 'CLOSED' THEN
action := 'Archive';
END CASE;
Searched CASE
CASE
WHEN salary > 100000 THEN
level := 'Executive';
WHEN salary > 50000 THEN
level := 'Manager';
ELSE
level := 'Staff';
END CASE;
Loops and Iteration
Loops execute statements
repeatedly.
LOOP
Basic syntax:
LOOP
statements;
END LOOP;
Must include exit logic.
Example:
DECLARE
counter INTEGER := 1;
BEGIN
LOOP
EXIT WHEN counter > 5;
RAISE NOTICE '%', counter;
counter := counter + 1;
END LOOP;
END;
WHILE Loop
Syntax:
WHILE condition LOOP
statements;
END LOOP;
Example:
DECLARE
counter INTEGER := 1;
BEGIN
WHILE counter <= 5 LOOP
RAISE NOTICE '%', counter;
counter := counter + 1;
END LOOP;
END;
FOR Loop
Most common loop.
FOR i IN 1..10 LOOP
RAISE NOTICE '%', i;
END LOOP;
Output:
1
2
3
...
10
Reverse FOR Loop
FOR i IN REVERSE 10..1 LOOP
RAISE NOTICE '%', i;
END LOOP;
Query-Based FOR Loop
Very common in enterprise
systems.
FOR emp IN
SELECT *
FROM employees
LOOP
RAISE NOTICE '%', emp.name;
END LOOP;
FOREACH Loop
Used for arrays.
FOREACH item IN ARRAY products
LOOP
RAISE NOTICE '%', item;
END LOOP;
Labels and Nested Blocks
Labels improve readability.
Example:
<<outer_loop>>
FOR i IN 1..10 LOOP
EXIT outer_loop
WHEN i = 5;
END LOOP;
Useful in deeply nested loops.
Working with NULL Values
NULL handling is critical.
Incorrect:
IF value = NULL THEN
Correct:
IF value IS NULL THEN
Checking NULL
IF employee_name IS NULL THEN
employee_name := 'Unknown';
END IF;
COALESCE
Provides fallback values.
salary := COALESCE(salary,0);
Example:
SELECT COALESCE(phone,'N/A')
FROM customers;
Best Practices for Writing
Maintainable Code
Use Meaningful Names
Bad:
a INTEGER;
b INTEGER;
Good:
employee_count INTEGER;
monthly_salary NUMERIC;
Keep Functions Focused
Avoid:
Validation
+
Reporting
+
Notifications
+
Billing
inside one function.
Prefer:
One function
One responsibility
Avoid Excessive Nesting
Bad:
IF
└── IF
└── IF
└── IF
Use:
- Early exits
- Smaller functions
Comment Business Logic
Example:
-- Apply loyalty discount
discount := amount * 0.10;
Handle Edge Cases
Always consider:
- NULL values
- Empty result sets
- Negative values
- Duplicate records
- Concurrency issues
Real-World Development
Patterns
Pattern 1: Validation Function
CREATE FUNCTION validate_age(
p_age INTEGER
)
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
BEGIN
IF p_age < 18 THEN
RETURN FALSE;
END IF;
RETURN TRUE;
END;
$$;
Pattern 2: Salary Calculator
CREATE FUNCTION calculate_bonus(
salary NUMERIC
)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
BEGIN
RETURN salary * 0.10;
END;
$$;
Pattern 3: Status Mapping
CREATE FUNCTION order_status(
status_code INTEGER
)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
BEGIN
CASE status_code
WHEN 1 THEN
RETURN 'Pending';
WHEN 2 THEN
RETURN 'Processing';
WHEN 3 THEN
RETURN 'Completed';
ELSE
RETURN 'Unknown';
END CASE;
END;
$$;
Conclusion of Part 1
This first section established
the core foundations of PL/pgSQL:
- PL/pgSQL architecture
- Execution model
- Program structure
- Variables and constants
- SQL integration
- Conditional logic
- Loops
- NULL handling
- Development best practices
- Practical coding patterns
Part 2
Functions, Parameters, Return
Types, Records, Arrays, Dynamic SQL, Cursors, and Advanced Data Processing
This part focuses on the
features developers use most in production systems:
- Advanced functions
- Parameters
- Return mechanisms
- Records
- Composite types
- Arrays
- Dynamic SQL
- Cursors
- Data processing techniques
- Enterprise coding practices
Advanced Functions in
PL/pgSQL
Functions are the foundation of
PL/pgSQL development.
A function:
- Accepts input
- Performs logic
- Returns a result
Basic example:
CREATE FUNCTION add_numbers(
a INTEGER,
b INTEGER
)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
BEGIN
RETURN a + b;
END;
$$;
Execution:
SELECT add_numbers(10,20);
Result:
30
Function Components
CREATE FUNCTION function_name(
parameters
)
RETURNS datatype
LANGUAGE plpgsql
AS $$
DECLARE
BEGIN
EXCEPTION
END;
$$;
Each section serves a specific
purpose.
Function Naming Conventions
Large enterprise databases
often contain hundreds of functions.
Recommended style:
fn_
sp_
calc_
validate_
get_
set_
Examples:
get_customer_name()
calculate_tax()
validate_email()
Avoid:
func1()
test123()
abc()
Function Parameters
Parameters allow external
values to be passed into a function.
Example:
CREATE FUNCTION square_number(
num INTEGER
)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
BEGIN
RETURN num * num;
END;
$$;
Usage:
SELECT square_number(5);
Output:
25
Multiple Parameters
CREATE FUNCTION calculate_total(
quantity INTEGER,
price NUMERIC
)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
BEGIN
RETURN quantity * price;
END;
$$;
Usage:
SELECT calculate_total(5,100);
Result:
500
Named Parameters
PostgreSQL supports named
arguments.
SELECT calculate_total(
quantity => 10,
price => 50
);
Benefits:
- Improved readability
- Flexible ordering
- Easier maintenance
Default Parameter Values
CREATE FUNCTION calculate_tax(
amount NUMERIC,
tax_rate NUMERIC DEFAULT 0.18
)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
BEGIN
RETURN amount * tax_rate;
END;
$$;
Usage:
SELECT calculate_tax(1000);
Result:
180
IN Parameters
Default parameter type.
CREATE FUNCTION demo(
p_name TEXT
)
Values are passed into the
function.
OUT Parameters
Used to return values.
Example:
CREATE FUNCTION get_employee(
OUT employee_id INTEGER,
OUT employee_name TEXT
)
LANGUAGE plpgsql
AS $$
BEGIN
employee_id := 1;
employee_name := 'John';
END;
$$;
Execution:
SELECT * FROM get_employee();
Output:
1 | John
INOUT Parameters
Allow modification of incoming
values.
CREATE FUNCTION increase_salary(
INOUT salary NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
salary := salary * 1.10;
END;
$$;
Usage:
SELECT increase_salary(50000);
Result:
55000
Return Types
PL/pgSQL supports multiple
return mechanisms.
Returning Scalar Values
RETURNS INTEGER
Example:
RETURN 100;
Returning Text
RETURNS TEXT
Example:
RETURN 'Success';
Returning Boolean
RETURNS BOOLEAN
Example:
RETURN TRUE;
Returning Composite Types
Example table:
CREATE TABLE employees(
id INTEGER,
name TEXT
);
Function:
CREATE FUNCTION get_emp()
RETURNS employees
LANGUAGE plpgsql
AS $$
DECLARE
emp employees;
BEGIN
SELECT *
INTO emp
FROM employees
LIMIT 1;
RETURN emp;
END;
$$;
Returning TABLE
Very common in reporting
systems.
CREATE FUNCTION get_all_employees()
RETURNS TABLE(
id INTEGER,
name TEXT
)
LANGUAGE plpgsql
AS $$
BEGIN
RETURN QUERY
SELECT
e.id,
e.name
FROM employees e;
END;
$$;
Usage:
SELECT * FROM get_all_employees();
RETURN vs RETURN QUERY
RETURN
Returns a single value.
RETURN salary;
RETURN QUERY
Returns multiple rows.
RETURN QUERY
SELECT * FROM employees;
Working with RECORD Types
RECORD is one of the most
powerful PL/pgSQL features.
A RECORD can store an entire
row.
Example:
DECLARE
employee_record RECORD;
Fetching Into RECORD
DECLARE
employee_record RECORD;
BEGIN
SELECT *
INTO employee_record
FROM employees
WHERE id = 1;
END;
Access fields:
employee_record.name
employee_record.salary
employee_record.department
Advantages of RECORD
Useful when:
- Column count is unknown
- Dynamic queries exist
- Generic functions are required
ROWTYPE Variables
ROWTYPE provides table
structure awareness.
Example:
DECLARE
employee_row employees%ROWTYPE;
Usage:
SELECT *
INTO employee_row
FROM employees
WHERE id = 1;
Access:
employee_row.name
employee_row.salary
RECORD vs ROWTYPE
|
Feature |
RECORD |
ROWTYPE |
|
Dynamic Structure |
Yes |
No |
|
Fixed Columns |
No |
Yes |
|
Compile Validation |
Limited |
Strong |
|
Performance |
Slightly Lower |
Better |
Composite Types
Custom structures can be
created.
Example:
CREATE TYPE employee_info AS (
id INTEGER,
name TEXT,
salary NUMERIC
);
Function:
CREATE FUNCTION create_employee()
RETURNS employee_info
LANGUAGE plpgsql
AS $$
DECLARE
emp employee_info;
BEGIN
emp.id := 1;
emp.name := 'Alice';
emp.salary := 50000;
RETURN emp;
END;
$$;
Arrays in PL/pgSQL
Arrays store multiple values.
Example:
DECLARE
numbers INTEGER[];
Array Initialization
DECLARE
numbers INTEGER[] :=
ARRAY[1,2,3,4,5];
Accessing Elements
numbers[1]
Result:
1
PostgreSQL arrays start at
index 1.
Array Length
array_length(numbers,1)
Example:
DECLARE
total INTEGER;
BEGIN
total :=
array_length(numbers,1);
END;
FOREACH with Arrays
DECLARE
item INTEGER;
numbers INTEGER[] :=
ARRAY[10,20,30];
BEGIN
FOREACH item
IN ARRAY numbers
LOOP
RAISE NOTICE '%', item;
END LOOP;
END;
Output:
10
20
30
Multi-Dimensional Arrays
Example:
DECLARE
matrix INTEGER[][];
Initialize:
ARRAY[
[1,2],
[3,4]
]
Useful for specialized
calculations.
Dynamic SQL
Dynamic SQL allows query
generation during execution.
Essential for:
- Metadata-driven systems
- Admin tools
- Multi-tenant platforms
- ETL frameworks
EXECUTE Statement
Syntax:
EXECUTE query_string;
Example:
EXECUTE
'SELECT COUNT(*) FROM employees';
Building Dynamic Queries
DECLARE
sql_text TEXT;
BEGIN
sql_text :=
'SELECT COUNT(*) FROM employees';
EXECUTE sql_text;
END;
Dynamic Table Names
Example:
DECLARE
table_name TEXT := 'employees';
total INTEGER;
BEGIN
EXECUTE
'SELECT COUNT(*) FROM '
|| table_name
INTO total;
END;
SQL Injection Risk
Dangerous:
EXECUTE
'SELECT * FROM ' || user_input;
Never trust user input.
Using FORMAT()
Safe approach:
EXECUTE format(
'SELECT COUNT(*) FROM %I',
table_name
);
Common placeholders:
|
Placeholder |
Meaning |
|
%I |
Identifier |
|
%L |
Literal |
|
%s |
String |
Dynamic WHERE Conditions
EXECUTE format(
'SELECT * FROM employees WHERE
id=%L',
employee_id
);
Dynamic DDL
Creating tables dynamically:
EXECUTE
'
CREATE TABLE temp_data(
id INTEGER
)
';
Cursors
Cursors process large result
sets efficiently.
Without cursor:
SELECT *
FROM employees;
Millions of rows may consume
memory.
Cursor Declaration
DECLARE
emp_cursor CURSOR FOR
SELECT *
FROM employees;
Opening Cursor
OPEN emp_cursor;
Fetching Rows
FETCH emp_cursor
INTO employee_record;
Loop Through Cursor
DECLARE
emp RECORD;
emp_cursor CURSOR FOR
SELECT *
FROM employees;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor
INTO emp;
EXIT WHEN NOT FOUND;
RAISE NOTICE '%',
emp.name;
END LOOP;
CLOSE emp_cursor;
END;
Cursor Lifecycle
Declare
↓
Open
↓
Fetch
↓
Process
↓
Close
Refcursor
Refcursor allows cursor
passing.
Example:
CREATE FUNCTION get_cursor()
RETURNS refcursor
LANGUAGE plpgsql
AS $$
DECLARE
cur refcursor;
BEGIN
OPEN cur FOR
SELECT *
FROM employees;
RETURN cur;
END;
$$;
Bulk Data Processing
Enterprise systems frequently
process:
Millions of Rows
Thousands of Transactions
Large Imports
Batch Jobs
PL/pgSQL excels at these
workloads.
Batch Update Pattern
Example:
FOR emp IN
SELECT *
FROM employees
LOOP
UPDATE employees
SET salary =
salary * 1.05
WHERE id = emp.id;
END LOOP;
Better Set-Based Alternative
Instead of looping:
UPDATE employees
SET salary = salary * 1.05;
This is usually faster.
Developer Rule
Prefer:
Set-Based SQL
Over:
Row-by-Row Processing
Whenever possible.
Using FOUND
FOUND indicates whether an
operation succeeded.
Example:
SELECT *
INTO employee_record
FROM employees
WHERE id = 100;
Check:
IF FOUND THEN
RAISE NOTICE 'Found';
END IF;
GET DIAGNOSTICS
Retrieve execution information.
Example:
GET DIAGNOSTICS
rows_updated = ROW_COUNT;
Useful after updates:
UPDATE employees
SET active = TRUE;
Retrieve count:
GET DIAGNOSTICS
affected_rows = ROW_COUNT;
Assertions
Validate assumptions.
ASSERT salary > 0;
Example:
ASSERT employee_id IS NOT NULL;
Useful during development.
Enterprise Coding Practices
Prefix Parameters
p_employee_id
p_salary
p_department
Example:
CREATE FUNCTION update_salary(
p_employee_id INTEGER,
p_salary NUMERIC
)
Prefix Variables
v_total
v_count
v_status
Example:
DECLARE
v_total NUMERIC;
Use Consistent Naming
Example:
get_customer()
get_order()
get_invoice()
Avoid mixing styles.
Separate Business Logic
Bad:
Validation
Calculation
Notification
Reporting
inside one function.
Better:
validate_customer()
calculate_discount()
generate_report()
Document Assumptions
Example:
-- Assumes customer exists
-- Assumes order is active
Future developers benefit
significantly.
Common Developer Mistakes
Using Loops for Everything
Bad:
FOR row IN ...
Often replaceable with:
UPDATE
DELETE
INSERT
Ignoring NULL Handling
Bad:
salary * bonus
Good:
COALESCE(salary,0)
*
COALESCE(bonus,0)
Dynamic SQL Without FORMAT
Bad:
sql := 'SELECT * FROM ' || table_name;
Good:
format(
'SELECT * FROM %I',
table_name
);
Returning Wrong Types
Always ensure:
RETURNS NUMERIC
matches:
RETURN salary;
Type mismatches create
maintenance problems.
Real-World Function Example
CREATE FUNCTION calculate_order_total(
p_customer_id INTEGER
)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
DECLARE
v_total NUMERIC;
BEGIN
SELECT
SUM(quantity * price)
INTO v_total
FROM orders
WHERE customer_id =
p_customer_id;
RETURN COALESCE(v_total,0);
END;
$$;
Usage:
SELECT calculate_order_total(101);
Summary of Part 2
In this section we explored:
- Advanced functions
- Function parameters
- OUT and INOUT arguments
- Return types
- RECORD
- ROWTYPE
- Composite types
- Arrays
- Dynamic SQL
- FORMAT()
- Cursors
- Bulk processing
- FOUND
- GET DIAGNOSTICS
- Enterprise coding practices
These concepts form the core of
professional PL/pgSQL development used in production PostgreSQL systems.
Part 3
Exception Handling,
Transactions, Stored Procedures, Triggers, Auditing, Security, and Enterprise
Architecture
Understanding Errors in
PL/pgSQL
Database applications encounter
failures regularly.
Examples:
- Duplicate records
- Constraint violations
- Invalid data
- Deadlocks
- Missing records
- Arithmetic errors
- Permission issues
Without proper handling:
Error
↓
Function Stops
↓
Transaction Fails
↓
Application Failure
PL/pgSQL provides structured
exception handling.
Exception Handling
Architecture
Structure:
BEGIN
-- logic
EXCEPTION
-- error handling
END;
Example:
DO $$
BEGIN
RAISE NOTICE 'Start';
EXCEPTION
WHEN OTHERS THEN
RAISE NOTICE 'Error';
END;
$$;
Basic Exception Handling
Example:
DO $$
DECLARE
v_result INTEGER;
BEGIN
v_result := 10 / 0;
EXCEPTION
WHEN division_by_zero THEN
RAISE NOTICE
'Division by zero detected';
END;
$$;
Output:
Division by zero detected
Common Exception Types
|
Exception |
Description |
|
division_by_zero |
Divide by zero |
|
no_data_found |
Missing row |
|
too_many_rows |
Multiple rows returned |
|
unique_violation |
Duplicate key |
|
foreign_key_violation |
Invalid reference |
|
check_violation |
Check constraint failure |
|
null_value_not_allowed |
Null restriction |
|
numeric_value_out_of_range |
Numeric overflow |
Handling Multiple Exceptions
BEGIN
-- logic
EXCEPTION
WHEN division_by_zero THEN
RAISE NOTICE 'Math Error';
WHEN unique_violation THEN
RAISE NOTICE 'Duplicate Record';
WHEN OTHERS THEN
RAISE NOTICE 'Unexpected Error';
END;
The WHEN OTHERS Clause
Acts as a catch-all handler.
Example:
EXCEPTION
WHEN OTHERS THEN
RAISE NOTICE
'Unknown error occurred';
Useful for preventing
unexpected application crashes.
SQLSTATE Codes
Every PostgreSQL error has a
code.
Example:
EXCEPTION
WHEN SQLSTATE '23505' THEN
RAISE NOTICE
'Duplicate Key';
Common codes:
|
Code |
Meaning |
|
23505 |
Unique violation |
|
23503 |
Foreign key violation |
|
23514 |
Check violation |
|
22012 |
Division by zero |
|
22003 |
Numeric overflow |
Retrieving Error Details
PL/pgSQL exposes detailed
diagnostics.
Example:
DECLARE
v_message TEXT;
BEGIN
-- logic
EXCEPTION
WHEN OTHERS THEN
GET STACKED DIAGNOSTICS
v_message =
MESSAGE_TEXT;
RAISE NOTICE '%',
v_message;
END;
Capturing Complete Error
Information
DECLARE
v_message TEXT;
v_detail TEXT;
v_hint TEXT;
BEGIN
EXCEPTION
WHEN OTHERS THEN
GET STACKED DIAGNOSTICS
v_message = MESSAGE_TEXT,
v_detail =
PG_EXCEPTION_DETAIL,
v_hint = PG_EXCEPTION_HINT;
END;
Useful for enterprise logging
systems.
Raising Custom Errors
Developers can generate
application-specific errors.
Example:
RAISE EXCEPTION
'Invalid salary value';
Output:
ERROR:
Invalid salary value
Custom Validation Example
IF p_salary < 0 THEN
RAISE EXCEPTION
'Salary cannot be negative';
END IF;
RAISE Levels
PostgreSQL supports multiple
message levels.
|
Level |
Purpose |
|
DEBUG |
Development |
|
LOG |
Server logs |
|
INFO |
Informational |
|
NOTICE |
General information |
|
WARNING |
Potential issue |
|
EXCEPTION |
Error |
Example:
RAISE WARNING
'Customer missing address';
Building Validation
Frameworks
Enterprise systems often
centralize validation.
Example:
CREATE FUNCTION validate_email(
p_email TEXT
)
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
BEGIN
IF p_email NOT LIKE '%@%' THEN
RAISE EXCEPTION
'Invalid email';
END IF;
RETURN TRUE;
END;
$$;
Transaction Fundamentals
A transaction is a unit of
work.
Example:
Step 1
Step 2
Step 3
Commit
If one step fails:
Rollback Everything
ACID Principles
Every PostgreSQL transaction
follows ACID.
|
Property |
Meaning |
|
Atomicity |
All or nothing |
|
Consistency |
Valid state |
|
Isolation |
Independent execution |
|
Durability |
Permanent storage |
Transaction Example
BEGIN;
UPDATE accounts
SET balance = balance - 100
WHERE id = 1;
UPDATE accounts
SET balance = balance + 100
WHERE id = 2;
COMMIT;
Rollback Example
BEGIN;
UPDATE accounts
SET balance = balance - 100;
ROLLBACK;
Changes disappear.
Functions and Transactions
Important limitation:
Functions cannot execute:
COMMIT;
ROLLBACK;
Inside standard functions.
Transaction control belongs to
the caller.
Stored Procedures
PostgreSQL introduced
procedures to support transaction management.
Example:
CREATE PROCEDURE process_payment()
LANGUAGE plpgsql
AS $$
BEGIN
-- logic
END;
$$;
Execution:
CALL process_payment();
Functions vs Procedures
|
Feature |
Function |
Procedure |
|
Returns Value |
Yes |
Optional |
|
Used in SELECT |
Yes |
No |
|
Supports COMMIT |
No |
Yes |
|
Supports ROLLBACK |
No |
Yes |
Procedure Example
CREATE PROCEDURE transfer_funds(
p_from INTEGER,
p_to INTEGER,
p_amount NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE accounts
SET balance =
balance - p_amount
WHERE id = p_from;
UPDATE accounts
SET balance =
balance + p_amount
WHERE id = p_to;
COMMIT;
END;
$$;
Understanding Triggers
Triggers execute automatically
when database events occur.
Events:
INSERT
UPDATE
DELETE
TRUNCATE
Trigger Architecture
Event
↓
Trigger
↓
Trigger Function
↓
Business Logic
Why Triggers Matter
Triggers enable:
- Auditing
- Validation
- Automation
- Synchronization
- Logging
- Compliance
Trigger Function Example
CREATE FUNCTION log_insert()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
RAISE NOTICE
'Row Inserted';
RETURN NEW;
END;
$$;
Creating a Trigger
CREATE TRIGGER employee_insert_trigger
BEFORE INSERT
ON employees
FOR EACH ROW
EXECUTE FUNCTION log_insert();
BEFORE Triggers
Execute before modification.
Insert Request
↓
BEFORE Trigger
↓
Actual Insert
Example uses:
- Validation
- Data cleanup
- Transformation
AFTER Triggers
Execute after modification.
Insert
↓
Stored
↓
AFTER Trigger
Common uses:
- Logging
- Notifications
- Audit trails
NEW and OLD Records
Trigger functions access row
data.
NEW
Represents new row.
NEW.salary
OLD
Represents previous row.
OLD.salary
UPDATE Trigger Example
CREATE FUNCTION salary_change()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
IF NEW.salary <>
OLD.salary THEN
RAISE NOTICE
'Salary Updated';
END IF;
RETURN NEW;
END;
$$;
Trigger Timing Options
|
Timing |
Description |
|
BEFORE |
Before action |
|
AFTER |
After action |
|
INSTEAD OF |
Replace action |
Row-Level Triggers
Execute for each affected row.
Example:
FOR EACH ROW
If 1000 rows update:
Trigger Executes
1000 Times
Statement-Level Triggers
Execute once.
FOR EACH STATEMENT
If 1000 rows update:
Trigger Executes
1 Time
Building Audit Systems
Auditing is one of the most
common trigger use cases.
Audit table:
CREATE TABLE audit_log(
audit_id BIGSERIAL,
table_name TEXT,
operation TEXT,
changed_at TIMESTAMP
);
Audit Trigger Function
CREATE FUNCTION audit_changes()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO audit_log(
table_name,
operation,
changed_at
)
VALUES(
TG_TABLE_NAME,
TG_OP,
NOW()
);
RETURN NEW;
END;
$$;
Audit Trigger
CREATE TRIGGER employee_audit
AFTER INSERT OR UPDATE OR DELETE
ON employees
FOR EACH ROW
EXECUTE FUNCTION audit_changes();
Trigger Variables
Built-in variables:
|
Variable |
Description |
|
TG_NAME |
Trigger name |
|
TG_TABLE_NAME |
Table name |
|
TG_OP |
Operation |
|
TG_WHEN |
BEFORE/AFTER |
|
TG_LEVEL |
ROW/STATEMENT |
Example:
TG_OP
Returns:
INSERT
UPDATE
DELETE
Soft Delete Architecture
Instead of deleting records:
DELETE FROM customers;
Use:
is_deleted BOOLEAN
Trigger:
UPDATE customers
SET is_deleted = TRUE;
Benefits:
- Data recovery
- Compliance
- Historical analysis
Security Fundamentals
Database security is critical.
Layers:
Authentication
↓
Authorization
↓
Permissions
↓
Auditing
Roles
PostgreSQL uses roles.
Create role:
CREATE ROLE app_user;
Grant access:
GRANT SELECT
ON employees
TO app_user;
Function Security
Functions may execute with:
Invoker Rights
or
Definer Rights
SECURITY INVOKER
Default behavior.
SECURITY INVOKER
Uses caller permissions.
SECURITY DEFINER
Function executes using owner
permissions.
CREATE FUNCTION secure_function()
RETURNS VOID
SECURITY DEFINER
LANGUAGE plpgsql
AS $$
BEGIN
END;
$$;
Extremely powerful.
Use carefully.
Preventing Unauthorized
Access
Example:
REVOKE ALL
ON employees
FROM PUBLIC;
Grant through controlled
functions instead.
Logging Framework Design
Enterprise systems often
maintain centralized logs.
Example table:
CREATE TABLE application_log(
log_id BIGSERIAL,
severity TEXT,
message TEXT,
created_at TIMESTAMP
);
Logging Function
CREATE FUNCTION write_log(
p_severity TEXT,
p_message TEXT
)
RETURNS VOID
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO application_log(
severity,
message,
created_at
)
VALUES(
p_severity,
p_message,
NOW()
);
END;
$$;
Enterprise Architecture
Pattern
A common layered approach:
Application Layer
↓
API Layer
↓
PL/pgSQL Layer
↓
PostgreSQL Tables
Benefits:
- Centralized rules
- Consistency
- Security
- Easier maintenance
Business Rule Pattern
Example:
Customer Rules
Pricing Rules
Tax Rules
Discount Rules
stored in:
PL/pgSQL Functions
instead of duplicated across
applications.
Enterprise Trigger
Architecture
User Action
↓
Insert Record
↓
Validation Trigger
↓
Audit Trigger
↓
Notification Trigger
↓
Commit
Provides complete lifecycle
tracking.
Common Enterprise Mistakes
Excessive Trigger Usage
Bad architecture:
Insert
↓
Trigger A
↓
Trigger B
↓
Trigger C
↓
Trigger D
Difficult to debug.
Silent Error Handling
Bad:
EXCEPTION
WHEN OTHERS THEN
NULL;
Errors disappear.
Always log exceptions.
Overusing SECURITY DEFINER
Can create privilege escalation
vulnerabilities.
Apply strict access controls.
Missing Audit Trails
Regulated industries require
change tracking.
Never assume application logs
are sufficient.
Production Checklist
Before deploying PL/pgSQL code:
Validation
- Input validation
- Boundary testing
- NULL testing
Security
- Permission review
- Role review
- Injection review
Performance
- Query plans analyzed
- Indexes verified
- Trigger overhead measured
Reliability
- Exception handling
- Logging
- Auditing
Summary of Part 3
This section covered:
- Exception handling
- SQLSTATE codes
- Custom errors
- Transaction concepts
- Stored procedures
- Trigger architecture
- BEFORE and AFTER triggers
- Auditing frameworks
- Logging systems
- Roles and permissions
- SECURITY DEFINER
- Enterprise architecture patterns
These capabilities form the
backbone of production-grade PostgreSQL development.
Part 4
Performance Optimization,
Concurrency, Locking, ETL, Reporting, Testing, Deployment, and Enterprise Best
Practices
Understanding PL/pgSQL
Performance
A common misconception is:
Slow Application
=
Slow Database
In reality:
Slow SQL
↓
Slow Function
↓
Slow Application
PL/pgSQL performance depends
heavily on:
- Query design
- Index strategy
- Execution plans
- Lock contention
- Data volume
- Memory usage
Most performance issues
originate from inefficient SQL, not PL/pgSQL itself.
Performance Architecture
Application
↓
PL/pgSQL
↓
SQL Engine
↓
Query Planner
↓
Storage Engine
↓
Disk
Understanding every layer helps
identify bottlenecks.
Measuring Performance
Never optimize blindly.
Measure first.
Example:
EXPLAIN
SELECT *
FROM employees;
EXPLAIN
Displays the execution plan.
Example:
EXPLAIN
SELECT *
FROM employees
WHERE id = 100;
Output:
Index Scan
or
Sequential Scan
The difference can be enormous
on large tables.
EXPLAIN ANALYZE
Provides actual runtime
statistics.
EXPLAIN ANALYZE
SELECT *
FROM employees
WHERE id = 100;
Displays:
- Execution time
- Planning time
- Actual rows
- Loops
- Scan type
This should become a daily tool
for every PostgreSQL developer.
Reading Query Plans
Common operators:
|
Operator |
Meaning |
|
Seq Scan |
Full table scan |
|
Index Scan |
Index usage |
|
Bitmap Scan |
Multiple index access |
|
Hash Join |
Hash-based join |
|
Merge Join |
Sorted join |
|
Nested Loop |
Row-by-row join |
|
Aggregate |
Aggregation |
|
Sort |
Sorting operation |
Sequential Scan Example
SELECT *
FROM orders;
Plan:
Seq Scan on orders
PostgreSQL reads every row.
Acceptable for:
- Small tables
- Reporting scans
- Analytics
Dangerous for large OLTP
workloads.
Index Scan Example
SELECT *
FROM orders
WHERE order_id = 100;
Plan:
Index Scan
Much faster because PostgreSQL
jumps directly to relevant rows.
PL/pgSQL Performance Rule #1
Avoid unnecessary loops.
Bad:
FOR rec IN
SELECT *
FROM employees
LOOP
total :=
total + rec.salary;
END LOOP;
Better Approach
SELECT SUM(salary)
INTO total
FROM employees;
Set-based operations are
usually dramatically faster.
Row-by-Row Processing (RBAR)
A common anti-pattern:
Row
↓
Process
↓
Row
↓
Process
↓
Row
↓
Process
Known as:
RBAR
(Row By Agonizing Row)
Avoid whenever possible.
Set-Based Processing
Preferred approach:
UPDATE employees
SET salary = salary * 1.05;
One statement.
One execution plan.
Maximum efficiency.
Reducing Context Switching
Every transition between:
PL/pgSQL
↓
SQL Engine
has overhead.
Bad:
FOR rec IN ...
LOOP
SELECT ...
END LOOP;
Potentially thousands of SQL
executions.
Better:
Single Query
Efficient Bulk Inserts
Bad:
INSERT INTO logs VALUES (...);
INSERT INTO logs VALUES (...);
INSERT INTO logs VALUES (...);
Better
INSERT INTO logs
VALUES
(...),
(...),
(...);
Reduces transaction overhead.
Working with Large Data
Volumes
Enterprise databases often
contain:
Millions
Tens of Millions
Billions
of rows.
Techniques:
- Partitioning
- Parallel execution
- Batch processing
- Index optimization
Understanding PostgreSQL
Locks
Locks protect data consistency.
Without locks:
User A Updates
User B Updates
Conflict
Corruption
PostgreSQL prevents this.
Lock Types
Common lock levels:
|
Lock |
Purpose |
|
Row Lock |
Individual row |
|
Table Lock |
Entire table |
|
Advisory Lock |
Application-defined |
|
Share Lock |
Shared access |
|
Exclusive Lock |
Exclusive access |
Row-Level Locking
Example:
SELECT *
FROM accounts
WHERE id = 1
FOR UPDATE;
Prevents concurrent
modification.
Useful for:
- Financial systems
- Inventory management
- Reservation systems
FOR UPDATE Example
BEGIN;
SELECT *
FROM inventory
WHERE item_id = 10
FOR UPDATE;
UPDATE inventory
SET quantity = quantity - 1
WHERE item_id = 10;
COMMIT;
Prevents overselling.
FOR SHARE
Allows reading while blocking
modifications.
SELECT *
FROM products
FOR SHARE;
Useful for reporting scenarios.
Deadlocks
Deadlock example:
Transaction A:
Lock Row 1
Wait Row 2
Transaction B:
Lock Row 2
Wait Row 1
Result:
Deadlock
PostgreSQL automatically
terminates one transaction.
Deadlock Prevention
Use consistent ordering.
Good:
Account 1
Account 2
Account 3
Always lock in the same
sequence.
Never:
1 → 2
2 → 1
Advisory Locks
Application-controlled locks.
Example:
SELECT pg_advisory_lock(1001);
Release:
SELECT pg_advisory_unlock(1001);
Use Cases for Advisory Locks
Scheduled Jobs
Prevent duplicate execution.
Job Starts
↓
Acquire Lock
↓
Execute
↓
Release Lock
ETL Pipelines
Ensure only one import process
runs.
Report Generation
Prevent duplicate report
creation.
Partition-Aware PL/pgSQL
Partitioning improves
scalability.
Example:
sales_2024
sales_2025
sales_2026
under one logical table.
Range Partition Example
CREATE TABLE sales (
sale_id BIGINT,
sale_date DATE
)
PARTITION BY RANGE(sale_date);
Why Partitioning Matters
Benefits:
- Faster scans
- Easier maintenance
- Faster archiving
- Better performance
Partition Pruning
Query:
SELECT *
FROM sales
WHERE sale_date
BETWEEN '2026-01-01'
AND '2026-01-31';
PostgreSQL may scan only the
relevant partition.
Massive performance
improvement.
Materialized Views
Complex reports can be
expensive.
Example:
SELECT
department,
SUM(salary)
FROM employees
GROUP BY department;
Executed repeatedly:
High CPU
High I/O
Materialized View Solution
CREATE MATERIALIZED VIEW department_totals AS
SELECT
department,
SUM(salary)
FROM employees
GROUP BY department;
Refreshing Materialized
Views
REFRESH MATERIALIZED VIEW
department_totals;
Useful for:
- Dashboards
- Analytics
- Executive reporting
ETL with PL/pgSQL
ETL means:
Extract
Transform
Load
A common enterprise workload.
ETL Workflow
Source Data
↓
Validation
↓
Transformation
↓
Loading
↓
Reporting
ETL Validation Example
IF p_email IS NULL THEN
INSERT INTO rejected_records
VALUES(...);
END IF;
Data Cleansing Example
customer_name :=
TRIM(customer_name);
Standardization Example
customer_email :=
LOWER(customer_email);
Batch Processing Strategy
Avoid:
10 Million Rows
Single Transaction
Prefer:
10,000 Rows
Commit
10,000 Rows
Commit
10,000 Rows
Commit
Improves recovery and reduces
lock duration.
Reporting Functions
Many organizations build
reporting APIs using PL/pgSQL.
Example:
CREATE FUNCTION monthly_sales_report(
p_month INTEGER
)
RETURNS TABLE(
department TEXT,
revenue NUMERIC
)
Returns structured reporting
data.
Dashboard Data Functions
Common pattern:
CREATE FUNCTION dashboard_metrics()
RETURNS JSON;
Application:
Call Function
↓
Receive JSON
↓
Display Dashboard
JSON Support
PostgreSQL provides powerful
JSON capabilities.
Example:
SELECT json_build_object(
'name',
'John',
'salary',
50000
);
Result:
{
"name":"John",
"salary":50000
}
Returning JSON from PL/pgSQL
RETURN json_build_object(
'customer_id',
p_customer_id,
'status',
'active'
);
Popular for REST APIs.
Monitoring Function
Performance
Useful views:
pg_stat_activity
pg_stat_user_tables
pg_stat_user_indexes
pg_stat_statements
pg_stat_statements
One of PostgreSQL's most
valuable extensions.
Provides:
- Query frequency
- Execution time
- Average runtime
- Total resource usage
Example:
SELECT *
FROM pg_stat_statements;
Testing PL/pgSQL Code
Enterprise systems require
testing.
Testing categories:
Unit Tests
Integration Tests
Performance Tests
Security Tests
Regression Tests
Unit Testing Example
Function:
SELECT calculate_tax(1000);
Expected:
180
Verify automatically.
Edge Case Testing
Test:
NULL
Zero
Negative
Maximum Value
Minimum Value
Regression Testing
When code changes:
Run Old Tests
Verify Results
Prevent Breakage
CI/CD for PL/pgSQL
Modern teams deploy database
code automatically.
Pipeline:
Developer
↓
Git
↓
CI Server
↓
Automated Tests
↓
Staging
↓
Production
Version Control
Store:
Functions
Procedures
Triggers
Views
Schemas
in source control.
Example structure:
database/
├── functions
├── procedures
├── triggers
├── views
├── migrations
Migration-Based Deployment
Example:
001_create_tables.sql
002_create_indexes.sql
003_create_functions.sql
004_add_triggers.sql
Benefits:
- Traceability
- Rollback support
- Auditability
Deployment Checklist
Before release:
Function Review
- Naming standards
- Comments
- Error handling
Security Review
- Permissions
- Roles
- SQL injection checks
Performance Review
- Execution plans
- Index verification
- Load testing
Operational Review
- Logging
- Monitoring
- Alerting
Enterprise Case Study:
Payroll System
Requirements:
Salary Calculation
Tax Processing
Audit Logging
Compliance
PL/pgSQL Components:
calculate_salary()
calculate_tax()
audit_payroll_changes()
generate_payroll_report()
Enterprise Case Study:
Banking System
Requirements:
Transactions
Balances
Transfers
Fraud Checks
PL/pgSQL Components:
transfer_funds()
validate_account()
record_transaction()
fraud_detection()
Enterprise Case Study:
E-Commerce Platform
Requirements:
Orders
Inventory
Payments
Shipping
PL/pgSQL Components:
create_order()
reserve_inventory()
process_payment()
update_shipping_status()
Senior Developer Best
Practices
Keep Functions Small
Bad:
2000-Line Function
Good:
Validation Function
Calculation Function
Persistence Function
Prefer Set-Based Logic
Always ask:
Can SQL Do This Directly?
If yes, prefer SQL.
Log Meaningful Events
Track:
Failures
Warnings
Critical Changes
Not every action.
Avoid Hidden Business Logic
Document:
Triggers
Security Rules
Dependencies
Clearly.
Optimize Only After Measurement
Never assume:
Slow
Measure:
EXPLAIN ANALYZE
first.
Complete PL/pgSQL Developer
Roadmap
Beginner
Learn:
- Variables
- Loops
- Conditions
- Functions
Intermediate
Learn:
- Arrays
- Records
- Dynamic SQL
- Triggers
Advanced
Learn:
- Transactions
- Procedures
- Security
- Auditing
Senior
Master:
- Query planning
- Performance tuning
- Concurrency
- Partitioning
- ETL design
- CI/CD
- Architecture
Final Conclusion
PL/pgSQL is far more than a
procedural extension to SQL. It is a complete server-side programming
environment that enables developers to build high-performance, secure,
maintainable, and enterprise-grade database solutions directly within
PostgreSQL.
A professional PL/pgSQL
developer should be comfortable with:
- Procedural programming constructs
- Functions and procedures
- Dynamic SQL
- Records, arrays, and composite types
- Exception handling
- Transaction management
- Triggers and auditing
- Security and permissions
- Performance optimization
- Query planning
- Locking and concurrency control
- ETL and reporting systems
- Testing and deployment automation
Comments
Post a Comment