helpers.ColumnSet(columns, optionsopt) → {helpers.ColumnSet}

new ColumnSet(columns, optionsopt) → {helpers.ColumnSet}

Performance-optimized, read-only structure with query-formatting columns.

In order to avail from performance optimization provided by this class, it should be created only once, statically, and then reused.

Parameters:
Name Type Attributes Description
columns object | helpers.Column | array

Columns information object, depending on the type:

  • When it is a simple object, its properties are enumerated to represent both column names and property names within the source objects. See also option inherit that's applicable in this case.

  • When it is a single Column object, property columns is initialized with just a single column. It is not a unique situation when only a single column is required for an update operation.

  • When it is an array, each element is assumed to represent details for a column. If the element is already of type Column, it is used directly; otherwise the element is passed into Column constructor for initialization. On any duplicate column name (case-sensitive) it will throw Error = Duplicate column name "name".

  • When it is none of the above, it will throw TypeError = Invalid parameter 'columns' specified.
options object <optional>
Properties
Name Type Attributes Default Description
table helpers.TableName | Table | string <optional>

Table details.

When it is a non-null value, and not a TableName object, a new TableName is constructed from the value.

It can be used as the default for methods insert and update when their parameter table is omitted, and for logging purposes.
inherit boolean <optional>
 false

Use inherited properties in addition to the object's own properties.

By default, only the object's own properties are enumerated for column names.
Source:
See:
Returns:
Type
helpers.ColumnSet
Example
// A complex insert/update object scenario for table 'purchases' in schema 'fiscal'.
// For a good performance, you should declare such objects once and then reuse them.
//
// Column Requirements:
//
// 1. Property 'id' is only to be used for a WHERE condition in updates
// 2. Property 'list' needs to be formatted as a csv
// 3. Property 'code' is to be used as raw text, and to be defaulted to 0 when the
//    property is missing in the source object
// 4. Property 'log' is a JSON object with 'log-entry' for the column name
// 5. Property 'data' requires SQL type casting '::int[]'
// 6. Property 'amount' needs to be set to 100, if it is 0
// 7. Property 'total' must be skipped during updates, if 'amount' was 0, plus its
//    column name is 'total-val'
const {ColumnSet} = pgp.helpers;

const cs = new ColumnSet([
    '?id', // ColumnConfig equivalent: {name: 'id', cnd: true}
    'list:csv', // ColumnConfig equivalent: {name: 'list', mod: ':csv'}
    {
        name: 'code',
        mod: '^', // format as raw text
        def: 0 // default to 0 when the property doesn't exist
    },
    {
        name: 'log-entry',
        prop: 'log',
        mod: ':json' // format as JSON
    },
    {
        name: 'data',
        cast: 'int[]' // use SQL type casting '::int[]'
    },
    {
        name: 'amount',
        init(col) {
            // set to 100, if the value is 0:
            return col.value === 0 ? 100 : col.value;
        }
    },
    {
        name: 'total-val',
        prop: 'total',
        skip(col) {
            // skip from updates, if 'amount' is 0:
            return col.source.amount === 0;
        }
    }
], {table: {table: 'purchases', schema: 'fiscal'}});

// Alternatively, you could take the table declaration out:
// const table = new pgp.helpers.TableName('purchases', 'fiscal');

console.log(cs); // console output for the object:
//=>
// ColumnSet {
//    table: "fiscal"."purchases"
//    columns: [
//        Column {
//            name: "id"
//            cnd: true
//        }
//        Column {
//            name: "list"
//            mod: ":csv"
//        }
//        Column {
//            name: "code"
//            mod: "^"
//            def: 0
//        }
//        Column {
//            name: "log-entry"
//            prop: "log"
//            mod: ":json"
//        }
//        Column {
//            name: "data"
//            cast: "int[]"
//        }
//        Column {
//            name: "amount"
//            init: [Function]
//        }
//        Column {
//            name: "total-val"
//            prop: "total"
//            skip: [Function]
//        }
//    ]
// }

Members

(readonly) columns :Array.<helpers.Column>

Array of Column objects.

Type:
Source:

(readonly) names :string

Returns a string - comma-separated list of all column names, properly escaped.

Type:
  • string
Source:
Example
const cs = new ColumnSet(['id^', {name: 'cells', cast: 'int[]'}, 'doc:json']);
console.log(cs.names);
//=> "id","cells","doc"

(readonly) table :helpers.TableName

Destination table. It can be specified for two purposes:

  • primary: to be used as the default table when it is omitted during a call into methods insert and update
  • secondary: to be automatically written into the console (for logging purposes).
Type:
Source:

(readonly) variables :string

Returns a string - formatting template for all column values.

Type:
  • string
Source:
See:
Example
const cs = new ColumnSet(['id^', {name: 'cells', cast: 'int[]'}, 'doc:json']);
console.log(cs.variables);
//=> ${id^},${cells}::int[],${doc:json}

Methods

assign(optionsopt) → {string}

Returns a formatting template of SET assignments, either generic or for a single object.

The method is optimized to cache the output string when there are no columns that can be skipped dynamically.

This method is primarily for internal use, that's why it does not validate the input.

Parameters:
Name Type Attributes Description
options object <optional>

Assignment/formatting options.

Properties
Name Type Attributes Description
source object <optional>

Source - a single object that contains values for columns.

The object is only necessary to correctly apply the logic of skipping columns dynamically, based on the source data and the rules defined in the ColumnSet. If, however, you do not care about that, then you do not need to specify any object.

Note that even if you do not specify the object, the columns marked as conditional (cnd: true) will always be skipped.
prefix string <optional>

In cases where needed, an alias prefix to be added before each column.
Source:
See:
Returns:

Comma-separated list of variable-to-column assignments.

Type
string
Example
const cs = new pgp.helpers.ColumnSet([
    '?first', // = {name: 'first', cnd: true}
    'second:json',
    {name: 'third', mod: ':raw', cast: 'text'}
]);

cs.assign();
//=> "second"=${second:json},"third"=${third:raw}::text

cs.assign({prefix: 'a b c'});
//=> "a b c"."second"=${second:json},"a b c"."third"=${third:raw}::text

assignColumns(optionsopt) → {string}

Generates assignments for all columns in the set, with support for aliases and column-skipping logic. Aliases are set by using method as.alias.

Parameters:
Name Type Attributes Description
options Object <optional>

Optional Parameters.

Properties
Name Type Attributes Description
from string <optional>

Alias for the source columns.
to string <optional>

Alias for the destination columns.
skip string | Array.<string> | function <optional>

Name(s) of the column(s) to be skipped (case-sensitive). It can be either a single string or an array of strings.

It can also be a function - iterator, to be called for every column, passing in Column as this context, and plus as a single parameter. The function would return a truthy value for every column that needs to be skipped.
Source:
Returns:

A string of comma-separated column assignments.

Type
string
Examples
const cs = new ColumnSet(['id', 'city', 'street']);

cs.assignColumns({from: 'EXCLUDED', skip: 'id'})
//=> "city"=EXCLUDED."city","street"=EXCLUDED."street"
const cs = new ColumnSet(['?id', 'city', 'street']);

cs.assignColumns({from: 'source', to: 'target', skip: c => c.cnd})
//=> target."city"=source."city",target."street"=source."street"

extend(columns) → {helpers.ColumnSet}

Creates a new ColumnSet, by joining the two sets of columns.

If the two sets contain a column with the same name (case-sensitive), an error is thrown.

Parameters:
Name Type Description
columns helpers.Column | helpers.ColumnSet | array

Columns to be appended, of the same type as parameter columns during ColumnSet construction, except:

  • it can also be of type ColumnSet
  • it cannot be a simple object (properties enumeration is not supported here)
Source:
See:
Returns:

New ColumnSet object with the extended/concatenated list of columns.

Type
helpers.ColumnSet
Example
const pgp = require('pg-promise')();

const cs = new pgp.helpers.ColumnSet(['one', 'two'], {table: 'my-table'});
console.log(cs);
//=>
// ColumnSet {
//    table: "my-table"
//    columns: [
//        Column {
//            name: "one"
//        }
//        Column {
//            name: "two"
//        }
//    ]
// }
const csExtended = cs.extend(['three']);
console.log(csExtended);
//=>
// ColumnSet {
//    table: "my-table"
//    columns: [
//        Column {
//            name: "one"
//        }
//        Column {
//            name: "two"
//        }
//        Column {
//            name: "three"
//        }
//    ]
// }

merge(columns) → {helpers.ColumnSet}

Creates a new ColumnSet, by joining the two sets of columns.

Items in columns with the same name (case-sensitive) override the original columns.

Parameters:
Name Type Description
columns helpers.Column | helpers.ColumnSet | array

Columns to be appended, of the same type as parameter columns during ColumnSet construction, except:

  • it can also be of type ColumnSet
  • it cannot be a simple object (properties enumeration is not supported here)
Source:
See:
Returns:

New ColumnSet object with the merged list of columns.

Type
helpers.ColumnSet
Example
const pgp = require('pg-promise')();
const {ColumnSet} = pgp.helpers;

const cs = new ColumnSet(['?one', 'two:json'], {table: 'my-table'});
console.log(cs);
//=>
// ColumnSet {
//    table: "my-table"
//    columns: [
//        Column {
//            name: "one"
//            cnd: true
//        }
//        Column {
//            name: "two"
//            mod: ":json"
//        }
//    ]
// }
const csMerged = cs.merge(['two', 'three^']);
console.log(csMerged);
//=>
// ColumnSet {
//    table: "my-table"
//    columns: [
//        Column {
//            name: "one"
//            cnd: true
//        }
//        Column {
//            name: "two"
//        }
//        Column {
//            name: "three"
//            mod: "^"
//        }
//    ]
// }

prepare(source) → {object}

Prepares a source object to be formatted, by cloning it and applying the rules as set by the columns configuration.

This method is primarily for internal use, that's why it does not validate the input parameters.

Parameters:
Name Type Description
source object

The source object to be prepared, if required.

It must be a non-null object, which the method does not validate, as it is intended primarily for internal use by the library.
Source:
Returns:

When the object needs to be prepared, the method returns a clone of the source object, with all properties and values set according to the columns configuration.

When the object does not need to be prepared, the original object is returned.

Type
object

toString(levelopt) → {string}

Creates a well-formatted multi-line string that represents the object.

It is called automatically when writing the object into the console.

Parameters:
Name Type Attributes Default Description
level number <optional>
 0

Nested output level, to provide visual offset.
Source:
Returns:
Type
string