https://github.com/kaspermarstal/plprql Skip to content Toggle navigation Sign in * Product + Actions Automate any workflow + Packages Host and manage packages + Security Find and fix vulnerabilities + Codespaces Instant dev environments + Copilot Write better code with AI + Code review Manage code changes + Issues Plan and track work + Discussions Collaborate outside of code Explore + All features + Documentation + GitHub Skills + Blog * Solutions For + Enterprise + Teams + Startups + Education By Solution + CI/CD & Automation + DevOps + DevSecOps Resources + Learning Pathways + White papers, Ebooks, Webinars + Customer Stories + Partners * Open Source + GitHub Sponsors Fund open source developers + The ReadME Project GitHub community articles Repositories + Topics + Trending + Collections * Pricing Search or jump to... Search code, repositories, users, issues, pull requests... Search [ ] Clear Search syntax tips Provide feedback We read every piece of feedback, and take your input very seriously. [ ] [ ] Include my email address so I can be contacted Cancel Submit feedback Saved searches Use saved searches to filter your results more quickly Name [ ] Query [ ] To see all available qualifiers, see our documentation. Cancel Create saved search Sign in Sign up You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session. You switched accounts on another tab or window. Reload to refresh your session. Dismiss alert {{ message }} kaspermarstal / plprql Public * Notifications * Fork 2 * Star 211 * Use PRQL in PostgreSQL prql-lang.org/ License Apache-2.0 license 211 stars 2 forks Branches Tags Activity Star Notifications * Code * Issues 0 * Pull requests 0 * Actions * Projects 0 * Security * Insights Additional navigation options * Code * Issues * Pull requests * Actions * Projects * Security * Insights kaspermarstal/plprql This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository. main BranchesTags Go to file Code Folders and files Name Name Last commit message Last commit date Latest commit History 42 Commits .cargo .cargo .github .github plprql-tests plprql-tests plprql plprql scripts scripts .dockerignore .dockerignore .gitignore .gitignore Cargo.lock Cargo.lock Cargo.toml Cargo.toml DESIGN.md DESIGN.md LICENSE LICENSE README.md README.md rustfmt.toml rustfmt.toml View all files Repository files navigation * README * Apache-2.0 license Linux Linux PRQL in PostgreSQL! PL/PRQL is a PostgreSQL extension that lets you write functions with PRQL. The extension supports PostgreSQL v12-16 on Linux and macOS. What is PRQL? PRQL (Pipelined Relational Query Language) is an open-source query language for data manipulation and analysis that compiles to SQL. PRQL introduces a pipeline concept (similar to Unix pipes) where data is transformed line-by-line. The sequential series of transformations reduces the complexity often encountered with nested SQL queries and makes your data manipulation logic easier to read and write. Key features * Write functions with PRQL - Useful for large analytical queries * PRQL compiler - Useful for development and debugging * Inline execution - Useful for prototyping and custom queries in ORMs Functions PRQL shines when your SQL queries becomes very long and complex. You can manage this complexity by porting your most impressive SQL incantations to PRQL functions, which can then be used in dashboards, business logic or other database code. For example: 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(1001) player | kd_ratio ---------+---------- Player1 | 0.625 Player2 | 1.6 (2 rows) This is similar to how PL/Python, PL/Javascript, and PL/Rust are implemented. PRQL Compiler You can use the PRQL compiler to see the SQL statements that PostgreSQL executes under the hood. You compile PRQL to SQL with the prql_to_sql() function: select prql_to_sql(...); -- statements above omitted for brevity prql_to_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 -- Generated by PRQL compiler version:0.11.1 (https://prql-lang.org) (1 row) Inline Execution You can run PRQL code directly with the prql function. This is useful for e.g. custom queries in ORMs: select prql('from matches | filter player == ''Player1''') as (id int, match_id int, round int, player text, kills int, deaths int) limit 2; id | match_id | round | player | kills | deaths ----+----------+-------+---------+-------+-------- 1 | 1001 | 1 | Player1 | 4 | 1 3 | 1001 | 2 | Player1 | 1 | 7 (2 rows) -- Same as above, but returns cursor select prql('from matches | filter player == ''Player1''', 'player1_cursor'); fetch 2 from player1_cursor; For more information on the design of the extension, see the design document. For more information on PRQL, visit the PRQL website, playground or repository. Note PRQL supports select statements only. insert, update, and delete statements, and your other database code, will continue to live in vanilla SQL, ORMs, or other database frameworks. Getting Started Install From Source Follow either of these steps to install from source: * Download and execute the install.sh bash script: curl --proto '=https' --tlsv1.2 -sSf https://raw.githubusercontent.com/kaspermarstal/plprql/main/scripts/install.sh | bash This will install the tip of the main branch using pg_config on your path. * You can customize the PostgreSQL installation and/or the PL/PRQL version using the --pg-config and --revision flags: curl --proto '=https' --tlsv1.2 -sSf https://raw.githubusercontent.com/kaspermarstal/plprql/main/scripts/install.sh > install.sh chmod +x ./install.sh ./install.sh --pg-version /usr/bin/pg_config --revision 186faea Set Up Development Environment Follow these steps to set up your development environment: 1. Install cargo-pgrx. cargo install --locked --version=0.11.3 cargo-pgrx The version of cargo-pgrx must match the version of pgrx in plprql/Cargo.toml. 2. Initialize pgrx for your system. cargo pgrx init --pg16 where is the path to your system installation's pg_config tool (typically /usr/bin/pg_config). Supported versions are PostgreSQL v12-16. You can also run cargo pgrx init and have pgrx download, install, and compile PostgreSQL v12-16. These installations are managed by pgrx and used for development and testing. Individual pgrx-managed installations can be installed using e.g. cargo pgrx init --pg16 download. 3. Clone this repository and cd into root directory. git clone https://github.com/kaspermarstal/plprql 4. Install the extension to the PostgreSQL specified by the pg_config currently on your $PATH. cd plprql/plprql cargo pgrx install --release You can target a specific PostgreSQL installation by providing the path of another pg_config using the -c flag. 5. Fire up your system PostgreSQL installation and start writing functions right away! You can also try out PL/PRQL in an installation managed by pgrx: $ cargo pgrx run pg16 psql> create extension plprql; psql> create function match_stats(int) returns table(total_kills real, total_deaths real) as $$ from rounds filter match_id == $1 aggregate { total_kills = sum kills, total_deaths = sum deaths } $$ language plprql psql> select match_stats(1); Running Tests You can run tests using cargo pgrx test pg16. Unit tests are in the main plprql crate while integration tests are in the plprql-tests crate. From the root source directory: cd plprql && echo "\q" | cargo pgrx run pg16 && cargo test --no-default-features --features pg16 cd ../plprql-tests && echo "\q" | cargo pgrx run pg16 && cargo test --no-default-features --features pg16 Supported PostgreSQL versions are pg12, pg13, pg14, pg15, and pg16. License Apache 2.0 License About Use PRQL in PostgreSQL prql-lang.org/ Topics sql postgresql pgrx prql Resources Readme License Apache-2.0 license Activity Stars 211 stars Watchers 3 watching Forks 2 forks Report repository Releases 1 v0.1.0 Latest Feb 16, 2024 Packages 0 No packages published Contributors 2 * @kaspermarstal kaspermarstal Kasper Marstal * @dependabot[bot] dependabot[bot] Languages * Rust 94.0% * Shell 6.0% Footer (c) 2024 GitHub, Inc. Footer navigation * Terms * Privacy * Security * Status * Docs * Contact * Manage cookies * Do not share my personal information You can't perform that action at this time.