I wonder is there is a way with SQLite3, to reuse a often used and
moderately long common table expression without the need to copy/paste it in
every query text it is used in.
I know there is a way to add a function to SQLite connexions using DB APIs,
like that of Python. Still, as much as I can, I prefer database definitions
to be independent of the environment in which it is used (ex. the function
would not be available when running the SQLite command line shell).
I tried to define a table with a trigger which I wanted to write the result
of the computation to a table, with the hope to read to write to that table
and just have to read it back, which could be a much shorter statement on
each use places.
Unfortunately, I was recalled (after I rediscovered) common table
expressions are not supported in triggers.
That’s not a big issue, if there is no way, I will go with a function added
for each connexion lifetime. That’s just if I can avoid it, it would be
cleaner to me.
Yannick Duchêne wrote:
>I wonder is there is a way with SQLite3, to reuse a often used and
>moderately long common table expression without the need to copy/paste
>it in every query text it is used in.
If you do not need dynamic parameters, use a view.
Otherwise, you have to write out the subquery.