array parser?

[booo]

Are there any plans to parse arrays? We get arrays from our database and would like to work with arrays in nodejs too.

[brianc]

Absolutely. If you could gist an example or better yet submit a failing test & I'll get to work on it.

[booo]

SELECT ARRAY['key', 'value', 'key key', 'value value'];

http://pastebin.com/et9AhFBX

[brianc]

thanks. I've implemented a test - wondering what you would expect to be logged when you logged that row in your example? Something like this:

var query = client.query("SELECT ARRAY['key', 'value', 'key key']");
query.on('row', function(row) {
  assert.equal(row.array[0], 'key');
  assert.equal(row.array[1], 'value');
  assert.equal(row.array[2], 'key key');
})

??

[booo]

Yes, I would expect exactly this.

[kaiko]

I've used this function (copied idea from perl DBI::Pg library):

function parseArray (arrStr) {
  if (!arrStr) return null;
  if (arrStr[0] !== '{' || arrStr[arrStr.length-1] !== '}')
    throw "Not postgresql array! (" + arrStr + ")";

  var x = arrStr.substring(1, arrStr.length - 1);
  x = x.match(/(NULL|[^,]+|"((?:.|\n|\r)*?)(?!\\)"|\{((?:.|\n|\r)*?(?!\\)\})(,|$))/mg);
  if (x === null) throw "Not postgre array";
  return x.map(function (el) {
    if (el === 'NULL') return null;
    if (el[0] === '{') return arguments.callee(el);
    if (el[0] === '"')
       return el.substring(1, el.length - 1).replace('\\"', '"');
    return el;
  });
}

[brianc]

I've pushed an initial pass at this. I've exposed a place on the Query object where you can extend and write custom type parsers (as postgres will allow you to define custom types)

Check out lib/query.js for an example, but basically you can do the following:

Query.registerParser(typeOid, parseFunction);

Where typeOid is the OID of the type from pg_type table and parseFunction is the function which takes a single parameter which is the text value of the column.

Kaiko - I've used your convert function for starters, but it does not convert the following properly:

SELECT ARRAY["one", "two", "t , h { r } e e"]

I'm holding off on doing another npm version until I have an opportunity to regression test changes against 9.x versions of PostgreSQL and make the converting more robust.

[kaiko]

This works better (still only for 1-dimension arrays):

var parseStringArray = function (val) {

if (!val || val[0] !== '{' || val[val.length-1] !== '}')
    throw Error("Not postgresql array! (" + arrStr + ")");

// S will make split and set's new inel state
// C doesn't make split but changes inel state
var S  = function (set_inel, ch) { return function () { inel = set_inel; return (typeof ch !== 'undefined') ? ch : String.fromCharCode(27); } }
var C  = function (set_inel, ch) { return function () { inel = set_inel; return (typeof ch !== 'undefined') ? ch : ''; } }
var no = function (match) { return match; } // do nothing
var X  = function () { throw new Error('Not PostgreSQL array'); }; // can't happen
var c  = ','; // just shorter writing

// matrix what found vs current state = what to do next
var actions = { //  0     1     2   // inel
     '","': [ X     ,     X,   S(2)   ] // certainly separator
    , ',"': [ S(2)  ,  S(2),   S(0,c) ] // can be at end of last item (2) or start new item (1->2)
    ,'",' : [ C(2,c),     X,   S(1)   ] // can be start new item (2) or end item with starting new item (2->1)
    ,'"'  : [ C(2)  ,     X,   C(0)   ] // can be start first item (2) or finish last item (2)
    ,','  : [ S(1)  ,  S(1),   no     ] // can be item separator (1->1) or just be in item (2)
};


var prevpos = 0, inel = 0; // "in element": not quoted element: 1, quoted element: 2
var x = val.substring(1, val.length - 1)
    // let' remove real string quotes from expression and replace these with something what there can't be in real expression
    .replace(/\\+"/g, function (match) { return match.length % 2 ? match : (match.substring(0, match.length - 2) + "\u0003") })

    // now the main thing: find separators and parse then depending current state
    .replace(/","|,"|",|"|,/g, function (match, pos, str) {
        // var l = '  ' + pos + ' ' + match +  '\tPREV '  + prevpos + '\tPOS ' + pos + '\tinel ' + inel;

        var pinel = inel;
        var R = actions[match][inel].apply(this, arguments);

        // special case NULL (without quotes), we add special char and separator
        if (pinel < 2 && str.substring(prevpos, pos).toUpperCase() === 'NULL')
            R = String.fromCharCode(1) + String.fromCharCode(27);

        // console.log(l + '=>' + inel + '\t' + str.substring(prevpos, pos));
        prevpos = pos + match.length;
        return R;
    })
    .replace(/.\u0008/g, '')
    .replace(/\u0003/g, '"')
    .split(  String.fromCharCode(27))
    .map(function (e) { return e[e.length-1] === String.fromCharCode(1) ? null : e; });
return x;
};

[brianc]

cool - want me to add it to the repo & test it out? You're also welcome to fork & do a pull request as long as the tests still pass.

[booo]

Alexander is working on a binary parser. I think it's the best way to do it. Check out https://github.com/AlexanderS/node-postgres Perhaps this could be integrated somehow? Perhaps we have to add support for other data types and we should write some tests :)

[brianc]

yeah he sent a pull request, but it has no tests written. Once test are written for the binary stuff, I'd be more than happy to pull.

[dvv]

Hi! What's the status of the subj? TIA

[brianc]

The status of the array parser?

The array parser implementation can be "swapped out" by the above mentioned method. I've not received any pull requests in regards to a better implementation. If you have one (with tests) I'd be happy to merge it in.

Or of Alexander's changes?

Alexander's changes are still lacking any test coverage unfortunately. :(

[dvv]

I see. I just yesterday discovered this project and am way immature to offer. So far just pulling the info and exploring the minefield ;)

[booo]

We will work on tests in the next 4 weeks...

[dvv]

TIA. BTW, how at all arrays are useful comparing to normalized out data? I'm a noob to postgres -- what pros/cons do you see?