SQL Functions

PostgreSQL functions, are also known as Stored Procedures. They allow you to carry out operations that would normally take several queries and round trips in a single function within the database. Functions allow database reuse as other applications can interact directly with your stored procedures instead of a middle-tier or duplicating code. let's see how to count records between two timestamps in PostgreSQL with a custom function.

Counting Records Between Two Timestamps in PostgreSQL with a Custom Function

Function Definition

CREATE OR REPLACE FUNCTION count_btween_timestamps (
    trans_table text,
    start_date text,
    end_date text
) RETURNS integer AS $total$
DECLARE
    total integer;
BEGIN
    EXECUTE format(
        'SELECT count(*) FROM %I WHERE inserted_at > %L AND inserted_at < %L',
        trans_table, start_date, end_date
    )
    INTO total;

    RETURN total;
END;
$total$ LANGUAGE plpgsql;

PostgreSQL allows you to define your own functions using the CREATE FUNCTION statement. This is extremely useful when you want to encapsulate reusable logic — like counting records between two timestamps. Here's a breakdown of how the count_btween_timestamps function works and what each part does.

Let’s Break It Down

  • function-name Specifies the name of the function — in this case, count_btween_timestamps.

  • [OR REPLACE] Allows you to modify the function if it already exists without needing to drop and recreate it.

  • RETURN clause Indicates the type of data that the function will return. Here, it returns an integer, which is the count of rows between the two dates.

  • function-body Contains the main logic. This is where we:

    • Build a dynamic SQL query using format(...)
    • Execute the query using EXECUTE
    • Store the result in the total variable
    • Return the total

  • AS Used to begin the function body. $total$ is a dollar-quoted string delimiter — you can name it anything, but it must match at the start and end of the body.

  • LANGUAGE plpgsql Specifies the language used to implement the function. plpgsql is PostgreSQL's procedural language, which allows for more complex logic than plain SQL.

Using the Function

Now that we've defined the function, we can use it in our queries. Here's an example of how to use the count_btween_timestamps function:

SELECT count_btween_timestamps('transactions', '2022-01-01', '2022-01-31');

This will return the number of rows in the transactions table where the inserted_at timestamp is after April 1st, 2022 and before May 1st, 2022.

By encapsulating this logic in a function, you get a clean, reusable way to perform timestamp-based filtering across different tables.

Back to code!