S-strings

An s-string inserts SQL directly, as an escape hatch when there’s something that PRQL doesn’t yet implement. For example, there’s no version() function in SQL that returns the Postgres version, so if we want to use that, we use an s-string:

PRQL

  1. from my_table
  2. select db_version = s"version()"

SQL

  1. SELECT
  2. version() AS db_version
  3. FROM
  4. my_table

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

So this compiles using the function:

PRQL

  1. from employees
  2. aggregate [average salary]

SQL

  1. SELECT
  2. AVG(salary)
  3. FROM
  4. employees

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

PRQL

  1. from de=dept_emp
  2. join s=salaries side:left [
  3. (s.emp_no == de.emp_no),
  4. s"""({s.from_date}, {s.to_date})
  5. OVERLAPS
  6. ({de.from_date}, {de.to_date})"""
  7. ]

SQL

  1. SELECT
  2. de.*,
  3. s.*
  4. FROM
  5. dept_emp AS de
  6. LEFT JOIN salaries AS s ON s.emp_no = de.emp_no
  7. AND (s.from_date, s.to_date) OVERLAPS (de.from_date, de.to_date)

For those who have used Python, s-strings are similar to Python’s f-strings, but the result is SQL code, rather than a string literal. For example, a Python f-string of f"average({col})" would produce "average(salary)", with quotes; while in PRQL, s"average({col})" produces average(salary), without quotes.

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

  1. from s"SELECT DISTINCT ON first_name, id, age FROM employees ORDER BY age ASC"
  2. join s = s"SELECT * FROM salaries" [==id]

SQL

  1. WITH table_0 AS (
  2. SELECT
  3. DISTINCT ON first_name,
  4. id,
  5. age
  6. FROM
  7. employees
  8. ORDER BY
  9. age ASC
  10. ),
  11. table_1 AS (
  12. SELECT
  13. *
  14. FROM
  15. salaries
  16. )
  17. SELECT
  18. table_2.*,
  19. table_3.*
  20. FROM
  21. table_0 AS table_2
  22. JOIN table_1 AS table_3 ON table_2.id = table_3.id

Note

S-strings in user code are intended as an escape-hatch for an unimplemented feature. If we often need s-strings to express something, that’s a sign we should implement it in PRQL or PRQL’s stdlib.

Braces

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

PRQL

  1. from employees
  2. derive [
  3. has_valid_title = s"regexp_contains(title, '([a-z0-9]*-){{2,}}')"
  4. ]

SQL

  1. SELECT
  2. *,
  3. regexp_contains(title, '([a-z0-9]*-){2,}') AS has_valid_title
  4. FROM
  5. employees

Precedence

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:

PRQL

  1. from employees
  2. derive [
  3. gross_salary = salary + benefits,
  4. daily_rate = s"{gross_salary} / 365"
  5. ]

SQL

  1. SELECT
  2. *,
  3. salary + benefits AS gross_salary,
  4. salary + benefits / 365 AS daily_rate
  5. FROM
  6. employees

Instead, put the denominator {gross_salary} in parentheses:

PRQL

  1. from employees
  2. derive [
  3. gross_salary = salary + benefits,
  4. daily_rate = s"({gross_salary}) / 365"
  5. ]

SQL

  1. SELECT
  2. *,
  3. salary + benefits AS gross_salary,
  4. (salary + benefits) / 365 AS daily_rate
  5. FROM
  6. employees