It’s a procedural programming language supported by PG. PostgreSQL: Documentation: 12: Chapter 42. PL/pgSQL - SQL Procedural Language

• Stored procedures use block structure. PL/pgSQL Block Structure

• Encapsulated in DO/END
• Body my be a string literal. That’s what the $$ is for.
• DECLARE section lets you declare variables, their types and optionally default values

DO $$ 
DECLARE
  counter integer := 0;
BEGIN 
  counter := counter + 1;
  RAISE NOTICE 'The current value of counter is %', counter;
END $$;

• Declare a variable with it’s datatype and optional default value
• You can copy the datatype from an existing table column from your database. See city_name example below.
• You can declare constants too, see TAX example below. These can’t be

DO $$ 
DECLARE
  counter    INTEGER := 1;
  first_name VARCHAR(50) := 'John';
  last_name  VARCHAR(50) := 'Doe';
  payment    NUMERIC(11,2) := 20.5;
  city_name  city.name%TYPE := 'San Francisco';
  TAX CONSTANT NUMERIC := 0.075; 
BEGIN 
  -- do stuff
END $$;

PostgreSQL Stored Procedures

• Available in Postgres v11+.
• When doing Large SQL data migrations with batches, it can be good to commit at each iteration of a loop. We cannot COMMIT transactions in PL/pgSQL unless using a stored procedure or function.