Add support for accessing PG composite types (UDT)

Dorian Johnson · Dorian Johnson · commit 5566e04b046e · 2015-03-17T11:27:19.000-05:00
As per http://www.postgresql.org/docs/9.4/static/rowtypes.html#AEN7836 No attempt at supporting these in UPDATE is attempted here.
diff --git a/lib/dialect/postgres.js b/lib/dialect/postgres.js
@@ -654,7 +654,7 @@ Postgres.prototype.visitColumn = function(columnNode) {
       txt.push('DISTINCT(');
     }
   }
-  if(!inInsertUpdateClause && !this.visitingReturning && !this._visitingCreate && !this._visitingAlter) {
+  if(!inInsertUpdateClause && !this.visitingReturning && !this._visitingCreate && !this._visitingAlter && !columnNode.subfieldContainer) {
     if(table.alias) {
       txt.push(this.quote(table.alias));
     } else {
@@ -686,6 +686,9 @@ Postgres.prototype.visitColumn = function(columnNode) {
     }
   }
   else {
+    if (columnNode.subfieldContainer) {
+      txt.push('(' + this.visitColumn(columnNode.subfieldContainer) + ').');
+    }
     txt.push(this.quote(columnNode.name));
   }
   if(closeParen) {
diff --git a/lib/node/column.js b/lib/node/column.js
@@ -19,6 +19,10 @@ module.exports = Node.define({
     this.primaryKey = config.primaryKey;
     this.notNull = config.notNull;
     this.references = config.references;
+    // If subfieldContainer is present, this is a subfield and subfieldContainer
+    // is the parent Column
+    this.subfieldContainer = config.subfieldContainer;
+    this.subfields = config.subfields;
     this.autoGenerated = !!config.autoGenerated;
   },
   as: function(alias) {
diff --git a/lib/table.js b/lib/table.js
@@ -67,6 +67,20 @@ Table.prototype.createColumn = function(col) {
 
     col.table = this;
     col = new Column(col);
+
+    // Load subfields from array into an object of form name: Column
+    if(util.isArray(col.subfields)) {
+      col.subfields = lodash.chain(col.subfields)
+        .map(lodash.bind(function (subfield) {
+          return [subfield, new Column({
+            table: this,
+            subfieldContainer: col,
+            name: subfield
+          })]
+        }, this))
+        .object()
+        .value()
+    }
   }
 
   return col;
diff --git a/test/dialects/subfield-tests.js b/test/dialects/subfield-tests.js
@@ -0,0 +1,42 @@
+'use strict';
+
+var Harness = require('./support');
+var customer = Harness.defineCustomerCompositeTable();
+var Sql = require('../../lib').setDialect('postgres');
+
+Harness.test({
+  query: customer.select(customer.info.subfields.age),
+  pg: {
+    text  : 'SELECT ("customer"."info")."age" FROM "customer"',
+    string: 'SELECT ("customer"."info")."age" FROM "customer"'
+  },
+  params: []
+});
+
+
+Harness.test({
+  query: customer.select(customer.info.subfields.age.as('years')),
+  pg: {
+    text  : 'SELECT ("customer"."info")."age" AS "years" FROM "customer"',
+    string: 'SELECT ("customer"."info")."age" AS "years" FROM "customer"'
+  },
+  params: []
+});
+
+Harness.test({
+  query: customer.select(customer.id).where(customer.info.subfields.salary.equals(10)),
+  pg: {
+    text  : 'SELECT "customer"."id" FROM "customer" WHERE (("customer"."info")."salary" = $1)',
+    string: 'SELECT "customer"."id" FROM "customer" WHERE (("customer"."info")."salary" = 10)'
+  },
+  params: [10]
+});
+
+Harness.test({
+  query: customer.select(customer.info.subfields.name.distinct()),
+  pg: {
+    text  : 'SELECT DISTINCT(("customer"."info")."name") FROM "customer"',
+    string: 'SELECT DISTINCT(("customer"."info")."name") FROM "customer"'
+  },
+  params: []
+});
diff --git a/test/dialects/support.js b/test/dialects/support.js
@@ -93,6 +93,17 @@ module.exports = {
     });
   },
 
+  // This table defines the customer attributes as a composite field
+  defineCustomerCompositeTable: function() {
+    return Table.define({
+      name: 'customer',
+      columns: {
+        id: {},
+        info: {subfields: ['name', 'age', 'salary']}
+      }
+    });
+  },
+
   defineCustomerAliasTable: function() {
     return Table.define({
       name: 'customer',

Original file line number	Diff line number	Diff line change
`@@ -654,7 +654,7 @@ Postgres.prototype.visitColumn = function(columnNode) {`
`654`	`654`	`txt.push('DISTINCT(');`
`655`	`655`	`}`
`656`	`656`	`}`
`657`		`- if(!inInsertUpdateClause && !this.visitingReturning && !this._visitingCreate && !this._visitingAlter) {`
	`657`	`+ if(!inInsertUpdateClause && !this.visitingReturning && !this._visitingCreate && !this._visitingAlter && !columnNode.subfieldContainer) {`
`658`	`658`	`if(table.alias) {`
`659`	`659`	`txt.push(this.quote(table.alias));`
`660`	`660`	`} else {`
`@@ -686,6 +686,9 @@ Postgres.prototype.visitColumn = function(columnNode) {`
`686`	`686`	`}`
`687`	`687`	`}`
`688`	`688`	`else {`
	`689`	`+ if (columnNode.subfieldContainer) {`
	`690`	`+ txt.push('(' + this.visitColumn(columnNode.subfieldContainer) + ').');`
	`691`	`+ }`
`689`	`692`	`txt.push(this.quote(columnNode.name));`
`690`	`693`	`}`
`691`	`694`	`if(closeParen) {`