2 ms·
Thank you! Very surprising behavior from SQLite, although it might come in handy if I use it carefully. Although I ask "why should macros have to return comple
by default-kramer 6y ago
Thank you! Very surprising behavior from SQLite, although it might come in handy if I use it carefully.
Although I ask "why should macros have to return complete query expressions?" To me, something like the following would be much more flexible.
Example 1, %ts_number returns a single numeric expression:
CREATE MACRO %ts_number(x) AS
ROW_NUMBER() OVER(ORDER BY x.ts)
select x.*, %ts_number(x) as rn
from SomeTable x
Example 2, %%foo returns 3 clauses to be combined into a larger query:
CREATE MACRO %%foo(x) AS
WHERE x.ts > 0
ORDER BY ROW_NUMBER() OVER (ORDER BY x.ts)
SELECT ROW_NUMBER() OVER (ORDER BY x.ts) as rn
select x.*
from SomeTable x
%%foo(x) -- this combines the 3 clauses into this query
-- So the result is equivalent to
select x.*, ROW_NUMBER() OVER (ORDER BY x.ts) as rn
from SomeTable x
WHERE x.ts > 0
ORDER BY ROW_NUMBER() OVER (ORDER BY x.ts)
I suppose it could even be up to the macro how its clauses get combined (before or after any existing clauses). In the following example, `...` is a special language element for use in macros. %%foo would append to the end of all the existing WHERE and ORDER BY clauses, but it would prepend to the existing SELECT clauses:
CREATE MACRO %%foo(x) AS
WHERE ... AND x.ts > 0
ORDER BY ..., ROW_NUMBER() OVER (ORDER BY x.ts)
SELECT ROW_NUMBER() OVER (ORDER BY x.ts) as rn, ...