Monday, 15 May 2017

Postgres backend function not returning expected results in node server

I am done banging my head against the keyboard on this one. I'm using a postgres back end, with a node server and pghelper on top. I have one function working perfectly fine. See below.

Server side code

findAllRegisteredTokensByNotificationId: function(req,res, next){
    try{
        var route = '[GET] - /api/device-tokens/' + req.params.nid;
        Auth.validateHeader(req.headers, route, res, function(){
            if(req && req.params && req.params.nid){
                pgHelper.connectDBAndFindResult('getAllTokensByNotificationId',[req.params.nid], res, function(result){
                    if(result){
                        return res.status(200).json({ data: result.rows, message: 'Retrieved ALL registered device tokens.'});
                    }
                    else{
                        return res.status(500).json({ message: 'Error finding registered device tokens.'});
                    }
                });
            }
            else{
                logger.processError('Error retrieving registered device tokens: Not a valid Request.', res, 500);
            }   
        });
    }   
    catch(err){
        logger.processError('Error finding Device Tokens:' + JSON.stringify(err.message), res,500);
    }
}

DB side code

-- FUNCTION: stardb.getallregisteredtokensfornotification(integer)

-- DROP FUNCTION stardb.getallregisteredtokensfornotification(integer);

CREATE OR REPLACE FUNCTION stardb.getallregisteredtokensfornotification(
integer)
RETURNS SETOF "TABLE(eid text, platform text, device_type text)"
LANGUAGE 'plpgsql'
COST 100.0
VOLATILE 
ROWS 1000.0
AS $function$

BEGIN
FOR eid, platform, device_type IN
SELECT DT.eid, DT.platform, DT.device_type
    FROM notification_token NT JOIN device_token DT
    ON NT.token_id = DT.token_id and NT.notification_id = ($1)
LOOP
RETURN NEXT;
END LOOP;
RETURN;     
END;

$function$;

ALTER FUNCTION stardb.getallregisteredtokensfornotification(integer)
OWNER TO stardb;

Then I have another function here that works fine when you run the query on the db, but when called through the node server it gives an unexpected result.

Server side code

findAllRegisteredTokensByApplicationId: function(req,res, next){
    try{
        var route = '[GET] - /api/device-tokens/tokens/' + req.params.aid;
        Auth.validateHeader(req.headers, route, res, function(){
            if(req && req.params && req.params.aid){
                pgHelper.connectDBAndFindResult('getallregisteredtokensbyappid',[req.params.aid], res, function(result){
                    if(result){
                        return res.status(200).json({ data: result, message: 'Retrieved ALL registered device tokens per App'});
                    }
                    else{
                        return res.status(500).json({ message: 'Error finding registered device tokens.'});
                    }
                });
            }
            else{
                logger.processError('Error retrieving registered device tokens: Not a valid Request.', res, 500);
            }   
        });
    }   
    catch(err){
        logger.processError('Error finding Device Tokens:' + JSON.stringify(err.message), res,500);
    }
},

DB Side Code

-- FUNCTION: stardb.getallregisteredtokensbyappid(integer)

-- DROP FUNCTION stardb.getallregisteredtokensbyappid(integer);

CREATE OR REPLACE FUNCTION stardb.getallregisteredtokensbyappid(
integer)
RETURNS SETOF device_token 
LANGUAGE 'plpgsql'
COST 100.0
VOLATILE 
ROWS 1000.0
AS $function$

DECLARE
r device_token%rowtype;
BEGIN
FOR r in SELECT token_id, token, eid, platform, device_type, app_id 
         FROM device_token where app_id = ($1)
LOOP
    RETURN next r;
END LOOP;
RETURN;
END;    

$function$;

ALTER FUNCTION stardb.getallregisteredtokensbyappid(integer)
OWNER TO stardb;

Expected result

{
"data": [
{
  "token_id": 15,
  "token": "zzzzzzzzzzzz",
  "id": "dddddddd",
  "platform": "iOS",
  "device_type": "iPhone",
  "app_id": 1
},
{
  "token_id": 16,
  "token": "1234",
  "eid": "BAD-TO",
  "platform": "iOS",
  "device_type": "iPhone",
  "app_id": 1
}
 ], "message": "Retrieved ALL registered device tokens." }

Actual result

{
"data": {
"command": null,
"rowCount": null,
"oid": null,
"rows": [],
"fields": [],
"_parsers": [],
"RowCtor": null,
"rowAsArray": false
 },
    "message": "Retrieved ALL registered device tokens."}

I can provide any other snippets needed. Thank you for any help!



via James Cockerham

No comments:

Post a Comment