Functions

Functions are a fundamental abstraction in PRQL — they allow us to run code in many places that we’ve written once. This reduces the number of errors in our code, makes our code more readable, and simplifies making changes.

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

func fahrenheit_to_celsius temp -> (temp - 32) / 1.8
from cities
derive temp_c = (fahrenheit_to_celsius temp_f)

SQL

SELECT
  *,
  (temp_f - 32) / 1.8 AS temp_c
FROM
  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

func interp low:0 high x -> (x - low) / (high - low)
from students
derive [
  sat_proportion_1 = (interp 1600 sat_score),
  sat_proportion_2 = (interp low:0 1600 sat_score),
]

SQL

SELECT
  *,
  (sat_score - 0) / 1600 AS sat_proportion_1,
  (sat_score - 0) / 1600 AS sat_proportion_2
FROM
  students

Piping

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

func interp low:0 high x -> (x - low) / (high - low)
from students
derive [
  sat_proportion_1 = (sat_score | interp 1600),
  sat_proportion_2 = (sat_score | interp low:0 1600),
]

SQL

SELECT
  *,
  (sat_score - 0) / 1600 AS sat_proportion_1,
  (sat_score - 0) / 1600 AS sat_proportion_2
FROM
  students

and

PRQL

func fahrenheit_to_celsius temp -> (temp - 32) / 1.8
from cities
derive temp_c = (temp_f | fahrenheit_to_celsius)

SQL

SELECT
  *,
  (temp_f - 32) / 1.8 AS temp_c
FROM
  cities

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

PRQL

func fahrenheit_to_celsius temp -> (temp - 32) / 1.8
func interp low:0 high x -> (x - low) / (high - low)
from kettles
derive boiling_proportion = (temp_c | fahrenheit_to_celsius | interp 100)

SQL

SELECT
  *,
  ((temp_c - 32) / 1.8 - 0) / 100 AS boiling_proportion
FROM
  kettles

Scope

Late binding

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

PRQL

func cost_share cost -> cost / cost_total
from costs
select [materials, labor, overhead, cost_total]
derive [
  materials_share = (cost_share materials),
  labor_share = (cost_share labor),
  overhead_share = (cost_share overhead),
]

SQL

SELECT
  materials,
  labor,
  overhead,
  cost_total,
  materials / cost_total AS materials_share,
  labor / cost_total AS labor_share,
  overhead / cost_total AS overhead_share
FROM
  costs