Mastering PostgreSQL 12 - Third Edition

Managing scopes

After dealing with quoting and basic security (SQL injection) in general, we will focus on another important topic: scopes.

Just like most popular programming languages, PL/pgSQL uses variables, depending on their context. Variables are defined in the DECLARE statement of a function. However, PL/pgSQL allows you to nest a DECLARE statement:

CREATE FUNCTION scope_test () RETURNS int AS 
$$ 
DECLARE 
  i int := 0; 
BEGIN 
  RAISE NOTICE 'i1: %', i; 
  DECLARE 
    i int; 
    BEGIN 
      RAISE NOTICE 'i2: %', i; 
    END; 
  RETURN i; 
END; 
$$ LANGUAGE 'plpgsql'; 

In the DECLARE statement, the i variable is defined and a value is assigned to it. Then, i is displayed. The output will, of course, be 0. Then, a second DECLARE statement starts. It contains an additional ...