JavaScript request filter - Vertica

The JavaScript request filter is the most flexible of all request filters. It can potentially be called for any packet type, and can modify traffic in any way it sees fit.

Parameters

• Packet types : optional. If specified, a comma-separated list of packet types for which this filter should be called, for instance:  Query,Bind,Execute. Spaces are ignored. If this is not specified, the filter will be invoked for all request packets.

• Client IPs: optional. If specified, a comma-separated (or line break separated) list of IP4 or IP6 addresses or regular expressions for IP addresses. Example: 12.34.56.78,regex:98\.76\.54\..*

• Users: optional. If specified, a comma-separated (or line break separated) list of user names or regular expressions for user names. Example: jdoe,wsmith,regex:acct_.*,regex:.*-mkt

• Query patterns: optional. One or more string or regular expression. If specified, this filter will only be executed if the current SQL query matches one of these. Example: regex:select\w+

Example: tweak the execution of a prepared statement

A Vertica client sends an Execute packet to request the execution of already-parsed and bound prepared statement. This packet includes the maximum number of rows to return.

In some cases, it may be desirable to change this number even if you cannot change the database client. This is easily done with a JavaScript request filter set up to execute for specific statements, for instance:

Packet types: Execute 

Query patterns: regex:select.*from acct.Accounts.*

and the following code:

if (context.packet.maxRows > 1000) {

    context.packet.maxRows = 1000;

}

Example: change the value of a bind parameter

Once a Vertica client has parsed a prepared statement (by sending a Parse packet), it can give values to the prepared statement's parameters with a Bind packet. 

It can be useful to change the value of these parameters so that you can change the behavior of the Vertica client without having to change its code. This can be done with a JavaScript request filter such as:

Packet types: Bind

Query patterns: regex:insert into acct.Payment.*values \(\?.*\)

and the following code (assuming parameter 2 is an integer):

let value = context.packet.getParameterValue(2);

if (value < 500) {

    context.packet.setParameterValue(2, 500);    

}