Browse files

Merge pull request #293 from benjie/function-types

Function return types
  • Loading branch information...
2 parents c024145 + 63dcf89 commit 75b9208acdcc41b59a27152b1520a06cdc1f92e4 @dmfay dmfay committed on GitHub Jul 29, 2016
Showing with 263 additions and 4 deletions.
  1. +6 −1 docs/connecting.md
  2. +33 −0 docs/functions.md
  3. +4 −1 index.js
  4. +73 −2 lib/executable.js
  5. +3 −0 lib/scripts/functions.sql
  6. +59 −0 test/db/schema.sql
  7. +84 −0 test/function_spec.js
  8. +1 −0 test/helpers/index.js
View
7 docs/connecting.md
@@ -7,7 +7,8 @@ massive.connect({
connectionString: 'postgres://postgres@localhost/massive',
defaults: {
poolSize: 20
- }
+ },
+ enhancedFunctions: true
}, function (err, db) {
// db contains your tables, views, and functions
})
@@ -21,3 +22,7 @@ the underlying node-postgres driver to set options such as `poolSize` or
`parseInt8`. For more complete documentation of available driver defaults, see
the [node-postgres](https://github.com/brianc/node-postgres/wiki/pg#pgdefaults)
documentation.
+
+`enhancedFunctions` is an experimental setting that allows functions to return
+a value matching their return type rather than a set of rows, see
+[Function Invocation](functions.md#function-invocation).
View
33 docs/functions.md
@@ -47,6 +47,39 @@ massive.connect({
});
```
+### Experimental: Enhanced Functions
+
+Massive can interpret the return types of functions, however this breaks backwards compatibility and thus must be opted into with setting `enhancedFunctions: true`.
+
+For example, if you have this [getRandomNumber function](https://xkcd.com/221/):
+
+```sql
+create function myschema."getRandomNumber"()
+returns integer as $$
+select 4; -- chosen by fair dice roll.
+ -- guaranteed to be random.
+$$ language sql;
+```
+
+Without `enhancedFunctions` you'd get the same results as a regular table fetch - a set of rows: `[{"getRandomNumber": 4}]`.
+
+With `enhancedFunctions: true`, you'd get simply the result of the function: `4`.
+
+```js
+var massive = require("massive");
+
+massive.connect({
+ connectionString: "postgres://localhost/massive",
+ enhancedFunctions: true // Enable return type honoring
+}, function(err, db) {
+ db.getRandomNumber(function(err, randomNumber) {
+ //randomNumber is the integer 4
+ });
+});
+```
+
+This also works for text, arrays, JSON, text domains and some other types. If it doesn't work for your use case, please raise an issue (or a PR!); we're aware of some restrictions due to [how the pg module currently handles types](https://github.com/brianc/node-postgres/issues/986).
+
## Parameters
Many, if not most, functions will expect some sort of input.
View
5 index.js
@@ -14,6 +14,7 @@ var self;
var Massive = function(args) {
this.scriptsDir = args.scripts || process.cwd() + "/db";
+ this.enhancedFunctions = args.enhancedFunctions || false;
var runner = new Runner(args.connectionString, args.defaults);
_.extend(this, runner);
@@ -440,7 +441,9 @@ Massive.prototype.loadFunctions = function(next) {
sql: sql,
schema: schema,
name : fn.name,
- db : self
+ db : self,
+ singleRow: self.enhancedFunctions && fn.return_single_row,
+ singleValue: self.enhancedFunctions && fn.return_single_value
});
MapToNamespace(_exec, "functions");
View
75 lib/executable.js
@@ -1,6 +1,43 @@
var _ = require("underscore")._;
var util = require('util');
var Entity = require('./entity');
+var Transform = require('stream').Transform;
+var inherits = require('util').inherits;
+
+// Takes a single-key object like {"foo": 27} and just returns the 27 part
+function _processSingleValue(data) {
+ if (!_.isNull(data)) {
+ var keys = Object.keys(data);
+ if (keys.length != 1) {
+ throw new Error("Was expecting just one value");
+ }
+ data = data[keys[0]];
+ }
+ return data;
+}
+
+function SingleValueStream(options) {
+ if (!(this instanceof SingleValueStream)) {
+ return new SingleValueStream(options);
+ }
+ if (!options) {
+ options = {};
+ }
+ options.objectMode = true;
+ Transform.call(this, options);
+}
+
+inherits(SingleValueStream, Transform);
+
+SingleValueStream.prototype._transform = function _transform(obj, encoding, callback) {
+ try {
+ obj = _processSingleValue(obj);
+ } catch(err) {
+ return callback(err);
+ }
+ this.push(obj);
+ callback();
+};
/**
* An executable function or script.
@@ -10,6 +47,8 @@ var Executable = function(args) {
this.sql = args.sql;
this.filePath = args.filePath;
+ this.singleRow = args.singleRow;
+ this.singleValue = args.singleValue;
};
util.inherits(Executable, Entity);
@@ -48,10 +87,42 @@ Executable.prototype.invoke = function() {
// console.log("invoke opts: ", opts);
// console.log("invoke params: ", params);
+ var singleRow = this.singleRow && !opts.stream;
+ var singleValue = this.singleValue;
+
if (opts.stream) {
- this.db.stream(this.sql, params, null, next);
+ this.db.stream(this.sql, params, null, function (err, stream) {
+ if (err) return next(err);
+ if (singleValue) {
+ var singleValueTransform = SingleValueStream();
+ return next(null, stream.pipe(singleValueTransform));
+ } else {
+ return next(null, stream);
+ }
+ });
} else {
- this.db.query(this.sql, params, null, next);
+ this.db.query(this.sql, params, null, function (err, rawData) {
+ var data = rawData;
+ if (err) return next(err);
+ try {
+ if (singleRow) {
+ if (!Array.isArray(data)) {
+ return next(new Error("Was expecting an array"));
+ }
+ data = data[0] || null;
+ if (singleValue) {
+ data = _processSingleValue(data);
+ }
+ } else {
+ if (singleValue) {
+ data = data.map(_processSingleValue);
+ }
+ }
+ } catch (e) {
+ err = e;
+ }
+ return next(err, data);
+ });
}
};
View
3 lib/scripts/functions.sql
@@ -2,10 +2,13 @@
-- inclusion/exclusion is schema - aspecific, no schema assumes 'public'
select distinct
n.nspname as "schema",
+ (not p.proretset) as "return_single_row",
+ (t.typtype in ('b', 'd', 'e', 'r')) as "return_single_value",
p.proname as "name",
p.pronargs as param_count
from pg_proc p
inner join pg_namespace n on (p.pronamespace = n.oid)
+ inner join pg_type t on (p.prorettype = t.oid)
where n.nspname not in ('pg_catalog','information_schema')
and n.nspname NOT LIKE 'pgp%'
and (case -- blacklist functions using LIKE by fully-qualified name (no schema assumes public):
View
59 test/db/schema.sql
@@ -1,4 +1,5 @@
CREATE EXTENSION IF NOT EXISTS pgcrypto;
+CREATE EXTENSION IF NOT EXISTS citext;
drop materialized view if exists mv_orders;
drop table if exists "Users";
@@ -82,6 +83,8 @@ create materialized view mv_orders as select * from orders;
drop table if exists myschema.artists cascade;
drop table if exists myschema.albums cascade; -- drops functions too
drop table if exists myschema.docs;
+drop function if exists myschema."JsonHelloWorld"();
+drop function if exists myschema."getRandomNumber"();
-- just in case:
drop table if exists myschema.doggies;
@@ -178,3 +181,59 @@ $$
select * from myschema.albums;
$$
language sql;
+
+create or replace function myschema."RandomAlbum"()
+returns myschema.albums
+as
+$$
+select * from myschema.albums order by random() limit 1;
+$$
+language sql;
+
+create or replace function myschema."getRandomNumber"()
+returns integer
+as
+$$
+select 4; -- chosen by fair dice roll.
+ -- guaranteed to be random.
+$$
+language sql;
+
+create or replace function myschema."JsonHelloWorld"()
+returns json
+as
+$$
+select '{"hello": "world"}'::json;
+$$
+language sql;
+
+create or replace function yesses() returns text[] as $$
+select array['yes', 'yes', 'yes', 'yes'];
+$$ language sql;
+
+drop function if exists coin_toss();
+drop function if exists coin_tosses();
+drop type if exists coin_toss;
+create type coin_toss as enum ('heads', 'tails');
+
+create function coin_toss() returns coin_toss as $$
+select (case when random() > 0.5 then 'heads'::coin_toss else 'tails'::coin_toss end);
+$$ language sql;
+
+-- `pg` module doesn't support arrays of custom types yet
+-- see: https://github.com/brianc/node-postgres/issues/986
+-- create function coin_tosses() returns coin_toss[] as $$
+-- select array[
+-- (case when random() > 0.5 then 'heads'::coin_toss else 'tails'::coin_toss end)
+-- , (case when random() > 0.5 then 'heads'::coin_toss else 'tails'::coin_toss end)
+-- , (case when random() > 0.5 then 'heads'::coin_toss else 'tails'::coin_toss end)
+-- ];
+-- $$ language sql;
+
+drop function if exists example_email();
+drop domain if exists email_address;
+create domain email_address as text check(value similar to '[^@]+@[^@]+.[^@]+');
+
+create function example_email() returns email_address as $$
+select 'example@example.com'::email_address;
+$$ language sql;
View
84 test/function_spec.js
@@ -119,5 +119,89 @@ describe('Functions', function () {
done();
});
});
+ it("executes schema-bound, camel-cased function RandomAlbum and returns the single row result", function (done) {
+ db.myschema.RandomAlbum(function(err,res) {
+ assert.ifError(err);
+ assert(_.isObject(res));
+ assert.deepEqual(Object.keys(res), ["id", "title", "artist_id"]);
+ done();
+ });
+ });
+ it("executes schema-bound, camel-cased function JsonHelloWorld and returns the single integer value", function (done) {
+ db.myschema.getRandomNumber(function(err,res) {
+ assert.ifError(err);
+ assert.equal(res, 4);
+ done();
+ });
+ });
+ it("executes schema-bound, camel-cased function JsonHelloWorld and returns the single JSON value", function (done) {
+ db.myschema.JsonHelloWorld(function(err,res) {
+ assert.ifError(err);
+ assert(_.isObject(res));
+ assert.deepEqual(res, {hello: "world"});
+ done();
+ });
+ });
+ it("executes function yesses and returns array of 'yes'", function (done) {
+ db.yesses(function(err,res) {
+ assert.ifError(err);
+ assert(Array.isArray(res), "Expected array");
+ res.forEach(function(el) {
+ assert.equal(el, 'yes');
+ });
+ done();
+ });
+ });
+ it("executes function coin_toss and returns 'heads' or 'tails'", function (done) {
+ db.coin_toss(function(err,res) {
+ assert.ifError(err);
+ assert(['heads', 'tails'].indexOf(res) >= 0, "'" + res + "' must be heads or tails");
+ done();
+ });
+ });
+ /*
+ // `pg` module doesn't support arrays of custom types yet
+ // see: https://github.com/brianc/node-postgres/issues/986
+ it("executes function coin_tosses and returns array of 'heads' or 'tails'", function (done) {
+ db.coin_tosses(function(err,res) {
+ assert.ifError(err);
+ console.dir(res);
+ assert(Array.isArray(res), "Expected array");
+ res.forEach(function(el) {
+ assert(['heads', 'tails'].indexOf(el) >= 0, "'" + el + "' must be heads or tails");
+ });
+ done();
+ });
+ });
+ */
+ it("executes function example_email and returns 'example@example.com'::email_address", function (done) {
+ db.example_email(function(err,res) {
+ assert.ifError(err);
+ assert.equal(res, 'example@example.com');
+ done();
+ });
+ });
+ it("executes function regexp_matches and returns stream of matches", function (done) {
+ db.regexp_matches('aaaaaaaaaaaaaaaaaaaa', 'a', 'g', {stream: true}, function(err, stream) {
+ assert.ifError(err);
+ var result = [];
+
+ stream.on('readable', function() {
+ var res = stream.read();
+
+ if (res) {
+ result.push(res);
+ }
+ });
+
+ stream.on('end', function () {
+ assert.equal(20, result.length);
+ result.forEach(function(r) {
+ assert.equal(r, 'a');
+ });
+ done();
+ });
+ });
+ });
});
});
View
1 test/helpers/index.js
@@ -9,6 +9,7 @@ exports.connectionString = connectionString;
exports.init = function(next){
massive.connect({
connectionString : connectionString,
+ enhancedFunctions : true,
scripts : scriptsDir}, next);
};

0 comments on commit 75b9208

Please sign in to comment.