[Request] Composable SQL queries

[inhaledesign]

Is your feature request related to a problem? Please describe. In my organization, we have a database where we can't use SQL views or procedures (security reasons). We're trying to work around this by coming up with a way to compose together strings of SQL in our Java code. This would allow us to define, say, a SELECT statement in one place, test it, and then use it as a CTE in other places.

Describe the solution you'd like If Manifold could allow something along these lines, it would address our issue:

        final String inneryQuery = "SELECT PERSON_COL FROM MYSCHEMA.PERSON";
        "[MyQuery.sql/] SELECT * FROM ( :inneryQuery )".fetchOne();

[rsmckinney]

Interesting, manifold-sql also supports files as SQL resources.

src/main/resources/org/example/queries/Persons.sql

SELECT PERSON_COL FROM MYSCHEMA.PERSON

import org.example.queries.Persons;
. . .
"[MyQuery.sql/] SELECT * FROM (::Persons)".fetchOne();

Where the ::Persons syntax is imaginary. But the idea is to allow type-safely inlining other manifold queries. Of course, the Persons query wouldn't have to be in a file, it could be a local inline query as well.

This syntax would work anywhere a select could be used, including the WITH clause.

"""
[CteExample.sql] WITH my_cte (alpha, beta, gamma) AS (::MyQuery), 
  SELECT * FROM my_cte . . . 
""".fetch(. . .)

Warning, I haven't thought this through, but I think it's doable. . . at least using the ::QueryType syntax with inlined queries.

[inhaledesign]

@rsmckinney Just to be clear: the ::QueryType syntax does not currently exist in Manifold, but you think it could be added?

[rsmckinney]

Correct