Insert into PostgreSQL database

[jomacdon]

Hi,
Here is a screen shot of the output from the API request (Sensors) array contains 1422 Objects:

Now the output from the function node (taking away last change) removing the moustache:

And finally my Flow:

[{"id":"e6d7d6d3.98ed08","type":"tab","label":"Flow 4","disabled":false,"info":""},{"id":"bea21dcb.82e6a","type":"inject","z":"e6d7d6d3.98ed08","name":"","repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":240,"y":140,"wires":[["a77381fe.655a6"]]},{"id":"a77381fe.655a6","type":"function","z":"e6d7d6d3.98ed08","name":"Auth Key","func":"msg.headers = {    \n    \"Content-Type\": \"application/json\"\n    };\n\n\nmsg.payload = {auth_key:\"069e2b9c-682d-4780-83c2-d87fe07e87dc\"};\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":400,"y":140,"wires":[["be173f4f.cf483"]]},{"id":"be173f4f.cf483","type":"http request","z":"e6d7d6d3.98ed08","name":"RESTful API Get Sensors","method":"GET","ret":"obj","paytoqs":"ignore","url":"https://api.ecostruxureit.com/rest/v1/organizations/069e2b9c-682d-4780-83c2-d87fe07e87dc/sensors","tls":"","persist":false,"proxy":"","authType":"bearer","x":410,"y":240,"wires":[["d445179a.6f0648"]]},{"id":"d445179a.6f0648","type":"split","z":"e6d7d6d3.98ed08","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":350,"y":340,"wires":[["cbcd0743.726808"]]},{"id":"cbcd0743.726808","type":"split","z":"e6d7d6d3.98ed08","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":490,"y":340,"wires":[["535aebd3.7e9fc4"]]},{"id":"535aebd3.7e9fc4","type":"function","z":"e6d7d6d3.98ed08","name":"","func":"msg.params = ['{{payload.id}}', '{{payload.deviceId}}', '{{payload.name}}', '{{payload.unit}}']\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":630,"y":340,"wires":[["619f105e.f998c"]]},{"id":"938430e8.27833","type":"postgrestor","z":"e6d7d6d3.98ed08","name":"","query":"INSERT INTO SENSORS (id, device_id, name, unit) VALUES ($1, $2, $3, $4);","postgresDB":"74bfc636.269f68","output":true,"outputs":1,"x":810,"y":340,"wires":[[]]},{"id":"dd369c3b.f317e","type":"debug","z":"e6d7d6d3.98ed08","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":770,"y":460,"wires":[]},{"id":"619f105e.f998c","type":"debug","z":"e6d7d6d3.98ed08","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":770,"y":260,"wires":[]},{"id":"74bfc636.269f68","type":"postgresDB","z":"","name":"postgres@127.0.0.1:5432/testdb2","host":"127.0.0.1","hostFieldType":"str","port":"5432","portFieldType":"num","database":"testdb2","databaseFieldType":"str","ssl":"false","sslFieldType":"bool","max":"10","maxFieldType":"num","min":"1","minFieldType":"num","idle":"1000","idleFieldType":"num","user":"postgres","userFieldType":"str","password":"19Glasgow69","passwordFieldType":"str"}]

Thank you!

[UnborN]

1. im confused .. from your last photo we actually see a result from function node
2. when i import your flow i see that your function still has the moustache '{{payload.id}}' systax. did you remove it ?

if you try this in function ?

let id = msg.payload.id;
let deviceId = msg.payload.deviceId;
let name = msg.payload.name;
let unit = msg.payload.unit;

msg.params = [id, deviceId, name, unit];
return msg;

ps. your api doesnt give a reply for me ..
and also you said you get 1422 Objects from the API .. you want a sinle object to be inserted in the db or all 1422 ??

[jomacdon]

Hi,

No, I didn't try this function as it's the first time I have seen it, BUT...
I tried it just now and WOW!:

It worked!!!
THANK YOU, THANK YOU, THANK YOU!!!
I was wondering if it would EVER work - you did it!!!

[UnborN]

cool

I must admit that we didnt fully understand the structure of the data coming from the API and the docs on PostgreSQL were not so clear but im glad it worked .. all the best