PLPRQL is an exciting PostgreSQL extension that allows you to write functions using PRQL (Pipelined Relational Query Language). This extension makes data manipulation and queries easier and more intuitive, especially for PostgreSQL versions 12-16 on Linux and macOS. In this blog, we’ll guide you through the basics of using PRQL in PostgreSQL and getting started with PLPRQL.

What is PRQL?

PRQL is an open-source query language designed for data manipulation and analysis that compiles to SQL. By utilizing a pipeline concept akin to Unix pipes, PRQL transforms data line-by-line. This sequential transformation minimizes the complexity associated with nested SQL queries, thereby simplifying the writing process and enhancing readability.

Key Features of PLPRQL

• Write functions with PRQL: Perfect for large analytical queries.
• PRQL compiler: Ideal for development and debugging.
• Inline execution: Beneficial for prototyping and executing custom queries in Object Relational Mappers (ORMs).

Functions in PRQL

Complex SQL queries can be a nightmare to manage. PRQL shines in this context, allowing you to encapsulate lengthy SQL queries into manageable PRQL functions. Here’s an analogy to make this clear: imagine you have a recipe with various steps. Instead of writing the entire list of ingredients and instructions every time, you can simply refer to a well-written recipe by name.

For instance, let’s say you want to create a function that retrieves match statistics:

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;

Then you can retrieve match stats for a specific match like this:

select * from match_stats(1001);

This approach neatly encapsulates your complex query within a function. The output will display the player’s name along with their kill/death ratio, making it easier to analyze data without dealing with convoluted SQL directly.

PRQL Compiler

To see what SQL statements are being executed under the hood, you can invoke the PRQL compiler by using the function prql_to_sql(). This feature helps with debugging and understanding the transformation of PRQL into SQL:

select prql_to_sql(...);

From the previous example, invoking the compiler would give you the resulting SQL:

WITH table_0 AS (  
    SELECT player, COALESCE(SUM(kills), 0) AS _expr_0, COALESCE(SUM(deaths), 0) AS _expr_1  
    FROM matches  
    WHERE match_id = $1  
    GROUP BY player
)  
SELECT player, _expr_0 / _expr_1 AS kd_ratio  
FROM table_0  
WHERE _expr_1 > 0;

Inline Execution

Inline execution enables you to run PRQL code directly. For example:

select prql(from matches filter player == 'Player1') as ( 
    id int, match_id int, round int, player text, kills int, deaths int 
) limit 2;

This is particularly useful for custom queries within ORMs, providing rapid prototyping capabilities.

Getting Started with Installation

Ready to dive into PLPRQL? Here’s how to get started:

Installing PLPRQL

Using a .deb File

1. Download the .deb file suitable for your operating system from the Releases page.
2. In the terminal, navigate to the directory containing the downloaded file and execute:

   sudo dpkg -i plprql-0.1.0-postgresql-16-debian-bookworm-amd64.deb

3. If any missing dependencies are reported, run:

   sudo apt-get install -f

Using the Shell Script

Install essential prerequisites and execute the following shell script:

curl --proto =https --tlsv1.2 -sSf https://raw.githubusercontent.com/kaspermarstal/plprql/main/scripts/install.sh | bash

Troubleshooting

If you encounter any issues during installation, here are a few troubleshooting steps:

• Ensure that PostgreSQL is installed and running.
• Check for compatibility between the PLPRQL version and your PostgreSQL installation.
• Confirm that all necessary dependencies are installed.

For more insights, updates, or to collaborate on AI development projects, stay connected with fxis.ai.

Conclusion

At fxis.ai, we believe that such advancements are crucial for the future of AI, as they enable more comprehensive and effective solutions. Our team is continually exploring new methodologies to push the envelope in artificial intelligence, ensuring that our clients benefit from the latest technological innovations.