brianc/node-sql

Add support for named notation function calls

Opened this issue · 0 comments

Allow functions to be called using named notation.

This is especially useful if a function has a large number of named parameters, only some of which need to be specified in a given query. It is also rather convenient for easily converting from an object into parameters of a function call.


The examples below are based on the examples in the PostgreSQL docs. They assume the following:

var concat_lower_or_upper = sql.functionCallCreator('concat_lower_or_upper');

The expected output for each named notation example is:

SELECT concat_lower_or_upper(a := 'Hello', b := 'World')

and for mixed notation:

SELECT concat_lower_or_upper('Hello', b := 'World')

Since named arguments themselves aren't supported in JS, there's a few different approaches we could use:

Pass a "named arguments" object as the last parameter to the function call creator:

Named notation:

concat_lower_or_upper({a: 'Hello', b: 'World'});

Mixed notation:

concat_lower_or_upper('Hello', {b: 'World'});
  • Disadvantage: May cause issues when passing values for JSON, hstore, or array arguments, since it will be harder to determine whether the last parameter is meant to be one of those types, or a collection of named arguments.

Attach a separate function to the function call creator for making a call using named parameters:

Named notation:

concat_lower_or_upper.callNamed({a: 'Hello', b: 'World'});

Mixed notation:

// Not supported. //

Attach a separate function to the function call node for adding named parameters to a call:

Named notation:

concat_lower_or_upper().namedParams({a: 'Hello', b: 'World'});

Mixed notation:

concat_lower_or_upper('Hello').namedParams({b: 'World'});
  • Disadvantage: Function calls in JS and SQL are no longer 1-to-1 when using named or mixed notation. (2 calls in JS for 1 in SQL; more obtuse)
  • Note: This may be combined with the previous solution to avoid awkward calls when using named notation.

If an object is passed as the only argument to the function call creator, treat it as an "options" object:

Named notation:

concat_lower_or_upper({namedParams: {a: 'Hello', b: 'World'});

Mixed notation:

concat_lower_or_upper({params: ['Hello'], namedParams: {b: 'World'});
  • Disadvantage: This requires more code than the other solutions.
  • Disadvantage: When using mixed notation, positional arguments must be wrapped in an array.