Node-red workflow that accesses data in Postgres

[{"id":"a88d32e7718a6d87","type":"postgres","z":"82c72b84094f383a","postgresdb":"1f17c4cb95c3dc20","name":"EXampleDataBase","output":true,"perrow":false,"rowspermsg":"1","return_on_error":false,"limit_queries":"0","limit_by":"payload","limit_value":"1","limit_drop_intermediate":false,"limit_drop_if_in_queue":false,"outputs":true,"x":550,"y":60,"wires":[["889b8287edfa50b0"]]},{"id":"6e67bb4ca0f1c667","type":"function","z":"82c72b84094f383a","name":"function  ","func":"msg.payload = `CREATE TABLE SQLusers(\n    user_id serial PRIMARY KEY,\n    name  TEXT,\n    age INTEGER,\n    country TEXT\n)`\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":340,"y":60,"wires":[["a88d32e7718a6d87"]]},{"id":"889b8287edfa50b0","type":"debug","z":"82c72b84094f383a","name":"debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":770,"y":60,"wires":[]},{"id":"07b63f8f7b2d50e8","type":"postgres","z":"82c72b84094f383a","postgresdb":"1f17c4cb95c3dc20","name":"EXampleDataBase","output":true,"perrow":false,"rowspermsg":"1","return_on_error":false,"limit_queries":"0","limit_by":"payload","limit_value":"1","limit_drop_intermediate":false,"limit_drop_if_in_queue":false,"outputs":true,"x":750,"y":280,"wires":[["75a8d8d72fa4cf76","189946ae6106a006"]]},{"id":"f8ecac28d7c68e9e","type":"function","z":"82c72b84094f383a","name":"function   Add db ","func":"msg.payload = `INSERT INTO SQLusers(name, age, country)\nVALUES (\n '${msg.payload.name}',\n '${msg.payload.age}', \n '${msg.payload.counrty}')\n returning *\n`\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":540,"y":280,"wires":[["07b63f8f7b2d50e8","d746e83afd4fb8d4"]]},{"id":"75a8d8d72fa4cf76","type":"debug","z":"82c72b84094f383a","name":"debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":930,"y":280,"wires":[]},{"id":"550336c689e343c3","type":"inject","z":"82c72b84094f383a","name":"SELECT","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":120,"y":340,"wires":[["9c9e65c71da30a4e"]]},{"id":"1383d31dfed255e2","type":"inject","z":"82c72b84094f383a","name":"DELETE","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":140,"y":500,"wires":[["1c07298c15531bd0"]]},{"id":"ed18c5ff0b796b9d","type":"inject","z":"82c72b84094f383a","name":"DROP TABLE","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"DROP TABLE LEDSTATUS","payload":"","payloadType":"date","x":150,"y":600,"wires":[["6a4d4b4432f8dfb6"]]},{"id":"074877409dc11f16","type":"inject","z":"82c72b84094f383a","name":"View Records","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":150,"y":660,"wires":[["01b19c4e5720c7ee"]]},{"id":"703e53c1c5ccbb6c","type":"ui_template","z":"82c72b84094f383a","group":"a7f7734f2e8432de","name":"UI Table","order":1,"width":6,"height":6,"format":"<table style=\"width:100%\">\n  <tr>\n    <th>user_id</th> \n    <th>name</th> \n    <th>age</th>     \n    <th>country</th>\n  </tr>\n  <tr ng-repeat=\"x in msg.payload | limitTo:20\">\n    <td>{{msg.payload[$index].user_id}}</td>\n    <td>{{msg.payload[$index].name}}</td> \n    <td>{{msg.payload[$index].age}}</td>\n    <td>{{msg.payload[$index].country}}</td>  \n  </tr>\n</table>","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":false,"templateScope":"local","className":"","x":660,"y":660,"wires":[["c188bd4001cbf8aa"]]},{"id":"c24149be7975129c","type":"inject","z":"82c72b84094f383a","name":"CREATE","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":120,"y":80,"wires":[["6e67bb4ca0f1c667"]]},{"id":"dc380219c6ad1e52","type":"inject","z":"82c72b84094f383a","name":"INSERT","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":120,"y":280,"wires":[["6d1dde0e8dc69e4e"]]},{"id":"6d1dde0e8dc69e4e","type":"function","z":"82c72b84094f383a","name":"function ","func":"\nmsg.payload={\n    name: 'Alex',\n    age: 50,\n    counrty: '大里'\n}\n\nreturn msg;\n\n//msg.payload = `INSERT INTO users(name, age, country)\n//VALUES (\n// '${msg.payload.name}',\n// '${msg.payload.age}', \n// '${msg.payload.counrty}')\n// returning *\n// `\n//return msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":280,"y":280,"wires":[["f8ecac28d7c68e9e","2f2b13465b51f650"]]},{"id":"d746e83afd4fb8d4","type":"debug","z":"82c72b84094f383a","name":"debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":710,"y":240,"wires":[]},{"id":"2f2b13465b51f650","type":"debug","z":"82c72b84094f383a","name":"debug","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":430,"y":220,"wires":[]},{"id":"9c9e65c71da30a4e","type":"function","z":"82c72b84094f383a","name":"Select","func":"msg.payload = `SELECT * FROM SQLusers `\n\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":270,"y":340,"wires":[["3b9f8156b761430b","cacb9953be31ce2e"]]},{"id":"3b9f8156b761430b","type":"debug","z":"82c72b84094f383a","name":"debug  ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":450,"y":380,"wires":[]},{"id":"cacb9953be31ce2e","type":"postgres","z":"82c72b84094f383a","postgresdb":"1f17c4cb95c3dc20","name":"EXampleDataBase","output":true,"perrow":false,"rowspermsg":"1","return_on_error":false,"limit_queries":"0","limit_by":"payload","limit_value":"1","limit_drop_intermediate":false,"limit_drop_if_in_queue":false,"outputs":true,"x":490,"y":340,"wires":[["88fee6ba4797e2a1"]]},{"id":"88fee6ba4797e2a1","type":"debug","z":"82c72b84094f383a","name":"debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":670,"y":340,"wires":[]},{"id":"2bc5919c9b56f68e","type":"postgres","z":"82c72b84094f383a","postgresdb":"1f17c4cb95c3dc20","name":"EXampleDataBase","output":true,"perrow":false,"rowspermsg":"1","return_on_error":false,"limit_queries":"0","limit_by":"payload","limit_value":"1","limit_drop_intermediate":false,"limit_drop_if_in_queue":false,"outputs":true,"x":490,"y":660,"wires":[["703e53c1c5ccbb6c","06acda8cfb11609f"]]},{"id":"1c07298c15531bd0","type":"function","z":"82c72b84094f383a","name":"function ","func":"var id=msg.payload;\n\nmsg.payload={\n    user_id: id\n}\n\nreturn msg;\n\n//msg.payload = `DELETE FROM users\n//WHERE user_id = ${msg.payload.user_id}\n//RETURNING *;`\n//return msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":320,"y":500,"wires":[["4292afd758f13dce","37d3c1ca7726d122"]]},{"id":"4292afd758f13dce","type":"function","z":"82c72b84094f383a","name":"function  delete 1 id","func":"msg.payload = `DELETE FROM SQLusers\nWHERE user_id = ${msg.payload.user_id}\nRETURNING *;`\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":510,"y":500,"wires":[["c1a1bcc14f5c9ed5","3c87dfae52e1d178"]]},{"id":"c1a1bcc14f5c9ed5","type":"postgres","z":"82c72b84094f383a","postgresdb":"1f17c4cb95c3dc20","name":"EXampleDataBase","output":true,"perrow":false,"rowspermsg":"1","return_on_error":false,"limit_queries":"0","limit_by":"payload","limit_value":"1","limit_drop_intermediate":false,"limit_drop_if_in_queue":false,"outputs":true,"x":750,"y":500,"wires":[["fa66e2c0270c270e"]]},{"id":"fa66e2c0270c270e","type":"debug","z":"82c72b84094f383a","name":"debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":910,"y":500,"wires":[]},{"id":"3c87dfae52e1d178","type":"debug","z":"82c72b84094f383a","name":"debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":710,"y":440,"wires":[]},{"id":"37d3c1ca7726d122","type":"debug","z":"82c72b84094f383a","name":"debug","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":470,"y":540,"wires":[]},{"id":"6a4d4b4432f8dfb6","type":"function","z":"82c72b84094f383a","name":"function  ","func":"\nmsg.payload = `DROP TABLE IF EXISTS SQLusers` \nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":340,"y":600,"wires":[["8bda1a5b4aeda1f4"]]},{"id":"8bda1a5b4aeda1f4","type":"postgres","z":"82c72b84094f383a","postgresdb":"1f17c4cb95c3dc20","name":"EXampleDataBase","output":true,"perrow":false,"rowspermsg":"1","return_on_error":false,"limit_queries":"0","limit_by":"payload","limit_value":"1","limit_drop_intermediate":false,"limit_drop_if_in_queue":false,"outputs":true,"x":530,"y":600,"wires":[["27a88695362e63a1"]]},{"id":"27a88695362e63a1","type":"debug","z":"82c72b84094f383a","name":"debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":730,"y":600,"wires":[]},{"id":"01b19c4e5720c7ee","type":"function","z":"82c72b84094f383a","name":"Select","func":"msg.payload = `SELECT * FROM SQLusers  ORDER BY user_id DESC LIMIT 20;`\n\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":310,"y":660,"wires":[["2bc5919c9b56f68e","ef1b53821c15a5f8"]]},{"id":"c188bd4001cbf8aa","type":"debug","z":"82c72b84094f383a","name":"debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":970,"y":660,"wires":[]},{"id":"532335a5cfaa3f96","type":"inject","z":"82c72b84094f383a","name":"INSERT 2","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":120,"y":140,"wires":[["77ccb41e682022ef","44e2eef69fb5343a","5f68cc23eed341e6"]]},{"id":"77ccb41e682022ef","type":"random-string","z":"82c72b84094f383a","size":"6","characters":"abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMOPQRSTUVWXYZ","property":"payload","x":320,"y":140,"wires":[["d64ba6f5760baddb"]]},{"id":"44e2eef69fb5343a","type":"random-string","z":"82c72b84094f383a","size":"2","characters":"莿桐佳冬牡丹蘆竹大樹路竹竹塘花壇柳營麻豆竹山楊梅卓蘭","property":"payload","x":320,"y":180,"wires":[["1ee2a2f689163ccc"]]},{"id":"5f68cc23eed341e6","type":"random","z":"82c72b84094f383a","name":"","low":"10","high":"75","inte":"true","property":"payload","x":300,"y":100,"wires":[["f9e89d24b84dd7cb"]]},{"id":"ceb62876fd1a340d","type":"debug","z":"82c72b84094f383a","name":"debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":830,"y":140,"wires":[]},{"id":"f9e89d24b84dd7cb","type":"function","z":"82c72b84094f383a","name":"function age","func":"var age=msg.payload;\nflow.set('age',age);\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":510,"y":100,"wires":[[]]},{"id":"e1bb6e59cb0ca4a4","type":"function","z":"82c72b84094f383a","name":"function","func":"var name1=flow.get('name');\nvar age1=flow.get('age');\nvar counrty1=flow.get('counrty');\n\n\nmsg.payload={\n    name: name1,\n    age: age1,\n    counrty: counrty1\n}\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":700,"y":140,"wires":[["ceb62876fd1a340d","f8ecac28d7c68e9e"]]},{"id":"d64ba6f5760baddb","type":"function","z":"82c72b84094f383a","name":"function name","func":"var name=msg.payload;\nflow.set('name',name);\nreturn msg;\n\n\n//msg.payload = `INSERT INTO users(name, age, country)\n//VALUES (\n// '${msg.payload.name}',\n// '${msg.payload.age}', \n// '${msg.payload.counrty}')\n// returning *\n// `\n//return msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":520,"y":140,"wires":[["e1bb6e59cb0ca4a4"]]},{"id":"1ee2a2f689163ccc","type":"function","z":"82c72b84094f383a","name":"function counrty","func":"var counrty=msg.payload;\nflow.set('counrty',counrty);\nreturn msg;\n\n\n//msg.payload = `INSERT INTO users(name, age, country)\n//VALUES (\n// '${msg.payload.name}',\n// '${msg.payload.age}', \n// '${msg.payload.counrty}')\n// returning *\n// `\n//return msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":520,"y":180,"wires":[[]]},{"id":"f8e5f7b72e9f02e1","type":"ui_button","z":"82c72b84094f383a","name":"","group":"88c17cd9a0f2b946","order":1,"width":0,"height":0,"passthru":false,"label":"建立資料庫","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"str","topic":"topic","topicType":"msg","x":110,"y":40,"wires":[["6e67bb4ca0f1c667"]]},{"id":"ed37f761f8bcc278","type":"ui_button","z":"82c72b84094f383a","name":"","group":"88c17cd9a0f2b946","order":4,"width":0,"height":0,"passthru":false,"label":"刪除一筆資料","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"str","topic":"topic","topicType":"msg","x":140,"y":420,"wires":[["cb1d8b0bf333e50a"]]},{"id":"310a3736863db1d4","type":"ui_button","z":"82c72b84094f383a","name":"","group":"88c17cd9a0f2b946","order":2,"width":0,"height":0,"passthru":false,"label":"新增一筆資料","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"str","topic":"topic","topicType":"msg","x":120,"y":180,"wires":[["5f68cc23eed341e6","77ccb41e682022ef","44e2eef69fb5343a"]]},{"id":"dfc122efcbb1f4d7","type":"ui_text_input","z":"82c72b84094f383a","name":"","label":"刪除的id","tooltip":"","group":"88c17cd9a0f2b946","order":3,"width":0,"height":0,"passthru":true,"mode":"number","delay":300,"topic":"topic","sendOnBlur":true,"className":"","topicType":"msg","x":120,"y":460,"wires":[["6adbd5babdf4709a"]]},{"id":"6adbd5babdf4709a","type":"function","z":"82c72b84094f383a","name":"function ","func":"var id=msg.payload;\nflow.set('id',id);\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":260,"y":460,"wires":[[]]},{"id":"effe47628a9be9a9","type":"ui_button","z":"82c72b84094f383a","name":"","group":"88c17cd9a0f2b946","order":5,"width":0,"height":0,"passthru":false,"label":"刪除資料庫","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"str","topic":"topic","topicType":"msg","x":130,"y":560,"wires":[["6a4d4b4432f8dfb6"]]},{"id":"293ecfab93a2c433","type":"ui_button","z":"82c72b84094f383a","name":"","group":"88c17cd9a0f2b946","order":6,"width":0,"height":0,"passthru":false,"label":"檢視資料","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"str","topic":"topic","topicType":"msg","x":120,"y":700,"wires":[["01b19c4e5720c7ee"]]},{"id":"189946ae6106a006","type":"link out","z":"82c72b84094f383a","name":"link out 53","mode":"link","links":["a2b21016e78c639e"],"x":855,"y":320,"wires":[]},{"id":"a2b21016e78c639e","type":"link in","z":"82c72b84094f383a","name":"link in 47","links":["189946ae6106a006"],"x":295,"y":720,"wires":[["01b19c4e5720c7ee"]]},{"id":"cb1d8b0bf333e50a","type":"function","z":"82c72b84094f383a","name":"function ","func":"var id1=flow.get('id');\n\nmsg.payload={\n    user_id: id1\n}\n\nreturn msg;\n\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":300,"y":420,"wires":[["4292afd758f13dce"]]},{"id":"ef1b53821c15a5f8","type":"debug","z":"82c72b84094f383a","name":"debug 281","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":490,"y":760,"wires":[]},{"id":"06acda8cfb11609f","type":"debug","z":"82c72b84094f383a","name":"debug 282","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":690,"y":760,"wires":[]},{"id":"1f17c4cb95c3dc20","type":"postgresdb","cfgname":"EXampleDataBase","hostname":"localhost","port":"5432","db":"EXampleDataBase","ssl":false},{"id":"a7f7734f2e8432de","type":"ui_group","name":"TestDataBase","tab":"564bbb77340e1ba1","order":1,"disp":true,"width":"6","collapse":false,"className":""},{"id":"88c17cd9a0f2b946","type":"ui_group","name":"命令選單","tab":"564bbb77340e1ba1","order":2,"disp":true,"width":"6","collapse":false,"className":""},{"id":"564bbb77340e1ba1","type":"ui_tab","name":"PostgreSQL","icon":"dashboard","order":106,"disabled":false,"hidden":false}]