Result set filter - Vertica
A result set filter is invoked (potentially) for every row in a result set. The filter can modify the row, add more rows, drop the row, or return an error. This filter works for all result sets, whether they result from SQL statements or from the execution of a stored procedure.
Parameters
All parameters are optional. If no parameter is specified, then the filter will be invoked for every row of every result set.
User pattern
If specified, a comma-separated (or line break separated) list of database user names or regular expressions for database user names.
Client IPs
A comma-separated (or line-break separated) list of IP4 or IP6 addresses or regular expressions for IP addresses. If specified, this filter will only be invoked when the client's IP address matches.
Schema pattern
If specified, the filter will not be invoked unless it contains at least one column from a table in the specified schema. Can be a string or a regular expression.
Table pattern
If specified, the filter will not be invoked unless it contains at least one column from the specified table. Can be a string or a regular expression.
Query pattern
A series of strings and/or regular expressions, separated by commas or newlines. If the current SQL does not match any of these, the filter will not be invoked. The current SQL is the last SQL command executed.
Column patterns
A series of name=value pairs, separated by commas or newlines. The value is a regular expression, which means special characters like period must be escaped.
And/or
If more than one column pattern is specified, this determines whether they must all be true (and) or any one of them (or).
Action
Can be either code or hide. If it's code, then the code is executed. If it's hide, then the row is dropped from the result set, and the code is not executed.
Examples
The following examples use Vertica's demo database schema named store.
Example: hide some stores in California
The requirement is that all stores in California (e.g. store_state = 'CA' ) should be taken out of the result set if their total square footage is over 2,000 sqft.
The actual data in the database can be seen with:
select * from store.store_dimension where store_state = 'CA'
which returns 15 rows.
We can create a result set filter with the proper parameters, and add a bit of code to hide the data.
The parameters for the filter are:
Schema pattern: store
Table pattern: store_dimension
Column patterns: store_state=CA
This will ensure that this filter will be invoked for any result set in which the store_state column is 'CA'.
Next we add a bit of code that checks the square footage and hides the row if necessary:
if (context.packet.total_square_footage > 2000) {
context.packet.remove();
}
If we now re-run the query:
select * from store.store_dimension where store_state = 'CA'
we only see 5 rows. The database is of course untouched -- all we changed was the result set on its way to the client.
Example: change some values
Let's say there is a query that returns data that should not be seen by some users. The rule is:
For users in France and Germany (i.e. users whose user name starts with "fr_" or "de_"), the store address should be hidden if the store is in the West region.
We can create a result set with the proper parameters, and add a bit of code to hide the data.
The parameters for the filter are:
User pattern: regex:fr_.*,de_.*
Schema pattern: store
Table pattern: store_dimension
Column patterns: store_region=West
The code for the filter will be invoked when these conditions are satisfied, and can hide the store's address with:
if (context.packet.store_address) {
context.packet.store_address = "#########";
}
Example: add a synthetic row to the result set
We can add new rows to the result set by cloning the current row and modifying it.
We create a filter with the parameters:
Schema pattern: store
Table pattern: store_dimension
Column patterns: store_key=4
This will ensure that the code for the filter will only be executed for store #4, which we'll duplicate with the following code:
for (var i = 1; i <= 3; i++) {
let newRow = context.packet.clone();
newRow.store_key = 1000000 + i;
newRow.store_name = 'Made-up store #' + i;
newRow.store_address = i + " Fake Ave";
context.packets.addPacket(newRow);
}