---
title: "PostgreSQL Extensions: PL/pgSQL"
description: "PL/pgSQL allows developers to write complex logic and functions in a more powerful and flexible language than standard SQL. Learn more about this extension."
section: "Postgres extensions"
---

> **TimescaleDB is now Tiger Data.**

# 

`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](https://console.cloud.timescale.com/signup), 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 [<u>time-series data</u>](https://www.timescale.com/learn/do-you-have-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 [*<u>the</u>*<u> database for time-series data</u>](https://www.timescale.com/blog/what-is-a-time-series-database/) and [<u>how you can scale it infinitely</u>](https://www.timescale.com/blog/scaling-postgresql-with-amazon-s3-an-object-storage-for-low-cost-infinite-database-scalability/).