It’s no surprise Postgres does more than merely store data; you can’t do everything with SQL. Often, it’s often more beneficial to process data locally without transmitting it to a client interface. Local manipulation can save hours in network traffic alone, let alone client-side allocation and per-row processing. Databases like Postgres are specifically for bulk data operations, so why not take advantage?

But doing that requires functions—anonymous or otherwise—and a Turing-complete language to write them with. For data-hungry jobs that benefit from local evaluation, Postgres has a veritable smorgasbord of procedural languages ready to sate almost any coding appetite. But where do we start?

Tightly integrating Postgres with a project is getting easier with each release. So reasonable in fact, we’re liable to forget that Postgres has its own fairly capable language in PL/pgSQL. Let’s work with Postgres’ free language a bit and explore its functionality, shall we?

Just so we have something to manipulate, let’s start by defining a basic table and associated data:

CREATE TABLE sensor_log (
  id            SERIAL PRIMARY KEY NOT NULL,
  location      VARCHAR NOT NULL,
  reading       BIGINT NOT NULL,
  reading_date  TIMESTAMP NOT NULL
);
 
INSERT INTO sensor_log (location, reading, reading_date)
SELECT s.id % 100, s.id % 100,
       CURRENT_DATE + INTERVAL '8h' 
                    - (s.id || 's')::INTERVAL
  FROM generate_series(1, 500) s(id);
 
ANALYZE sensor_log;

With that out of the way, let’s start with a short history lesson. It’s hard to ignore the blocky nature of SQL statement organization. This is almost a natural consequence of a language saddled with such high verbosity and numerous ubiquitous keywords. Each SQL statement becomes a series of high-level directives led by grouped operations, making contextual break-points for each. So we end up seeing SELECT ... FROM ... WHERE ... GROUP BY ... operational blocks.

Some language nerds might recognize this cadence. To make it a bit more obvious, here’s a very basic Postgres PL/pgSQL function:

CREATE OR REPLACE FUNCTION f_add_nums(x INT, y INT)
RETURNS INT 
LANGUAGE plpgsql AS
$BODY$
DECLARE
    ret_val INT;
BEGIN
    ret_val := x + y;
    RETURN ret_val;
END;
$BODY$;

There’s no coincidence that this highly resembles Oracle’s PL/SQL. But Oracle was inspired by Ada, a language developed for use by the US government. Ada itself is highly influenced by Pascal. With this kind of pedigree and a very similar block-style approach, it shouldn’t be surprising so many database languages feel similar.

With that in mind, Postgres does take advantage of many of its own syntax choices within this realm, either to simplify statements or provide better integration with the core. For example, there are essentially two ways to loop through a SQL statement within a procedure:

-- First, using an explicit cursor:
 
CREATE OR REPLACE FUNCTION f_process_log()
RETURNS INT 
LANGUAGE plpgsql AS
$BODY$
DECLARE
    ret_val BIGINT := 0;
    log_record RECORD;
 
    log_results CURSOR FOR
        SELECT reading
          FROM sensor_log;
BEGIN
    FOR log_record IN log_results LOOP
      ret_val := ret_val + log_record.reading;
    END LOOP;
 
    RETURN ret_val;
END;
$BODY$;
 
-- Next, with an implicit cursor as a shortcut:
 
CREATE OR REPLACE FUNCTION f_process_log()
RETURNS INT 
LANGUAGE plpgsql AS
$BODY$
DECLARE
    ret_val BIGINT := 0;
    read_value BIGINT;
BEGIN
    FOR read_value IN
        SELECT reading
          FROM sensor_log
    LOOP
      ret_val := ret_val + read_value;
    END LOOP;
 
    RETURN ret_val;
END;
$BODY$;

Of these two approaches, the second is likely preferred by actual human beings. The cursor is still there, but exists ephemerally, doing its magic in the background. In using the explicit cursor, we have to ensure we haven’t opened it previously in the function, and addressing the results is a bit more cumbersome.

What about conditionals based on query results? Again, there are both hard and easy ways of going about this:

-- First, using a variable as an outright result:
 
CREATE OR REPLACE FUNCTION f_check_sensor(sensor_loc TEXT)
RETURNS INT 
LANGUAGE plpgsql AS
$BODY$
DECLARE
    ret_val BIGINT;
BEGIN
    ret_val := (SELECT id
                  FROM sensor_log
                 WHERE location = sensor_loc
                 LIMIT 1);
 
    IF ret_val IS NULL THEN
        RETURN FALSE;
    END IF;
 
    RETURN TRUE;
END;
$BODY$;
 
-- Next, using built-in syntax:
 
CREATE OR REPLACE FUNCTION f_check_sensor(sensor_loc TEXT)
RETURNS BOOLEAN 
LANGUAGE plpgsql AS
$BODY$
BEGIN
    PERFORM id
       FROM sensor_log
      WHERE location = sensor_loc
      LIMIT 1;
 
    IF NOT FOUND THEN
        RETURN FALSE;
    END IF;
 
    RETURN TRUE;
END;
$BODY$;

This time we used the PERFORM syntax instead of SELECT. It’s one of many available while relying on PL/pgSQL. It’s meant for times when we don’t explicitly require query results, but still need to execute a query. Once again, we leveraged implicit-style syntax to simplify the code (and our thought process).

This also extends into exception handling. We’ve already covered exception performance considerations, but there are also cool syntax tricks available. Imagine we want to process all log readings for the current day, raising a warning when certain boundaries are violated.

Watch what happens if we just use a naked exception:

CREATE OR REPLACE FUNCTION f_process_current_log()
RETURNS VOID 
LANGUAGE plpgsql AS
$BODY$
DECLARE
    log_rec RECORD;
BEGIN
    FOR log_rec IN
        SELECT location, reading, reading_date
          FROM sensor_log
         WHERE reading_date >= CURRENT_DATE
    LOOP
        -- Imagine this is some complicated procedure. Maybe we
        -- call a function, for instance, and consider a bad
        -- result or a passed exception as fatal.
 
        IF log_rec.reading > 98 THEN
            RAISE EXCEPTION 'location % reading % invalid at %!',
                  log_rec.location, log_rec.reading,
                  log_rec.reading_date;
        END IF;
    END LOOP;
END;
$BODY$;
 
SELECT f_process_current_log();
 
ERROR:  location 99 reading 99 invalid at 2016-09-02 07:58:21!

Well, we built our fake data, so we know for a fact that there should be five errors returned, but processing stopped right away. We could reduce the exception to a warning to prevent this, yet we can’t always count on downstream functions on being so forgiving. If this function invokes several other sub-functions, we’ll eventually get a fatal exception that will derail the entire processing loop. How can we prevent this?

Perhaps not so obvious, but Postgres blocks can go pretty much anywhere. Here’s the same function without its fatal processing issue:

CREATE OR REPLACE FUNCTION f_process_current_log()
RETURNS VOID 
LANGUAGE plpgsql AS
$BODY$
DECLARE
    log_rec RECORD;
BEGIN
    FOR log_rec IN
        SELECT location, reading, reading_date
          FROM sensor_log
         WHERE reading_date >= CURRENT_DATE
    LOOP
        -- Imagine this is some complicated procedure. Maybe we
        -- call a function, for instance, and consider a bad
        -- result or a passed exception as fatal.
 
        BEGIN
            IF log_rec.reading > 98 THEN
                RAISE EXCEPTION 'location % reading % invalid at %!',
                      log_rec.location, log_rec.reading,
                      log_rec.reading_date;
            END IF;
        EXCEPTION
            WHEN OTHERS THEN
                RAISE NOTICE '%', SQLERRM;
        END;
    END LOOP;
END;
$BODY$;
 
SELECT f_process_current_log();
 
NOTICE:  location 99 reading 99 invalid at 2016-09-02 07:58:21!
NOTICE:  location 99 reading 99 invalid at 2016-09-02 07:56:41!
NOTICE:  location 99 reading 99 invalid at 2016-09-02 07:55:01!
NOTICE:  location 99 reading 99 invalid at 2016-09-02 07:53:21!
NOTICE:  location 99 reading 99 invalid at 2016-09-02 07:51:41!

By wrapping the “dangerous” external call in another block, we were able to trap and override the exception it raised. This is effectively the same as a try/catch block in other languages, but may be potentially confusing because of its similarity to the outer block syntax. Regardless, PL/pgSQL has most of the trappings of a modern language, even if some appear a bit antiquated by current standards.

The idea with PL/pgSQL is that it highly resembles the SQL language itself, and allows directly embedding SQL statements. Within a database context, this is a massive time saver for native SQL speakers. Consider the mechanism for enabling a function to return row results:

CREATE OR REPLACE FUNCTION f_generate_and_return(new_count INT)
RETURNS SETOF sensor_log 
LANGUAGE plpgsql AS
$BODY$
DECLARE
    n_count INT := 0;
    new_row RECORD;
BEGIN
    FOR n_count IN 1..new_count
    LOOP
        INSERT INTO sensor_log (location, reading, reading_date)
        SELECT n_count, n_count % 100, now()
        RETURNING * INTO new_row;
 
        RETURN NEXT new_row;
    END LOOP;
END;
$BODY$;
 
SELECT * FROM f_generate_and_return(5);
 
 id  | location | reading |        reading_date        
-----+----------+---------+----------------------------
 501 | 1        |       1 | 2016-09-02 13:39:33.566244
 502 | 2        |       2 | 2016-09-02 13:39:33.566244
 503 | 3        |       3 | 2016-09-02 13:39:33.566244
 504 | 4        |       4 | 2016-09-02 13:39:33.566244
 505 | 5        |       5 | 2016-09-02 13:39:33.566244

How about that? Not only were we able to insert new rows, but we fetched them to immediately verify their presence. By using the SETOF decorator, we told Postgres that the function would return the declared result set. We could do this for any existing table or view within the database, or we could define our own type for special cases. In essence, we are transforming our function into a reentrant version with a persistent stack, and all that implies. Yet everything still resembles SQL, not some other language that happens to support SQL through function calls or other convoluted contortions.

Now, this is an extremely trivial application of this capability. In fact, we highly recommend against looping inserts, but some complicated business logic or external function calls might demand this, or a similar level of expensive processing. Try to consider this as a demonstration, rather than a bible for best practices.

In any case, we hope Pl/pgSQL becomes part of your everyday lexicon!

PG Phriday: Cult of Functionality
Tagged on:             

One thought on “PG Phriday: Cult of Functionality

Comments are closed.