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

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/c05...

[3] ISO/IEC 9075-2, "<query expression>": the definition of "query name in scope" says "contained", not "generally contained".