Functions

Functions have two types of parameters:

  1. Positional parameters, which require an argument.
  2. Named parameters, which optionally take an argument, otherwise using their default value.

So this function is named fahrenheit_to_celsius and has one parameter temp:

PRQL

  1. let fahrenheit_to_celsius = temp -> (temp - 32) / 1.8
  2. from cities
  3. derive temp_c = (fahrenheit_to_celsius temp_f)

SQL

  1. SELECT
  2. *,
  3. (temp_f - 32) / 1.8 AS temp_c
  4. FROM
  5. cities

This function is named interp, and has two positional parameters named high and x, and one named parameter named low which takes a default argument of 0. It calculates the proportion of the distance that x is between low and high.

PRQL

  1. let interp = low:0 high x -> (x - low) / (high - low)
  2. from students
  3. derive {
  4. sat_proportion_1 = (interp 1600 sat_score),
  5. sat_proportion_2 = (interp low:0 1600 sat_score),
  6. }

SQL

  1. SELECT
  2. *,
  3. (sat_score - 0) / (1600 - 0) AS sat_proportion_1,
  4. (sat_score - 0) / (1600 - 0) AS sat_proportion_2
  5. FROM
  6. students

Other examples

PRQL

  1. let is_adult = col -> col >= 18
  2. let writes_code = col -> (col | in ["PRQL", "Rust"])
  3. let square = col -> (col | math.pow 2)
  4. let starts_with_a = col -> (col | text.lower | text.starts_with("a"))
  5. from employees
  6. select {
  7. first_name,
  8. last_name,
  9. hobby,
  10. adult = is_adult age,
  11. age_squared = square age,
  12. }
  13. filter ((starts_with_a last_name) && (writes_code hobby))

SQL

  1. WITH table_0 AS (
  2. SELECT
  3. first_name,
  4. last_name,
  5. hobby,
  6. age >= 18 AS adult,
  7. POW(age, 2) AS age_squared
  8. FROM
  9. employees
  10. )
  11. SELECT
  12. first_name,
  13. last_name,
  14. hobby,
  15. adult,
  16. age_squared
  17. FROM
  18. table_0
  19. WHERE
  20. LOWER(last_name) LIKE CONCAT('a', '%')
  21. AND hobby IN ('PRQL', 'Rust')

Piping values into functions

Consistent with the principles of PRQL, it’s possible to pipe values into functions, which makes composing many functions more readable. When piping a value into a function, the value is passed as an argument to the final positional parameter of the function. Here’s the same result as the examples above with an alternative construction:

PRQL

  1. let interp = low:0 high x -> (x - low) / (high - low)
  2. from students
  3. derive {
  4. sat_proportion_1 = (sat_score | interp 1600),
  5. sat_proportion_2 = (sat_score | interp low:0 1600),
  6. }

SQL

  1. SELECT
  2. *,
  3. (sat_score - 0) / (1600 - 0) AS sat_proportion_1,
  4. (sat_score - 0) / (1600 - 0) AS sat_proportion_2
  5. FROM
  6. students

and

PRQL

  1. let fahrenheit_to_celsius = temp -> (temp - 32) / 1.8
  2. from cities
  3. derive temp_c = (temp_f | fahrenheit_to_celsius)

SQL

  1. SELECT
  2. *,
  3. (temp_f - 32) / 1.8 AS temp_c
  4. FROM
  5. cities

We can combine a chain of functions, which makes logic more readable:

PRQL

  1. let fahrenheit_to_celsius = temp -> (temp - 32) / 1.8
  2. let interp = low:0 high x -> (x - low) / (high - low)
  3. from kettles
  4. derive boiling_proportion = (temp_c | fahrenheit_to_celsius | interp 100)

SQL

  1. SELECT
  2. *,
  3. ((temp_c - 32) / 1.8 - 0) / (100 - 0) AS boiling_proportion
  4. FROM
  5. kettles

Late binding

Functions can bind to any variable that is in scope when the function is executed. For example, here cost_total refers to the column that’s introduced in the from.

PRQL

  1. let cost_share = cost -> cost / cost_total
  2. from costs
  3. select {materials, labor, overhead, cost_total}
  4. derive {
  5. materials_share = (cost_share materials),
  6. labor_share = (cost_share labor),
  7. overhead_share = (cost_share overhead),
  8. }

SQL

  1. SELECT
  2. materials,
  3. labor,
  4. overhead,
  5. cost_total,
  6. materials / cost_total AS materials_share,
  7. labor / cost_total AS labor_share,
  8. overhead / cost_total AS overhead_share
  9. FROM
  10. costs