4 ms·
The polymorphic view as I would like them would enable us to re-use entiere <query expressions> (i.e. "subqueries") applied to different data sources (i.e. "tab
by MarkusWinand 6y ago
The polymorphic view as I would like them would enable us to re-use entiere <query expressions> (i.e. "subqueries") applied to different data sources (i.e. "tables").
E.g (made-up syntax)
CREATE TEMPLATE VIEW numbered_by_ts(t1) AS (
SELECT t1.*
, ROW_NUMBER() OVER(ORDER BY ts) AS rn
FROM t1
)
e.g. use it like this:
SELECT *
FROM numbered_by_ts(table_name);
I think they should behave more like C++ templates rather than C pre-processor makros[1], thus I used the keyword TEMPLATE in CREATE VIEW. If you provide a table that doesn't have a column TS, it would be a syntax error, just like it is with C++ templates.
With common table expressions it would be easily possible to inject entire subqueries into the template view:
WITH cte AS (SELECT ... FROM ...)
SELECT *
FROM numbered_by_ts(cte);
For completness, WITH queries should also be allowed to be TEMPLATEs.
Speaking of the WITH clause, SQLite support ploymorphic views because SQLite CTEs are visible in elements that are "generally contained"[2] in the statement (the SQL standard defines it differently[3]).
Example:
CREATE TABLE t1 (
ts TIMESTAMP
);
INSERT INTO t1 VALUES ('2000-01-01 00:00:00');
CREATE VIEW numbered_by_ts AS
SELECT t1.*
, ROW_NUMBER() OVER(ORDER BY ts) AS rn
FROM t1
;
CREATE TABLE ta (
ts TIMESTAMP
);
INSERT INTO ta VALUES ('2010-01-01 00:00:00');
CREATE TABLE tb (
no_ts TIMESTAMP
);
INSERT INTO tb VALUES ('2020-01-01 00:00:00');
SELECT * FROM numbered_by_ts;
-- accesses t1, resturns year 2000
WITH t1 AS (SELECT * FROM ta)
SELECT * FROM numbered_by_ts;
-- accesses ta, resturns year 2010
WITH t1 AS (SELECT * FROM tb)
SELECT * FROM numbered_by_ts;
-- syntax error: no "ts" column in tb
SQLite is the only system I know of that implements WITH like that. See "views bypass with" here: https://modern-sql.com/feature/with#compatibility https://modern-sql.com/feature/with#compatibility
The polymorpic table functions, as introduced by SQL:2016, might be able to accomplish all of that but for me they feel like using a sledgehamer for cracking a nut.
[1] As far as I understand the Oracle 20c SQL makros behave like C pre-processor makros.
[2] "Generally contain" as defined by "Syntactic containment" in ISO/IEC 9075-1. The 2011 version of that can be downloaded for free at ISO: http://standards.iso.org/ittf/PubliclyAvailableStandards/c053681_ISO_IEC_9075-1_2011.zip http://standards.iso.org/ittf/PubliclyAvailableStandards/c05...
[3] ISO/IEC 9075-2, "<query expression>": the definition of "query name in scope" says "contained", not "generally contained".
- default-kramer 6y agoThank 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, ...