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. │              ╰─── unexpected `t = `<time>``
7. │
8. │ Help: this is probably a 'bad type' error (we are working on that)
9. ───╯

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
• let - variable definition
• into - variable definition
• case - flow control
• type - type declaration
• func - explicit function declaration
• module - used internally
• internal - used internally
• true - literal
• false - literal
• null - literal

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

It may seem that transforms are also keywords, but they are normal functions within 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