4 ms·

The most important code management tool SQL has are views and the WITH clause. https://modern-sql.com/use-case/literate-sql https://modern-sql.com/use-case/lit

The most important code management tool SQL has are views and the WITH clause. https://modern-sql.com/use-case/literate-sql https://modern-sql.com/use-case/literate-sql Something like "polymorpihc" views would be extremley useful, but is not there yet (in standard SQL). In other areas there are sublte features like the explicit WINDOW clause to avoid repeating similar OVER clauses. Breifly mentioned at the end of this doc: https://www.postgresql.org/docs/current/tutorial-window.html https://www.postgresql.org/docs/current/tutorial-window.html This was adopted in recent years by many FOSS-DBs, but not yet by the big commercial ones: https://modern-sql.com/static/sm-blog-2019-postgesql-11.over.en/T612.window-clause.zrNWMjbF.svg https://modern-sql.com/static/sm-blog-2019-postgesql-11.over... Similarily the chaining of grouping sets can be helpful in those very rare cases you need them. The Oracle Database intrdouced "SQL Macros" recently. For my taste, they are too 'rude' to make me happy on first sight (much like C #define): https://blog.dbi-services.com/oracle-20c-sql-macros-a-scalar-example-to-join-agility-and-performance/ https://blog.dbi-services.com/oracle-20c-sql-macros-a-scalar...

switch007 6y ago
Little tip: don't use indents for URLs. URLs are parsed automatically and need no special formatting.
- MarkusWinand 6y ago
  Thx, updated.
yen223 6y ago
Be careful with WITH clauses in Postgres though. Prior to Postgres 12, the engine always materialized queries inside the WITH clauses, even when its unnecessary. This can lead to certain queries being orders of magnitude slower than normal if written with a WITH clause.
- Tostino 6y ago
  Luckily 12 has been out a while now, and future versions will continue to not have this issue.
- eatonphil 6y ago
  You don't need to follow this blindly though. Benchmark with EXPLAIN to see the loss/gain on performance vs. readability for your particular query.
throwaway_pdp09 6y ago
> Similarily the chaining of grouping sets Do you mean 'group by grouping sets'? If not, could you clarify.
- MarkusWinand 6y ago
  Not only the grouping sets alone, the chaining of them. E.g. GROUP BY tenant , GROUPING SETS ( (year, month) , (year) ) is equivalant to GROUP BY GROUPING SETS ( (tenant, year, month) , (tenant, year) ) and GROUP BY tenant, year , GROUPING SETS ( (month) , () ) (however, I find the last one odd: I like to keep the "tenant" part seperate from the actual grupings you'd like to do).
  - throwaway_pdp09 6y ago
    Nice answer, thanks. Have you ever used this syntax? Has anybody here. I ask because I'm a heavy SQL user when I'm working, and I've never ever found the need (almost used a cube once but not quite) (I guess it's maybe for analysis rather than transactional stuff).
    - thom 6y ago
      Yeah, we use CUBE to create caches of aggregate data cut by various combinations of criteria, which allows us to keep UIs with lots of filters snappy, instead of having to hit a view.
    - jacobr1 6y ago
      I abused it in the past for aggregating every bitmask of CIDR, where the underlying data was by IP. So the groups were functions of the IP, one per mask level. We needed aggregations of arbitrary levels and this worked relatively well. We also tried it using it for some reporting that was tied to organizational hierarchy - same intuition - the group sets included org, parent, grandparent, etc ...
default-kramer 6y ago
What do you mean by "polymorphic views"? And you seem to imply they are implemented somewhere other than standard SQL, is that correct?
- 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".

switch007 6y ago

Little tip: don't use indents for URLs. URLs are parsed automatically and need no special formatting.

MarkusWinand 6y ago
Thx, updated.

yen223 6y ago

Be careful with WITH clauses in Postgres though. Prior to Postgres 12, the engine always materialized queries inside the WITH clauses, even when its unnecessary. This can lead to certain queries being orders of magnitude slower than normal if written with a WITH clause.

Tostino 6y ago
Luckily 12 has been out a while now, and future versions will continue to not have this issue.
eatonphil 6y ago
You don't need to follow this blindly though. Benchmark with EXPLAIN to see the loss/gain on performance vs. readability for your particular query.

throwaway_pdp09 6y ago

> Similarily the chaining of grouping sets Do you mean 'group by grouping sets'? If not, could you clarify.

MarkusWinand 6y ago
Not only the grouping sets alone, the chaining of them. E.g. GROUP BY tenant , GROUPING SETS ( (year, month) , (year) ) is equivalant to GROUP BY GROUPING SETS ( (tenant, year, month) , (tenant, year) ) and GROUP BY tenant, year , GROUPING SETS ( (month) , () ) (however, I find the last one odd: I like to keep the "tenant" part seperate from the actual grupings you'd like to do).
- throwaway_pdp09 6y ago
  Nice answer, thanks. Have you ever used this syntax? Has anybody here. I ask because I'm a heavy SQL user when I'm working, and I've never ever found the need (almost used a cube once but not quite) (I guess it's maybe for analysis rather than transactional stuff).
  - thom 6y ago
    Yeah, we use CUBE to create caches of aggregate data cut by various combinations of criteria, which allows us to keep UIs with lots of filters snappy, instead of having to hit a view.
  - jacobr1 6y ago
    I abused it in the past for aggregating every bitmask of CIDR, where the underlying data was by IP. So the groups were functions of the IP, one per mask level. We needed aggregations of arbitrary levels and this worked relatively well. We also tried it using it for some reporting that was tied to organizational hierarchy - same intuition - the group sets included org, parent, grandparent, etc ...

default-kramer 6y ago

What do you mean by "polymorphic views"? And you seem to imply they are implemented somewhere other than standard SQL, is that correct?

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".