Skip to content

Latest commit

 

History

History
59 lines (51 loc) · 1.99 KB

20200121141321-sql_procedural_language.org

File metadata and controls

59 lines (51 loc) · 1.99 KB
Raw

SQL Procedural Language

SQL Procedural Language

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

Block Structure

Basic Example of 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

  • 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 $$;

Stored Procedure

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.