Variables

Variables assign a name — say x — to an expression, like in most programming languages. The name can then be used in any expression, acting as a substitute for the expression x.

Syntactically, variables can take 3 forms.

• let declares the name before the expression.

1. let my_name = x - into declares the name after the expression. This form is useful for quick pipeline splitting and conforms with the “flow from top to bottom” rule of pipelines.

1. x 2. into my_name - The final expression of a pipeline defaults to taking the name main.

1. from x

… is equivalent to:

1. let main = x

When compiling to SQL, relational variables are compiled to Common Table Expressions (or sub-queries in some cases).

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_0 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. COALESCE(SUM(salary), 0) AS total_salary
13. FROM
14. table_0
15. )
16. SELECT
17. total_salary
18. FROM
19. top_50

PRQL

1. from employees
2. take 50
3. into first_50

5. from first_50

SQL

1. WITH first_50 AS (
2. SELECT
3. *
4. FROM
5. employees
6. LIMIT
7. 50
8. )
9. SELECT
10. *
11. FROM
12. first_50

Variables can be assigned an s-string containing the whole SQL query 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. SELECT
10. *
11. FROM
12. table_0