Kind of function out of common table expression

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Kind of function out of common table expression

Yannick Duchêne
Hi there,

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.

Merry Christmas to all by the way :-p




--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Kind of function out of common table expression

Nelson, Erik - 2
Yannick Duchêne Sent: Wednesday, December 20, 2017 5:23 PM

>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 use a C preprocessor for this and pass the queries through that on their way to the engine so a user might write something like

#define LONG_EXPRESSION  some very long expression

select LONG_EXPRESSION from somewhere;

select LONG_EXPRESSION from somewhere_else;


----------------------------------------------------------------------
This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended recipient, please delete this message.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Kind of function out of common table expression

Yannick Duchêne
Indeed, a preprocessor may be an option, since all the queries are stored in
text files (which are loaded by an application or directly used with
copy/paste).

If that's better or not than a function added per‑connection, it’s a matter
of taste. I will have to decide.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Kind of function out of common table expression

Clemens Ladisch
In reply to this post by Yannick Duchêne
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.


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users