[ <<label>>]
[ DECLARE
    declarations ]
BEGIN
    statements
END [ label ]

CREATE FUNCTION nested() RETURNS integer AS $$
DECLARE
    value integer := 5;
BEGIN
    DECLARE
        value integer := 10;
    BEGIN
        RAISE NOTICE 'Inner value= %', value;  -- 10
    END;
    RAISE NOTICE     'Outer value= %', value;  -- 5
    RETURN value;
END;
$$ LANGUAGE plpgsql;

DECLARE

  value INTEGER;

  stock_num DECIMAL(5);

  stock_num NUMERIC(5);

  stock_name CHAR(5);

  stock_name VARCHAR(30);

  stock_date DATE;

  stock_required CONSTANT NUMERIC(5) := 30;

  max_salary CONSTANT NUMERIC(11,2) := 2.45;

  course_row COURSE%ROWTYPE;

  course_number COURSE.cnum%TYPE;

  query CURSOR FOR SELECT * FROM COURSE;

department_code := 'SCIT';

SELECT name
INTO STRICT department_name
FROM DEPARTMENT
WHERE code = department_code;

IF (credits < limit) THEN
  credits := credits + 1;
  limit := limit - 1;
ELSIF (credits = limit) THEN
  credits := credits - 2;	   
ELSE
  limit := limit + 1;
END IF;

FOR i IN 1..100 LOOP
  total := total + i;
END LOOP;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    INSERT INTO AUDIT_TABLE VALUES( current_timestamp, course_number );
      RAISE NOTICE 'Course % not found', course_number;

EXCEPTION
  WHEN TOO_MANY_ROWS THEN
    RAISE EXCEPTION 'Course % not unique', title;

EXCEPTION
  WHEN DIVISION_BY_ZERO THEN
    RAISE NOTICE 'Caught division_by_zero';

EXCEPTION
  WHEN OTHERS
    INSERT INTO AUDIT_TABLE VALUES( current_timestamp, 'Error' );
    RAISE EXCEPTION 'Something went wrong';
END;

 
CREATE OR REPLACE PROCEDURE procedure_name ( parameters ) AS

$$ -- Procedure body separator

DECLARE

 -- optional declarations

BEGIN

  -- executable statements, at least one statements is required

EXCEPTION

  -- optional exception handlers

END;

$$

LANGUAGE plpgsql;

 
CREATE OR REPLACE PROCEDURE phello_world ( hello text,
                                           world text,
                                           OUT message text) AS
$$ 
BEGIN
  message := hello || ' ' || world || ' !';
END;
$$ 
LANGUAGE plpgsql;

 
CALL phello_world('Hello', 'world', NULL);
    message    
---------------
 Hello world !
(1 row)

DECLARE
  course_number COURSE.cnum%TYPE;
  course_title COURSE.title%TYPE;
  course_row COURSE%ROWTYPE;
BEGIN
  course_number := 'CSCI235';
  SELECT title INTO course_title FROM COURSE WHERE cnum = course_number;
  course_row.cnum := 'CSIT115';
  course_row.title := 'Data Management and Security';
  course_row.credits := 6;
  course_row.offered_by := 'Computer Science';
  INSERT INTO COURSE VALUES(course_row.cnum, course_row.title, course_row.credits,
                            course_row.offered_by);
END;

WHILE (condition)
LOOP
  statement;	      
  ...
END LOOP;

LOOP
  statement;	      
  ...
   EXIT WHEN condition;
  statement;	      
  ...
END LOOP;

<<label>> BEGIN
  statement;	      
  ...
   IF condition THEN EXIT label;
  statement;	      
  ...
END;

CREATE OR REPLACE PROCEDURE BCURSOR() AS
$$
DECLARE
  TITLES CURSOR FOR SELECT title FROM COURSE WHERE cnum IN ('CSCI235','CSCI205');
  course_title text;

BEGIN
  OPEN TITLES;
  LOOP
    FETCH TITLES INTO course_title;
    EXIT WHEN NOT FOUND;
    RAISE NOTICE 'Title: %',course_title;
  END LOOP;
CLOSE TITLES;
END;
$$
LANGUAGE plpgsql

 
CALL BCURSOR();

CREATE OR REPLACE PROCEDURE UCURSOR() AS
$$
DECLARE
  course_title text;
  TITLES REFCURSOR;

BEGIN
  OPEN TITLES FOR SELECT title FROM COURSE WHERE cnum IN ('CSCI235','CSCI205');
  LOOP
    FETCH TITLES INTO course_title;
    EXIT WHEN NOT FOUND;
    RAISE NOTICE 'Title: %',course_title;
  END LOOP;
CLOSE TITLES;
END;
$$
LANGUAGE plpgsql;

 
CALL UCURSOR();

 
CREATE OR REPLACE PROCEDURE IBCURSOR() AS
$$
DECLARE
  TITLES CURSOR FOR SELECT title FROM COURSE WHERE cnum IN ('CSCI235','CSCI205');

BEGIN
  FOR trow IN TITLES
  LOOP
    RAISE NOTICE 'Title: %',trow.title;
  END LOOP;
END;
$$
LANGUAGE plpgsql;

 
CALL IBCURSOR();

CREATE OR REPLACE PROCEDURE exception1() AS
$$
DECLARE
  course_title COURSE.title%TYPE;
BEGIN
  SELECT title INTO STRICT course_title FROM COURSE WHERE cnum = 'ABCD123';
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE NOTICE 'Course ABCD123 not found';
END;
$$
LANGUAGE plpgsql;

call exception1();
NOTICE:  Course ABCD123 not found

  
CREATE OR REPLACE PROCEDURE exception2() AS
$$
DECLARE
  course_title COURSE.title%TYPE;
BEGIN
  SELECT title INTO STRICT course_title FROM COURSE;
EXCEPTION
  WHEN TOO_MANY_ROWS THEN
    RAISE NOTICE 'Too many rows found';
END;
$$
LANGUAGE plpgsql;

  
call exception2();
NOTICE:  Too many rows found

CREATE OR REPLACE PROCEDURE exception3() AS $$
DECLARE
  message text;
  stack text;
  i integer := 5;
BEGIN
  i := i/0.0;
EXCEPTION
  WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS message := MESSAGE_TEXT,
                            stack := PG_CONTEXT;
    RAISE NOTICE 'Message text: %', message;
    RAISE NOTICE E'--- Call Stack ---\n%', stack;
END; $$
LANGUAGE plpgsql;

call exception3();
NOTICE:  Message text: division by zero
NOTICE:  --- Call Stack ---
PL/pgSQL function exception3() line 10 at GET STACKED DIAGNOSTICS

  
DO
$$
BEGIN
  IF (SELECT count(*) FROM COURSE) > 0 THEN
    RAISE NOTICE 'COURSE table is not empty';
  END IF;
END;
$$
LANGUAGE plpgsql;

  
DO
$$
DECLARE
 dept_name DEPARTMENT.name%TYPE := 'Computer Science';
 dept_code DEPARTMENT.code%TYPE;
BEGIN
  SELECT name, code
  INTO dept_name, dept_code
  FROM DEPARTMENT
  WHERE name = dept_name;
 
  RAISE NOTICE 'A code of department % is %', dept_name, dept_code;
END;
$$
LANGUAGE plpgsql;