Variables

Variables assign a name to an expression (let’s call it x). 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.

  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.

  x
  into my_name

• implicit name, does not declare a name at all, but uses name main as the default. This is practical, as main is the variable that is compiled as the main relational query by default.

  x

  … is equivalent to:

  let main = x

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

PRQL

let top_50 = (
  from employees
  sort salary
  take 50
  aggregate {total_salary = sum salary}
)
from top_50      # Starts a new pipeline

SQL

WITH table_0 AS (
  SELECT
    salary
  FROM
    employees
  ORDER BY
    salary
  LIMIT
    50
), top_50 AS (
  SELECT
    COALESCE(SUM(salary), 0) AS total_salary
  FROM
    table_0
)
SELECT
  total_salary
FROM
  top_50

from employees
take 50
into first_50
from 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

let grouping = s"""
  SELECT SUM(a)
  FROM tbl
  GROUP BY
    GROUPING SETS
    ((b, c, d), (d), (b, d))
"""
from grouping

SQL

WITH table_0 AS (
  SELECT
    SUM(a)
  FROM
    tbl
  GROUP BY
    GROUPING SETS ((b, c, d), (d), (b, d))
)
SELECT
  *
FROM
  table_0