Variables — let & into

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. )
  7. 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
  4. 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. """
  8. 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