Reference documentation: here
In a way, I was developing something like this with dbms_sql_translator and sql_macro(table)
The problems I have with Google's syntax:
- seems to mix declarative language with a procedural language
- doesn't feel like other “table modifiers” (eg
pivot & match_recognize etc)
At first glance, each of the operators ( Google's rename, drop, extend … ) appear to be a declarative way to describe an SQL Macro. Then, I realized that the pipe symbol ( | or >| ) is mostly syntax sugar but can easily be used to indicate “the next level of subqueries/CTEs”
However, the need for a syntax like this works better when you use ETL centric procedures:
- enumeration (
row_number() )
- lookup (
left outer join )
- validation (
domain_check() )
- string detanglement ( passing a column through a UDT and extracting the attributes as new columns)
With that, the syntax I'm going with looks like:
select *
from <table>
transform (
<procedure> <procedure> ..
| <procedure> <procedure> .. -- transforms occur at a different subquery depth
..
)
My attempted implementing that modified version of the syntax (using one SQL Macro per <procedure> ) proved futile:
YOU CAN'T PIPELINE TABLE MACROS!
Question for Oracle: can we please have the ability to pipeline table macros? It will allow reusable Table Macros.
Question for everyone else: What do you think of Google's syntax? what do you think of my modified version?