Support for table-valued-functions

[tstack]

It would be nice to support table-valued-functions at some point. (Unless it's already supported and I missed it)

[aljazerzen]

It is not currently supported, but it's already in the works!

As you can imagine, our transforms have the same syntax as all other functions. The only difference is that they have their own treatment right after parsing, so normal semantic analysis does not apply to them. For example:

join side:left [employee_id]

... applies custom semantic analysis to the function join. left is not being treated as a variable that has to be resolved and employee_id must be resolved into two columns (because it transpiles into USING).

Such custom semantic analysis treatments are what is keeping us from having table-valued-functions (or transform functions as I call it).

If you are interested in more details, see my work-in-progress PR https://github.com/prql/prql/pull/593

[aljazerzen]

This was done some time ago, but it is not yet documented.

Here is some discussion about it: https://github.com/PRQL/prql/discussions/1323#discussioncomment-4476298

[tstack]

Hmm, I'm not quite sure I understand the discussion. I'm looking for how to use something like json_each() in a PRQL pipeline. For example, how would I write the following SQL in PRQL:

SELECT * FROM json_each('{"a": 1, "b": 2}')

[aljazerzen]

This issue refers to PRQL-table-valued functions.

What you want is this:

from s"""SELECT * FROM json_each('{{"a": 1, "b": 2}}')"""
select {a, b}
derive c = a + b

The SELECT * FROM is needed because our current implementation of s-strings requires it to start with SELECT.

[tstack]

This issue refers to PRQL-table-valued functions.

Heh, I filed the issue and was thinking of how to call SQL table-valued-functions at the time. Sorry I didn't make that clear.

So, it looks like I just need to declare the function and then I can use it like so:

prql target:sql.sqlite

let json_each = input -> s"SELECT * FROM json_each({input:0})"

from (json_each '{"a": 1}')

Thanks!