Returned json "hex-like" string seems to have unhandled escaping

[ReinsBrain]

I've simplified my postgres function to illustrate an unexpected result. Here is how to recreate the scenario:

CREATE DOMAIN route AS bytea CHECK(length(value) <= 8);
CREATE DOMAIN rand AS bytea CHECK(length(value) <=8);
CREATE TYPE rkey AS ( route route, rand rand, created bigint );
CREATE OR REPLACE FUNCTION rkeytohex( Prkey rkey )
RETURNS TEXT AS $$
    SELECT lower( (Prkey).route ||':'|| (Prkey).rand ||':'|| to_hex((Prkey).created) );
$$ LANGUAGE SQL VOLATILE;
CREATE OR REPLACE FUNCTION test_1356 () RETURNS json AS $$
    BEGIN
        RETURN json_build_array('0:0:0',rkeytohex('(0,0,0)'::rkey));
    END;
$$ LANGUAGE plpgsql;

Executing test_1356 in psql or pgadmin returns json as expected:

[ "0:0:0", "0:0:0" ]

via nodepostgres I get:

[ '0:0:0', '\\x303a303a0' ]

Stepping through code (in connection.js), I saw that it seems that is what is indeed emitted from the postgres server. It looks like some kind of escaping that is not being managed by node-postgres but is being handled by psql and pgadmin. And, I suspect, it seems it has something to do with the domain and types I've created although I'm not returning types as my function rkeytohex returns text... it's odd.

[charmander]

I can’t reproduce this:

test=> CREATE TABLE test_1356 (data jsonb NOT NULL);
CREATE TABLE
test=> INSERT INTO test_1356 VALUES ('[[0, "0:0:0", 2, 0], [0, "f00a:123:15cf2dba258", 2, 1498723099224]]');
INSERT 0 1
test=> SELECT * FROM test_1356;
                                data                                 
---------------------------------------------------------------------
 [[0, "0:0:0", 2, 0], [0, "f00a:123:15cf2dba258", 2, 1498723099224]]
(1 row)

'use strict';

const pg = require('./');

const pool = new pg.Pool({
  host: '/var/run/postgresql',
  user: 'test',
  database: 'test',
});

pool.query('SELECT * FROM test_1356').then(result => {
  console.log(result.rows[0].data);
  pool.end();
});

$ node test1356.js
[ [ 0, '0:0:0', 2, 0 ],
  [ 0, 'f00a:123:15cf2dba258', 2, 1498723099224 ] ]

The result is the same after changing the type from jsonb to json.

Could you show a minimal setup and JavaScript query that will present the problem, please?

[charmander]

Sorry, I missed that it was a function. The results are the same, though, with

CREATE FUNCTION test_1356 () RETURNS jsonb AS $$
    BEGIN
        RETURN '[[0, "0:0:0", 2, 0], [0, "f00a:123:15cf2dba258", 2, 1498723099224]]';
    END;
$$ LANGUAGE plpgsql;

and

pool.query('SELECT test_1356()').then(result => {
  console.log(result.rows[0].test_1356);
  ⋮

[ReinsBrain]

I've thought about it some more and I don't think this should be considered an issue after all. Just because pgadmin unescapes results doesn't mean node-postres should do the same (actually it should i think). I've refactored my solution to move away from bytea which doesn't really work as i would like it to. Learned my lesson. If I could delete this issue I would (instead it's here for posterity - ayayay). Perhaps it might be of value to others to know that coercing a bytea to text may not what you expect... and, there is no type "hexstring" that you can cast to (you only have the literal form x'F000').