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:

derive db_version = s"version()"

SELECT
  version() AS db_version

We can embed columns 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:

from employees
aggregate [average salary]

SELECT
  AVG(salary)
FROM
  employees

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

from de=dept_emp
join s=salaries side:left [
  (s.emp_no == de.emp_no),
  s"""({s.from_date}, {s.to_date})
  OVERLAPS
  ({de.from_date}, {de.to_date})"""
]

SELECT
  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)

To use brackets in an s-string, use double brackets:

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

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

For those who have used python, s-strings are similar to python’s f-strings, but the result is SQL, rather than a string literal — a python f-string of f"average{col}" where col="salary" would produce "average(salary)", with the quotes. s"average{col}" produces average(salary), without quotes.

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.