Published at Mar 6, 2024
ExtensibilityPL/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.
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.
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.
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);
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.
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.
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.