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

Abierto
  • ¿Algo no está bien?
#5,282 0 comentarios 1 reacción 0 asignados Ver en GitHub

Los mantenedores suelen responder en 3 días

Nadie ha tomado este issue todavía.

Evaluación

Dificultad
4/5
Tiempo estimado
3-5 días
Aptitud para principiantes
38/100
Tipo de issue
Nueva funcionalidad
Claridad
Bastante claro
Estado de actividad
Estancado
Stack tecnológico
rust, sql
Área
backend, databases

Línea de trabajo

The issue points to the s-string handling in prqlc/prqlc/src/sql/gen_query.rs, around line 508. Read that code and its related tests to see how table s-strings are validated and emitted, especially the current requirement that they start with SELECT . Done means a valid SQL query, including one beginning with a CTE, can be used as a table in an s-string while existing cases remain covered.

Escrito por el modelo de indexación a partir del texto del issue.

Descripción

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!

Lenguaje dominante
Rust
Estrellas
10.9k
Forks
261
Merge medio
3 d 21 h
PR fusionados (30 d)
84

Preparar el entorno

Abrir en Codespaces

Inicia el contenedor de desarrollo del proyecto en tu navegador, con tu propia cuenta de GitHub.

Primeros pasos

  1. Lee el issue completo y luego la guía de contribución del proyecto.
  2. Comenta en el issue que vas a ocuparte — evita que dos personas hagan lo mismo.
  3. Haz un fork del repositorio y trabaja en una rama.
  4. Abre un pull request que haga referencia al número del issue.

Más de PRQL/prql

Todos los issues de PRQL/prql

Issues similares

Más issues de Rust

Recibe los nuevos issues en tu correo

Un resumen breve de issues de GitHub para principiantes.