DEV Community

Cover image for PostgreSQL Functions, Must-Knows Quick Start
Diego Sevilla
Diego Sevilla

Posted on

PostgreSQL Functions, Must-Knows Quick Start

#postgres #sql #database #development

As a software developer I bet you spend the majority of time creating functions in different kinds of programming languages like: Typecript, Python, Java, PHP, C# and so on. 💻 That’s terrific! Nevertheless, you can also build functions using PL/pgSQL (Procedural Language/PostgreSQL) in order to encapsulate SQL statements in one place and execute calculations and iterations with data, directly in the DB (data base). Since the syntax is a little different to what developers are used to, this article might be useful and save you some time.

PostgreSQL 🐘 is one popular relational data base management system (RDBMS), which is open-source and has a big community giving support to it. Read More

In this occasion, I will be sharing a brief practical example involving various useful Must-Knows that will help you quick start your development and data management with PostgreSQL functions.

TOPICS COVERED

  • Variables Declaration and Types
  • FOR loops
  • Conditionals
  • Logs
  • Helper Functions
  • Generic Error Handling

QUICK NOTE: in case you are short on time, you can jump directly to the Analysis Section to review the PL/pgSQL function examples and start creating your own functions⏩

Table of Contents

📌Introduction
📌Analysis

📍FIRST - Logs
📍SECOND - Helper functions
📍THIRD - The Main Function

📌Conclusions

Introduction

Let’s say a movie theater manager 🎥 decides to start a discount promotion by giving coupons representing future discounts to existing customers. These coupons most be created for each customer that has watched movies with specific genres. Depending on the genre of the movie watched, the coupon will accumulate more, less or none points. Also, if the movie duration is less than 2 hours (120 min), the coupon will not apply regardless the movie genre.

So having this in mind 💡, here is the test DB:

/**
 * For simplificy purposes, I'm not specifying FOREIGN keys and the creation
 * of more tables Ex. Writer, MovieWriters, Actor, MovieCast, etc.
 * NOTE: replace gen_random_uuid() with uuid_generate_v4() in case you're 
 * using a previous PostgreSQL version. I'm using PostgreSQL 17.0.
 */
CREATE TABLE "Movie"(
 id UUID PRIMARY KEY DEFAULT(gen_random_uuid()),
 "title" VARCHAR(255),
 "duration" NUMERIC,
 "mainDirectorId" UUID,
 "writers"  TEXT, 
 "cast" TEXT 
);
CREATE TABLE "Genre" ( 
 id UUID PRIMARY KEY DEFAULT(gen_random_uuid()),
 "name" VARCHAR(255),
 "pointsApply" BOOLEAN,
 "points" NUMERIC
);
CREATE TABLE "MovieGenre" ( 
 id UUID PRIMARY KEY DEFAULT(gen_random_uuid()),
 "movieId" UUID,
 "genreId" UUID
);
CREATE TABLE "Director" (
 id UUID PRIMARY KEY DEFAULT(gen_random_uuid()),
 "name" VARCHAR(255),
 "otherWorks" TEXT
);
CREATE TABLE "Customer" (
 id UUID PRIMARY KEY DEFAULT(gen_random_uuid()),
 "name" VARCHAR(255),
 "phoneNumber" VARCHAR(255)
);
CREATE TABLE "CustomerWatchedMovie" (
 id UUID PRIMARY KEY DEFAULT(gen_random_uuid()),
 "customerId" UUID,
 "movieId" UUID
);
CREATE TABLE "Coupon" (
 id UUID PRIMARY KEY DEFAULT(gen_random_uuid()),
 "movieId" UUID,
 "customerId" UUID,
 "points" NUMERIC
);
Enter fullscreen mode Exit fullscreen mode

and this is the sample data for our DB:

INSERT INTO "Genre" (name, "pointsApply", "points") VALUES ('Action', TRUE, 15),('Comedy', TRUE, 3),('Drama', TRUE, 4),('Horror', FALSE, 0),('Sci-Fi', TRUE, 10),('Romance', FALSE, 0),('Fantasy', TRUE, 4),('Thriller', FALSE, 0),('Documentary', FALSE, 0),('Adventure', TRUE, 10);
INSERT INTO "Director" (name, "otherWorks") VALUES('Christopher Nolan', 'Inception, The Dark Knight, Interstellar'),('Quentin Tarantino', 'Pulp Fiction, Kill Bill, Django Unchained'),('Steven Spielberg', 'Jurassic Park, E.T., Schindler''s List'),('Martin Scorsese', 'Goodfellas, Taxi Driver, The Irishman'),('James Cameron', 'Titanic, Avatar, Terminator'),('Greta Gerwig', 'Lady Bird, Little Women'),('Jordan Peele', 'Get Out, Us, Nope'),('Ridley Scott', 'Gladiator, Alien, The Martian'),('Wes Anderson', 'The Grand Budapest Hotel, Moonrise Kingdom'),('Denis Villeneuve', 'Blade Runner 2049, Dune, Arrival'),('George Lucas', 'Star Wars, Indiana Jones');
INSERT INTO "Movie" (title, "duration", "mainDirectorId", "writers", "cast") VALUES('Inception', 148, (SELECT id FROM "Director" WHERE name = 'Christopher Nolan'), 'Christopher Nolan', 'Leonardo DiCaprio, Joseph Gordon-Levitt, Ellen Page'),('Pulp Fiction', 149, (SELECT id FROM "Director" WHERE name = 'Quentin Tarantino'), 'Quentin Tarantino', 'John Travolta, Uma Thurman, Samuel L. Jackson'),('Jurassic Park', 127, (SELECT id FROM "Director" WHERE name = 'Steven Spielberg'), 'Michael Crichton', 'Sam Neill, Laura Dern, Jeff Goldblum'),('Goodfellas', 146, (SELECT id FROM "Director" WHERE name = 'Martin Scorsese'), 'Nicholas Pileggi', 'Robert De Niro, Ray Liotta, Joe Pesci'),('Avatar', 162, (SELECT id FROM "Director" WHERE name = 'James Cameron'), 'James Cameron', 'Sam Worthington, Zoe Saldana, Sigourney Weaver'),('Lady Bird', 95, (SELECT id FROM "Director" WHERE name = 'Greta Gerwig'), 'Greta Gerwig', 'Saoirse Ronan, Laurie Metcalf, Tracy Letts'),('Get Out', 104, (SELECT id FROM "Director" WHERE name = 'Jordan Peele'), 'Jordan Peele', 'Daniel Kaluuya, Allison Williams, Bradley Whitford'),('Gladiator', 155, (SELECT id FROM "Director" WHERE name = 'Ridley Scott'), 'David Franzoni', 'Russell Crowe, Joaquin Phoenix, Connie Nielsen'),('The Grand Budapest Hotel', 100, (SELECT id FROM "Director" WHERE name = 'Wes Anderson'), 'Wes Anderson', 'Ralph Fiennes, Tony Revolori, Saoirse Ronan'),('Dune', 155, (SELECT id FROM "Director" WHERE name = 'Denis Villeneuve'), 'Denis Villeneuve', 'Timothée Chalamet, Rebecca Ferguson, Oscar Isaac'),('Star Wars', 121, (SELECT id FROM "Director" WHERE name = 'George Lucas'), 'George Lucas', 'Mark Hamill, Harrison Ford, Carrie Fisher');
INSERT INTO "MovieGenre" ("movieId", "genreId") VALUES((SELECT id FROM "Movie" WHERE title = 'Inception'), (SELECT id FROM "Genre" WHERE name = 'Sci-Fi')),((SELECT id FROM "Movie" WHERE title = 'Pulp Fiction'), (SELECT id FROM "Genre" WHERE name = 'Thriller')),((SELECT id FROM "Movie" WHERE title = 'Jurassic Park'), (SELECT id FROM "Genre" WHERE name = 'Adventure')),((SELECT id FROM "Movie" WHERE title = 'Jurassic Park'), (SELECT id FROM "Genre" WHERE name = 'Drama')),((SELECT id FROM "Movie" WHERE title = 'Goodfellas'), (SELECT id FROM "Genre" WHERE name = 'Drama')),((SELECT id FROM "Movie" WHERE title = 'Avatar'), (SELECT id FROM "Genre" WHERE name = 'Fantasy')),((SELECT id FROM "Movie" WHERE title = 'Lady Bird'), (SELECT id FROM "Genre" WHERE name = 'Drama')),((SELECT id FROM "Movie" WHERE title = 'Get Out'), (SELECT id FROM "Genre" WHERE name = 'Horror')),((SELECT id FROM "Movie" WHERE title = 'Gladiator'), (SELECT id FROM "Genre" WHERE name = 'Action')),((SELECT id FROM "Movie" WHERE title = 'The Grand Budapest Hotel'), (SELECT id FROM "Genre" WHERE name = 'Comedy')),((SELECT id FROM "Movie" WHERE title = 'Dune'), (SELECT id FROM "Genre" WHERE name = 'Sci-Fi')),((SELECT id FROM "Movie" WHERE title = 'Star Wars'), (SELECT id FROM "Genre" WHERE name = 'Sci-Fi')),((SELECT id FROM "Movie" WHERE title = 'Star Wars'), (SELECT id FROM "Genre" WHERE name = 'Adventure'));
INSERT INTO "Customer" (name, "phoneNumber") VALUES('Alice Johnson', '465 101 010'),('Bob Smith', '111 222 333'),('Charlie Brown', '798 444 222'),('Daisy Miller', '432 222 789'),('Edward Green', '899 654 333');
INSERT INTO "CustomerWatchedMovie" ("customerId", "movieId") VALUES ((SELECT id FROM "Customer" WHERE name = 'Alice Johnson'), (SELECT id FROM "Movie" WHERE title = 'Inception')), ((SELECT id FROM "Customer" WHERE name = 'Alice Johnson'), (SELECT id FROM "Movie" WHERE title = 'Lady Bird')), ((SELECT id FROM "Customer" WHERE name = 'Alice Johnson'), (SELECT id FROM "Movie" WHERE title = 'Star Wars')), ((SELECT id FROM "Customer" WHERE name = 'Bob Smith'), (SELECT id FROM "Movie" WHERE title = 'Pulp Fiction')), ((SELECT id FROM "Customer" WHERE name = 'Bob Smith'), (SELECT id FROM "Movie" WHERE title = 'Get Out')), ((SELECT id FROM "Customer" WHERE name = 'Charlie Brown'), (SELECT id FROM "Movie" WHERE title = 'Jurassic Park')), ((SELECT id FROM "Customer" WHERE name = 'Charlie Brown'), (SELECT id FROM "Movie" WHERE title = 'Gladiator')), ((SELECT id FROM "Customer" WHERE name = 'Daisy Miller'), (SELECT id FROM "Movie" WHERE title = 'Goodfellas')), ((SELECT id FROM "Customer" WHERE name = 'Daisy Miller'), (SELECT id FROM "Movie" WHERE title = 'The Grand Budapest Hotel')), ((SELECT id FROM "Customer" WHERE name = 'Edward Green'), (SELECT id FROM "Movie" WHERE title = 'Avatar')), ((SELECT id FROM "Customer" WHERE name = 'Edward Green'), (SELECT id FROM "Movie" WHERE title = 'Dune'));
Enter fullscreen mode Exit fullscreen mode

The INSERTs will basically do the following:

  1. Insert 10 genres, movies and directors
  2. Insert 5 customers
  3. Link genres and movies respectively
  4. Add at least 2–3 watched movies per customer

Analysis

Ok, awesome! 💯 Now that we have the data ready, let's review our main goal:

Create coupons for each customer that has watched movies with specific genres. Points will accumulate according to the movie genre and no coupon will be added if the duration is less than 2 hours.

This is the syntax for a PL/pgSQL function:

-- $$: opens and closes the body of the function
CREATE OR REPLACE FUNCTION "functionOne"(param1 VARCHAR, param2 INTEGER)
  RETURNS VARCHAR
  LANGUAGE plpgsql
  AS 
$$
DECLARE
  -- variables declaration here
BEGIN
  -- function logic here
RETURN 'Done.';
END;
$$;
Enter fullscreen mode Exit fullscreen mode

FIRST - Logs

Depending on the amount of data and complexity of calculations, functions can take several minutes to complete. For debugging purposes, it's recommended to add info console logs which will let you know the progress of the function overall. However, for a post data analysis, it's important to consider a more robust logs approach, so that when the function finishes you have a way to verify the results:

-- Console log in PL/pgSQL
RAISE NOTICE 'Movie: % Customer: %', movieName, customerName;
Enter fullscreen mode Exit fullscreen mode 
-- Will store the logs of the backfill process 
CREATE TABLE "CouponBackfillHistory"(
 id UUID PRIMARY KEY DEFAULT(gen_random_uuid()),
 "movieId" UUID,
 "movieGenreId" UUID,
 "customerId" UUID,
 "accumulatedPoints" NUMERIC,
 "couponCreatedSuccessfully" BOOLEAN, -- indicator
 "notes" TEXT -- info regarding coupon insertion
);
Enter fullscreen mode Exit fullscreen mode

SECOND - Helper functions

Since we need to iterate in Movies, Customers, Genres and add Coupons depending on what it's found, we need to reuse code:

-- Helper function to insert into CouponBackfillHistory table
CREATE OR REPLACE FUNCTION "insertCouponBackfillHistory"(
 movieId UUID,
 movieGenreId UUID,
 customerId UUID,
 accumulatedPoints NUMERIC,
 couponCreatedSuccessfully BOOLEAN,
 notes TEXT
) 
 RETURNS VOID -- will not return any value
 LANGUAGE plpgsql 
 AS 
$$
BEGIN
 INSERT INTO "CouponBackfillHistory" 
  ("movieId", "movieGenreId", "customerId", "accumulatedPoints",  "couponCreatedSuccessfully", "notes")
    VALUES 
  (movieId, movieGenreId, customerId, accumulatedPoints,  couponCreatedSuccessfully, notes);
END;
$$;

-- Helper function to handle generic errors
-- Ref: https://www.postgresql.org/docs/current/plpgsql-errors-and-messages.html
CREATE OR REPLACE FUNCTION "handleGenericError"(pgSqlState TEXT, pgSqlErrMsg TEXT, pgFunctionName TEXT)
 RETURNS VOID -- will not return any value
 LANGUAGE plpgsql
 AS 
$$
BEGIN
   RAISE EXCEPTION USING 
    ERRCODE = pgSqlState,
    MESSAGE = pgSqlErrMsg,
    HINT = pgSqlErrMsg || ' in function ' || pgFunctionName;
END;
$$;
Enter fullscreen mode Exit fullscreen mode

THIRD - The Main Function 

CREATE OR REPLACE FUNCTION "backfillCoupons"(customerName VARCHAR(255)) 
 RETURNS VARCHAR 
 LANGUAGE plpgsql
 AS 
$$
DECLARE
 customers RECORD; -- Iterator variables
 movies RECORD; 
 genreName VARCHAR(255); -- Other variables
 genrePoints INTEGER;
 genrePointsApply BOOLEAN;
 movieDuration INTEGER; 
 pointsCnt INTEGER := 0; -- Counters
 recordsCreated INTEGER := 0;
BEGIN
 RAISE NOTICE '----------------- START ------------------';
 RAISE NOTICE 'Customer: %', customerName;
 FOR customers IN -- Customer records loop
  SELECT c.id, c.name, cwm."movieId" watched_movie_id
  FROM "Customer" c 
  JOIN "CustomerWatchedMovie" cwm ON cwm."customerId" = c.id
  WHERE c.name = customerName
 LOOP
  pointsCnt := 0;
  FOR movies IN -- Movie records loop
   SELECT DISTINCT m.id, m.title, m.duration, mg."genreId" genre_id,
   m.writers, m.cast, d.name director_name
   FROM "Movie" m
   JOIN "Director" d on d.id = m."mainDirectorId"
   JOIN "MovieGenre" mg on mg."movieId" = m.id 
   WHERE m.id = customers.watched_movie_id
  LOOP
   RAISE NOTICE 'Movie: % Id: %', movies.title, movies.id;
   BEGIN
    SELECT g.name, g."pointsApply", g.points 
    INTO genreName, genrePointsApply, genrePoints
    FROM "Genre" g
    WHERE g.id = movies.genre_id;

    IF genrePointsApply = FALSE THEN
     RAISE NOTICE 'Points do not apply for Genre';
     PERFORM "insertCouponBackfillHistory"(movies.id,movies.genre_id, customers.id, 0, FALSE, 'Points do not apply for Genre');
     CONTINUE;
    ELSIF  movies.duration < 120 THEN
     RAISE NOTICE 'Movie duration is not equal or greater than 2 hours';
     PERFORM "insertCouponBackfillHistory"(movies.id, movies.genre_id, customers.id, genrePoints, FALSE, 'Movie duration is not equal or greater than 2 hours');
     CONTINUE;
    END IF;

    pointsCnt := pointsCnt + genrePoints;
    INSERT INTO "Coupon" ("movieId", "customerId", "points") 
    VALUES (movies.id,customers.id,genrePoints);
    PERFORM "insertCouponBackfillHistory"(movies.id, movies.genre_id, customers.id, genrePoints, TRUE, 'Coupon created succesfully');
    recordsCreated := recordsCreated +1;

    EXCEPTION --Handle unexpected errors
     WHEN OTHERS THEN
      PERFORM "handleGenericError"(SQLSTATE, SQLERRM, 'backfillCoupons');
   END;
  END LOOP; -- End Movie records loop
  RAISE NOTICE 'Total Points Accumulated: %', pointsCnt;
 END LOOP; --End Customer records loop
RAISE NOTICE 'Total number of coupons created: %', recordsCreated;
RAISE NOTICE 'Check CouponsBackfillHistory for details';
RETURN 'Success.';
END;
$$;
Enter fullscreen mode Exit fullscreen mode

The function is simple, but looks wild at first sight 🍃🐯 haha let's break it down. It basically performs the following actions:

  1. Iterate in one Customer
  2. Iterate in each Movie watched by the Customer
  3. Verify the Genre points and the Movie duration
  4. Add a Coupon or jump to the next iteration depending on the conditions

Side note: the SELECT.. INTO.. query could have been omitted and included in a JOIN clause in the previous query, but was added for DEMO purposes.

Execute the function:

-- for all customers
SELECT "backfillCoupons"(name) FROM "Customer"; 

-- for one customer
SELECT "backfillCoupons"(name) FROM "Customer" WHERE c.name = 'Daisy Miller';
Enter fullscreen mode Exit fullscreen mode

Logs for Daisy Miller:

----------------- START ------------------
Customer: Daisy Miller
Movie: Goodfellas Id: 487e3227-f6f0-4faf-8011-13dfa7abcc77
Total Points Accumulated: 4
Movie: The Grand Budapest Hotel Id: 3bd9fd29-f593-4b1f-8904-59456918e41a
Movie duration is not equal or greater than 2 hours
Total Points Accumulated: 0
Total number of coupons created: 1
Check CouponsBackfillHistory for details
Enter fullscreen mode Exit fullscreen mode

Now let's execute a query to see the records:

-- Query 1
SELECT m.title "movieTitle", g."name" genre, cbh."accumulatedPoints", cbh."couponCreatedSuccessfully", cbh.notes
FROM "CouponBackfillHistory" cbh
JOIN "Movie" m ON m.id = cbh."movieId"
JOIN "Genre" g ON g.id = cbh."movieGenreId";
Enter fullscreen mode Exit fullscreen mode

Image Showing the results of Query 1

The coupon created:

-- Query 2
SELECT c.id, m.title "movieTitle", g."name" genre, cu.name, c.points
FROM "Coupon" c
JOIN "Movie" m ON m.id = c."movieId"
JOIN "MovieGenre" mg ON mg."movieId" = m.id
JOIN "Genre" g ON g.id = mg."genreId"
JOIN "Customer" cu ON cu.id = c."customerId";
Enter fullscreen mode Exit fullscreen mode

Image Showing the results of Query 2

Important things to keep in mind:

  1. Use "" to use camelCase instead of snake_case in PostgreSQL.
  2. Init all needed variables in the DECLARE section
  3. FOR LOOPs and SELECT.. INTO.. statements depend on previous declared variables to work
  4. Organize the code inside your function with BEGIN.. END; statements
  5. Use SELECT DISTINCT.. to avoid duplicate rows
  6. No changes will be committed to the DB unless the function completes
  7. EXCEPTIONS will stop the execution of the function
  8. Use CONTINUE; statement to jump to the next iteration in a loop

Conclusions

  1. Getting familiarized with PL/pgSQL functions, is a nice ability that will help you automate SQL statements, iterate in complex data and execute operations depending on certain conditions.
  2. Always consider a robust logs approach when creating any kind of function involving data changes.
  3. Take into consideration the time you have and decide whether to use PL/pgSQL or implement a PostgreSQL connector with your preferred programming language for a better debugging experience.

PostgreSQL has lots of docs and examples throughout the internet. Checkout these references, I consider very helpful, for further reading:

My favorite DB IDE: DBeaver Community
Happy Coding! 😃💻

Top comments (0)