Mastering PostgreSQL 10

Managing scopes

After dealing with quoting and basic security (SQL injection) in general, I want to shift your focus to another important topic: scopes.

Just like most popular programming languages I am aware of, 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, a variable i 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. ...