S-strings

PRQL

SQL

SELECT
  version() AS db_version
FROM
  my_table

Embed a column name in an s-string using braces. For example, PRQL’s standard library defines the average function as:

func average column -> s"AVG({column})"

So this compiles using the function:

PRQL

from employees
aggregate [average salary]

SQL

SELECT
  AVG(salary)
FROM
  employees

Here’s an example of a more involved use of an s-string:

PRQL

SQL

  de.*,
  s.*
FROM
  dept_emp AS de
  LEFT JOIN salaries AS s ON s.emp_no = de.emp_no
  AND (s.from_date, s.to_date) OVERLAPS (de.from_date, de.to_date)

Note that interpolations can only contain plain variable names and not whole expression like Python.

We can also use s-strings to produce a full table:

PRQL

from s"SELECT DISTINCT ON first_name, id, age FROM employees ORDER BY age ASC"

SQL

WITH table_0 AS (
  SELECT
    DISTINCT ON first_name,
    id,
    age
  FROM
    employees
  ORDER BY
    age ASC
),
table_1 AS (
  SELECT
    *
  FROM
    salaries
)
SELECT
  table_2.*,
  table_3.*
  table_0 AS table_2
  JOIN table_1 AS table_3 ON table_2.id = table_3.id

Note

To output braces from an s-string, use double braces:

from employees
derive [
]

The PRQL compiler simply places a literal copy of each variable into the resulting string, which means we may get surprising behavior when the variable is has multiple terms and the s-string isn’t parenthesized.

In this toy example, the salary + benefits / 365 gets precedence wrong:

from employees
derive [
  gross_salary = salary + benefits,
  daily_rate = s"{gross_salary} / 365"
]

SELECT
  *,
  salary + benefits AS gross_salary,
  salary + benefits / 365 AS daily_rate
FROM
  employees

from employees
derive [
  gross_salary = salary + benefits,
  daily_rate = s"({gross_salary}) / 365"
]

SELECT
  *,
  salary + benefits AS gross_salary,
  (salary + benefits) / 365 AS daily_rate
FROM