Home

Getting started

Postgres basics

Postgres guides

Postgres best practices

Postgres extensions

Benchmarks

Overview

PostgreSQL Extensions: amcheck

PostgreSQL Extensions: Unlocking Multidimensional Points With Cube

PostgreSQL Extensions: hstore

PostgreSQL Extensions: ltree

PostgreSQL Extensions: Secure Your Time-Series Data With pgcrypto

PostgreSQL Extensions: pg_prewarm

PostgreSQL Extensions: pgRouting

PostgreSQL Extensions: pg_stat_statements

PostgreSQL Extensions: Database Testing With pgTAP

PostgreSQL Extensions: Install pg_trgm for Data Matching

PostgreSQL Extensions: Turning PostgreSQL Into a Vector Database With pgvector

PostgreSQL Extensions: PL/pgSQL

PostgreSQL Extensions: Using PostGIS and Timescale for Advanced Geospatial Insights

Beyond the Basics: Exploring PostgreSQL Extensions

PostgreSQL Extensions: Intro to uuid-ossp

Extensibility

PostgreSQL Extensions: PL/pgSQL

PL/pgSQL is a loadable procedural language for the PostgreSQL database system. It allows developers to write complex logic and functions in a more powerful and flexible language than standard SQL.

Let’s learn how to install it and use it.

What Is PL/pgSQL?

PL/pgSQL is a block-structured language that provides control structures such as loops and conditionals, as well as complex data types and other programming language features. It is particularly useful for tasks requiring complex computation or not easily accomplished with standard SQL.

Installing the PL/pgSQL Extension

Before you can use PL/pgSQL, you must install it. Here's how:

1. Connect to the PostgreSQL database where you want to install the extension. On Timescale, you can find available extensions by going to Operations > Extensions from your service overview, which will also give you installation instructions.

2. Run the following SQL command:

CREATE EXTENSION IF NOT EXISTS plpgsql;

This command installs the PL/pgSQL extension if it is not already installed.

Using the PL/pgSQL Extension

To use PL/pgSQL, you write functions in the PL/pgSQL language and then call them from your SQL queries. Here is an example of a simple PL/pgSQL function:

CREATE FUNCTION add_numbers(integer, integer) RETURNS integer
AS $$
BEGIN
RETURN $1 + $2;
END;
$$ LANGUAGE plpgsql;

This function takes two integers as input and returns their sum. You can call this function from a SQL query like this:

SELECT add_numbers(5, 3);

Time-Series Use Cases for the PL/pgSQL Extension

PL/pgSQL is particularly useful for time-series data, where you often need to perform complex calculations over a series of data points. For example, you might use PL/pgSQL to calculate moving averages, perform time-series forecasting, or detect anomalies in your data.

Using PL/pgSQL With Timescale and Time-Series Data

If you're using Timescale for time-series data, you can use PL/pgSQL to write complex queries and calculations. For example, you might use PL/pgSQL to write a function that calculates the moving average of a time-series data set:

CREATE OR REPLACE FUNCTION moving_average(time_interval INTERVAL)
RETURNS TABLE (“time” TIMESTAMPTZ, avg DOUBLE PRECISION)
AS $$
DECLARE
   start_time TIMESTAMPTZ;
   end_time TIMESTAMPTZ;
BEGIN
   FOR start_time, end_time IN
   SELECT time_bucket(time_interval, time) AS start_time,
time_bucket(time_interval, time) + time_interval AS end_time
FROM my_table
 LOOP
      RETURN QUERY
      SELECT start_time, AVG(value)
      FROM my_table
      WHERE time >= start_time AND time < end_time;
   END LOOP;
END;
$$ LANGUAGE plpgsql;

This function calculates the moving average of the value column in my_table, using a sliding window of size time_interval. Below is an example test setup for the moving_average function:

1. First, we'll create a dummy table my_table with some sample data.

2. Then, we'll call your moving_average function with a specified time interval.

3. Lastly, we'll query the result to observe the computed moving averages.

-- 1. Setup: Create the my_table and insert some sample data
DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table (
    time TIMESTAMPTZ,
    value DOUBLE PRECISION
);

-- Let's insert some sample data. Imagine this data represents some sensor readings taken every 10 minutes.
INSERT INTO my_table (time, value) VALUES
('2023-08-21 08:00:00', 5),
('2023-08-21 08:10:00', 6),
('2023-08-21 08:20:00', 5.5),
('2023-08-21 08:30:00', 6.5),
('2023-08-21 08:40:00', 7),
('2023-08-21 08:50:00', 6.5);


-- 2. Test: Call the moving_average function for a 30-minute time interval.
-- This will average the values over each 30-minute period.
SELECT * FROM moving_average('30 minutes'::INTERVAL) GROUP BY 1, 2 ORDER BY 1 ;
-- The result should be:
-- '2023-08-21 08:00:00', 5.5   (Average of 5, 6, 5.5)
-- '2023-08-21 08:30:00', 6.67 (Average of 6.5, 7, 6.5)

The test setup above clearly shows how to utilize the moving_average function. You'll be able to observe the computed moving averages for the given sample data and the specified time interval.

Learn More

Timescale is a cloud-native, high-performance database that is not only built on PostgreSQL—it works and feels just like PostgreSQL but provides great scalability. Learn here why Timescale is the database for time-series data and how you can scale it infinitely.