plprql

Overview

Version

Build

You can build the RPM / DEB packages for plprql using pig build:

pig build pkg plprql         # build RPM / DEB packages

Install

You can install plprql directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

• Install
• pig
• dnf
• apt

pig install plprql;          # Install for current active PG version

pig ext install -y plprql -v 18  # PG 18
pig ext install -y plprql -v 17  # PG 17
pig ext install -y plprql -v 16  # PG 16
pig ext install -y plprql -v 15  # PG 15
pig ext install -y plprql -v 14  # PG 14

dnf install -y plprql_18       # PG 18
dnf install -y plprql_17       # PG 17
dnf install -y plprql_16       # PG 16
dnf install -y plprql_15       # PG 15
dnf install -y plprql_14       # PG 14

apt install -y postgresql-18-plprql   # PG 18
apt install -y postgresql-17-plprql   # PG 17
apt install -y postgresql-16-plprql   # PG 16
apt install -y postgresql-15-plprql   # PG 15
apt install -y postgresql-14-plprql   # PG 14

Create Extension:

CREATE EXTENSION plprql;

Usage

plprql: Use PRQL in PostgreSQL - Pipelined Relational Query Language

plprql enables writing PostgreSQL functions using PRQL (Pipelined Relational Query Language), a modern language that compiles to SQL with a pipeline syntax.

CREATE EXTENSION plprql;

Create Functions with PRQL

CREATE FUNCTION match_stats(int)
RETURNS TABLE(player text, kd_ratio float) AS $$
  from matches
  filter match_id == $1
  group player (
    aggregate {
      total_kills = sum kills,
      total_deaths = sum deaths
    }
  )
  filter total_deaths > 0
  derive kd_ratio = total_kills / total_deaths
  select { player, kd_ratio }
$$ LANGUAGE plprql;

SELECT * FROM match_stats(42);

Execute PRQL Directly

SELECT prql('from matches | filter player == "Player1"')
  AS (id int, match_id int, player text, kills int)
  LIMIT 2;

Use Cursors for Large Results

SELECT prql('from matches | filter player == "Player1"', 'cursor_name');
FETCH 2 FROM cursor_name;

Inspect Compiled SQL

SELECT prql_to_sql('from matches | filter player == "Player1"');

PRQL Syntax Overview

PRQL uses pipeline transformations:

from employees                    # data source
filter department == "Engineering" # row filtering
derive monthly_salary = salary / 12 # computed columns
sort {-monthly_salary}            # sorting (- for descending)
select {name, monthly_salary}     # column projection
take 10                           # limit rows

Limitations

PRQL only supports SELECT statements. For INSERT, UPDATE, and DELETE, use SQL or PL/pgSQL.