[odb-users] WITH Queries in Views

[Brian Coggins]

Boris,

Thank you so much!  This solved the problem, and no issues encountered.

Brian


> On 31 Jul 2024, at 09:17, Boris Kolpackov <boris at codesynthesis.com> wrote:
> 
> Brian Coggins <becoggins at hotmail.com> writes:
> 
>> Yes, it tries to guess whether it's a complete query or the WHERE
>> clause by checking if the query starts with one of the known keywords
>> (like SELECT). I have a TODO item to recognize some additional database-
>> specific keywords (like WITH).
>> 
>> I’ve run into this limitation again, this time in the context of a
>> WITH RECURSIVE … query that I don’t think can be accomplished any
>> other way.  I upgraded to ODB 2.5.0-b.27 but it seems the issue is
>> still there.
>> 
>> Any chance we might be able to get a patch to loosen up these restrictions?
> 
> Ok, I went ahead and added WITH as a recognized SELECT prefix (I see it's
> also supported by SQLite). Plus I've added the /*SELECT*/ prefix as a
> hint that what follows is a SELECT-like query (this is similar to the
> /*CALL*/ hint we use to distinguish procedure calls in PostgreSQL, which
> uses SELECT for everything).
> 
> If you want to give it a try, I've published the snapshot packages here:
> 
>  https://queue.stage.build2.org
> 
> Simply replace:
> 
>  https://pkg.cppget.org/1/beta
> 
> with:
> 
>  https://queue.stage.build2.org/1/alpha
> 
> in the bpkg commands when building ODB.
> 
> Let me know if there any issues.