Allow any valid SQL query to be used as a table in a s-string

[lukapeschke]

I was wondering if there would be an easy way to allow any valid SQL query to be used as table.

Our use case it the following: We receive a source SQL query, and its content is out of our control. We then generate a PRQL pipeline applying to this query, compile it to SQL, and execute it on a target database. We have no control over the source query, and some of them can be pretty complex, and can include CTEs. An example would be

WITH Base AS (SELECT c.company_id, cc.name AS company_name, [...] a.value::JSONB AS json_data FROM sometable AS c LEFT JOIN other_table AS cc ON cc.foo = c.baz LEFT JOIN fizz AS s ON s.wee = c.woo LEFT JOIN  CROSS JOIN LATERAL (SELECT EXTRACT(YEAR FROM d.d)::INTEGER AS blah FROM generate_series(date_trunc('year', a.start_date), date_trunc('year', a.end_date), INTERVAL '1 year'), other_cte AS ([...]) SELECT * FROM other_cte

Right now, this results in an error with s-strings representing a table must start with SELECT `, which seems to originate in https://github.com/PRQL/prql/blob/main/prqlc/prqlc/src/sql/gen_query.rs#L508 .

Would there be an easy way to allow such a query to be used in an s-string in a from statement ?

from (s""" <complex_sql_query_here> """) | select {this.a, this.b, this.c}

For now, I see an escape hatch, but it seems quite hacky:

1. Instead of the real SQL query, use a magic value accepted by prql, so you get a prql query like from (s""" SELECT __MY_MAGIC_VALUE__ """) | select {this.a, this.b, this.c}
2. Compile to SQL
3. Replace SELECT __MY_MAGIC_VALUE__ with the actual sql query in the compiled SQL
4. Validate and format the final string with sqlparser

Thank you for your help and your work on prql!