PL/pgSQL Basics

Write procedural logic in PostgreSQL's native PL/pgSQL language.

Overview

PL/pgSQL is PostgreSQL's built-in procedural language for writing functions, procedures, and triggers with complex logic. It supports variables, constants, IF/ELSIF/ELSE, WHILE, FOR loops, RAISE for logging/errors, and exception handling with BEGIN/EXCEPTION/END blocks. PL/pgSQL runs inside the database server no network round trips for each statement. It has access to all SQL data types and can return any type including tables.

Key Points

•DECLARE section: variables and constants
•IF cond THEN ... ELSIF cond THEN ... ELSE ... END IF
•WHILE cond LOOP ... END LOOP
•FOR var IN QUERY LOOP ... END LOOP iterate over rows
•RAISE NOTICE/WARNING/EXCEPTION for messages/errors
•Exception handling: BEGIN ... EXCEPTION WHEN others THEN ... END

Examples

PL/pgSQL with variables, IF, and RAISE

CREATE OR REPLACE FUNCTION classify_score(score INT)
RETURNS TEXT LANGUAGE plpgsql IMMUTABLE AS $$
DECLARE
  grade TEXT;
BEGIN
  IF score >= 90 THEN grade := 'A';
  ELSIF score >= 80 THEN grade := 'B';
  ELSIF score >= 70 THEN grade := 'C';
  ELSE grade := 'F';
  END IF;

  RAISE NOTICE 'Score % classified as %', score, grade;
  RETURN grade;
END;
$$;

SELECT classify_score(85);  -- B

PL/pgSQL Basics

Overview

Key Points

Examples

Exercises

Knowledge Check

What symbol is used for assignment in PL/pgSQL?