Variables

We can define a relation — similar to a CTE in SQL — as a variable with let:

PRQL

1. let top_50 = (
2. from employees
3. sort salary
4. take 50
5. aggregate [total_salary = sum salary]
6. )

8. from top_50      # Starts a new pipeline

SQL

1. WITH table_1 AS (
2. SELECT
3. salary
4. FROM
5. employees
6. ORDER BY
7. salary
8. LIMIT
9. 50
10. ), top_50 AS (
11. SELECT
12. SUM(salary) AS total_salary
13. FROM
14. table_1 AS table_0
15. )
16. SELECT
17. total_salary
18. FROM
19. top_50

We can even place a whole CTE in an s-string, enabling us to use features which PRQL doesn’t yet support.

PRQL

1. let grouping = s"""
2. SELECT SUM(a)
3. FROM tbl
4. GROUP BY
5. GROUPING SETS
6. ((b, c, d), (d), (b, d))
7. """

9. from grouping

SQL

1. WITH table_0 AS (
2. SELECT
3. SUM(a)
4. FROM
5. tbl
6. GROUP BY
7. GROUPING SETS ((b, c, d), (d), (b, d))
8. ),
9. grouping AS (
10. SELECT
11. *
12. FROM
13. table_0 AS table_1
14. )
15. SELECT
16. *
17. FROM
18. grouping

Info

In PRQL tables are far less common than CTEs are in SQL, since a linear series of CTEs can be represented with a single pipeline.

Currently defining variables with let is restricted to relations. We’d like to extend this to expressions that evaluate to scalars.