Identifiers & keywords

Identifiers can contain alphanumeric characters and _ and must not start with a number. They can be chained together with the . indirection operator, used to retrieve a tuple from a field or a variable from a module.

1. hello

3. _h3llo

5. hello.world

this & that

this refers to the current relation:

PRQL

1. from invoices
2. aggregate (
3. count this
4. )

SQL

1. SELECT
2. COUNT(*)
3. FROM
4. invoices

Within a join, that refers to the other table:

PRQL

1. from invoices
2. join tracks (this.track_id==that.id)

SQL

1. SELECT
2. invoices.*,
3. tracks.*
4. FROM
5. invoices
6. JOIN tracks ON invoices.track_id = tracks.id

this can also be used to remove any column ambiguity. For example, currently using a bare time as a column name will fail, because it’s also a type:

PRQL

1. from invoices
2. derive t = time

Error

1. Error:
2. ╭─[:2:12]
3. │
4. 2 │ derive t = time
5. │            ──┬─
6. │              ╰─── expected a value, but found a type
7. ───╯

But with this.time, we can remove the ambiguity:

PRQL

1. from invoices
2. derive t = this.time

SQL

1. SELECT
2. *,
3. time AS t
4. FROM
5. invoices

Quoting

To use characters that would be otherwise invalid, identifiers can be surrounded by with backticks.

When compiling to SQL, these identifiers will use dialect-specific quotes and quoting rules.

PRQL

1. prql target:sql.mysql
2. from employees
3. select `first name`

SQL

1. SELECT
2. `first name`
3. FROM
4. employees

PRQL

1. prql target:sql.postgres
2. from employees
3. select `first name`

SQL

1. SELECT
2. "first name"
3. FROM
4. employees

PRQL

1. prql target:sql.bigquery

3. from `project-foo.dataset.table`
4. join `project-bar.dataset.table` (==col_bax)

SQL

1. SELECT
2. `project-foo.dataset.table`.*,
3. `project-bar.dataset.table`.*
4. FROM
5. `project-foo.dataset.table`
6. JOIN `project-bar.dataset.table` ON `project-foo.dataset.table`.col_bax = `project-bar.dataset.table`.col_bax

Schemas & database names

Identifiers of database tables can be prefixed with schema and databases names.

PRQL

1. from my_database.chinook.albums

SQL

1. SELECT
2. *
3. FROM
4. my_database.chinook.albums

Note that all of following identifiers will be treated as separate table definitions: tracks, public.tracks, my_database.public.tracks.

Keywords

PRQL uses following keywords:

  • prql - query header more…
  • let - variable definition more…
  • into - variable definition more…
  • case - flow control more…
  • type - type declaration
  • func - explicit function declaration more…
  • module - used internally
  • internal - used internally
  • true - boolean more…
  • false - boolean more…
  • null - NULL more…

Keywords can be used as identifiers (of columns or variables) when encased in backticks: `case` .

Transforms are normal functions within the std namespace, not keywords. That is, std.from is the same function as from. In the example below, the resulting query is the same as without the std. namespace:

PRQL

1. std.from my_table
2. std.select {from = my_table.a, take = my_table.b}
3. std.take 3

SQL

1. SELECT
2. a AS "from",
3. b AS take
4. FROM
5. my_table
6. LIMIT
7. 3