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:
- Positional parameters, which require an argument.
- 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
higher
and x
, and one named parameter named lower
which takes a default
argument of 0
. It calculates the proportion of the distance that x
is
between lower
and higher
.
PRQL
func interp lower:0 higher x -> (x - lower) / (higher - lower)
from students
derive [
sat_proportion_1 = (interp 1600 sat_score),
sat_proportion_2 = (interp lower: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 lower:0 higher x -> (x - lower) / (higher - lower)
from students
derive [
sat_proportion_1 = (sat_score | interp 1600),
sat_proportion_2 = (sat_score | interp lower: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 lower:0 higher x -> (x - lower) / (higher - lower)
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
Roadmap
Late binding
Currently, functions require a binding to variables in scope; they can’t late-bind to column names; so for example:
func return price -> (price - dividend) / price_yesterday
…isn’t yet a valid function, and instead would needs to be:
func return price dividend price_yesterday -> (price - dividend) / (price_yesterday)
(which makes functions in this case not useful)