7.1 KiB
Executable File
Transform
The <transform>
statements provides a means of chaining commands on <table-set>
s produced by any command, either by passing the resulting <table-set>
to the next command -- similar to how CTEs work -- or by applying a set operation on two resulting <table-set>
s. It also provides the framework for declaring <common-table-expression>
s, which can be consumed by following commands.
<transform> ::=
[ WITH [ <common-table-expression> [ ,...n ] ] ]
<cmd>
[ INTO <table>
| <transform-op> [ ( ] <cmd> [ ) ]
| <tee-op> <set-op> [ ( ] <cmd> [ ) ]
] [ ...n ]
[ AS OF [ <as-of-time> ] ]
<common-table-expression> ::= <transform> [ AS ] <alias>
A <transform>
in a CTE cannot include a WITH
clause.
<cmd> ::=
<delete>
| <insert>
| <merge>
| <query>
| <update>
A <cmd>
is considered terminal when it operates on a <table>
and potentially mutates its state, whether it mutates <table>
state or not. A terminal <cmd>
must be the last step in a <transform>
, it cannot be grouped by parentheses, and if is not the only <cmd>
it must have been preceded by a <pass-thru-op>
.
The <query>
command by itself is never terminal. It is terminal when it is followed by INTO <table>
.
<transform-op> ::= <set-op> | <pass-thru-op>
<set-op> ::=
UNION
| EXCEPT
| INTERSECT
| DIVIDED BY [ WITH REMAINDER ]
Set operations between two result <table-sets>
. The left <table-set>
represents the running result of the <transform>
and the right <table-set>
can be the result of nested <cmd>
s.
UNION
UNION
concatenates the left and right <table-set>
s into one <table-set>
of distinct rows.
EXCEPT
EXCEPT
returns distinct rows from the left <table-set>
that are not in the right <table-set>
. Rows that are not of the same <row-type>
are considered not matching.
INTERSECT
INTERSECT
returns distinct rows that are in both the left and right <table-set>
s. Rows that are not of the same <row-type>
are considered not matching.
DIVIDED BY [ WITH REMAINDER ]
This operator performs a relational division on the left <table-set>
as the dividend and the right <table-set>
as divisor.
NOTE: rule for dividing union <row-type>
s TBD.
<pass-thru-op> ::=
PASS-THRU
| TEE
| MULTEE
<tee-op> ::=
TEE
| MULTEE
<pass-thru-op>
s make the left resulting <table-set>
available for consumption by the next <cmd>
in the <transform>
. The right side of the statement cannot be nested. The left <table-set>
can be consumed by *
, in which case column identifiers and <row-type>
column alignments from the left <cmd>
apply, or a list of column aliases, in which case all columns produced by the left <cmd>
must be included in the order produced. (See the definition of <table-set>
in the Introduction.) In other words the <row-type>
of the left <table-set>
applies when consumed by the right <cmd>
.
PASS-THRU
The result <table-set>
of the left sequence of <cmd>
s in the <transform>
is available to the next (right) <cmd>
.
TEE
The result <table-set>
of the left sequence of <cmd>
s in the <transform>
is available to the next (right) <cmd>
and the left <table-set>
is placed in order in the list of <table-set>
s resulting from the parent <transform>
.
MULTEE
The result <table-set>
of the left sequence of <cmd>
s in the <transform>
is available to the next (right) <cmd>
and the results of each <row-type>
in the left <table-set>
union type is placed in order in the list of <table-set>
s resulting from the parent <transform>
. The order of the resulting <row-type>
s from the union type is arbitrary.
NOTE: deterministic ordering of union type results TBD.
<set-functions> ::=
<cmd> | <set-op>
API:
+$ transform
$:
%transform
ctes=(list <common-table-expression>)
set-functions=(tree <set-functions>)
==
Arguments
WITH [ <common-table-expression> [ ,...n ] ]
The WITH
clause makes the result <table-set>
of a <transform>
statement available to the subsequent <transform>
statements in the WITH
clause and <cmd>
s in the main <transform>
by <alias>
. <transform>
s in the WITH
clause cannot have their own WITH
clause, rather preceding CTEs within the clause are available and function as a virtual WITH
clause.
When used as a <common-table-expression>
, <transform>
output must be a pass-thru virtual-table.
When used as a <common-table-expression>
, <transform>
cannot include TEE
and MULTEE
operators.
INTO <table>
This clause inserts the resulting <table-set>
into <table>
. The associated <cmd>
is terminal. This is the only case in which <query>
is terminal.
<transform-op> [ ( ] <cmd> [ ) ]
If <transform-op>
is a <set-op>
the result <table-set>
from the left side is applied to the next (right) result or result from next nested <cmd>
s.
If <transform-op>
is a <pass-thru-op>
the result <table-set>
from the left side is available to the next <cmd>
.
Nesting left paren (
can only exist singly, but right paren )
may be stacked to any depth ...)))
, so long as left and right are matching. In other words nesting can only be applied on the right side.
<as-of-time>
The AS OF
provides a means to "travel through time" through the state changes of the <database>
(s). The default is the current state at execution, NOW
.
If the last <cmd>
is terminal (i.e. potentially state mutating, see discussion above), the affected <table>
definition (columns, indices, and foreign keys) must be the same currently and in the AS OF
time period. Foreign key constraints will operate against the current parent <table>
s. All other <database>
state will be in the AS OF
time period.
Due to possible <database>
state changes there is no guarantee of the success of an AS OF
<transform>
.
<timestamp>
Any valid date/time before the time of execution.
n
Integer seconds, minutes, hours, days, weeks, months, or years before execution time. If months is specified and the time "lands" on a day that is beyond the last day of that month, the date defaults to the last day of the landing month.
<inline-scalar>
TBD
Remarks
The <transform>
command potentially results in a state change of the Obelisk agent depending on the <cmd>
in the last step.
If a <cmd>
is terminal it must be the last <cmd>
in the <transform>
, cannot be nested by parentheses, and cannot be the right side of a <set-op>
.
Produced Metadata
list of output <table-set>
s in order produced (if the last <cmd>
is terminal it produces no output)
metadata from last <cmd>
, if it was not the right side of a <set-op>
@@ROWCOUNT
returns the total number of rows returned, if the last <cmd>
is in the right side of a <set-op>
Exceptions
<table>
does not exist
mismatch of result <row-type>
and <table>
GRANT
permission on <table>
violated
unique key violation on <table>
AS OF
prior to creation of any <table>
directly or indirectly (through a <view>
) referenced in the <transform>
any exception for <cmd>
anywhere in the <transform>