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 a version() function in PostgreSQL that returns the PostgreSQL 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:

1. let average = column -> s"AVG({column})"

So this compiles using the function:

PRQL

1. from employees
2. aggregate {average salary}

SQL

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

Note

Because S-string contents are SQL, double-quotes (") will denote a column name. To avoid that, use single-quotes (') around the SQL string, and adjust the quotes of the S-string. For example, instead of s'CONCAT("hello", "world")' use s"CONCAT('hello', 'world')"

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 (s.emp_no == de.emp_no && s"""
3. ({s.from_date}, {s.to_date})
4. OVERLAPS
5. ({de.from_date}, {de.to_date})
6. """)

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_0.*,
19. table_1.*
20. FROM
21. table_0
22. JOIN table_1 ON table_0.id = table_1.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

Variables in s-strings are inserted into the SQL source as-it, 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, the denominator {gross_salary} must be encased 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