PostgerSQL 與 Node-Red

 PostgerSQL DataBase  與 Node-Red

[{"id":"368fd7c606e21cb8","type":"function","z":"332ada08f7c3884c","name":"function  ","func":"msg.payload = `CREATE TABLE users(\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":80,"wires":[["022940eec34e041c"]]},{"id":"a6fd40995423c258","type":"debug","z":"332ada08f7c3884c","name":"debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":770,"y":80,"wires":[]},{"id":"dbf51f2fcc3b143f","type":"function","z":"332ada08f7c3884c","name":"function   Add db ","func":"msg.payload = `INSERT INTO users(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":300,"wires":[["7599576986151811","dd33360f7276583f"]]},{"id":"5d3303b659ae7f6a","type":"debug","z":"332ada08f7c3884c","name":"debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":890,"y":300,"wires":[]},{"id":"5aa6c63c158de02d","type":"inject","z":"332ada08f7c3884c","name":"SELECT","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":120,"y":360,"wires":[["4cbfc7dcace15357"]]},{"id":"60a1d235017ebe03","type":"inject","z":"332ada08f7c3884c","name":"DELETE","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":140,"y":520,"wires":[["94046098bbf1addc"]]},{"id":"c844e70b99dcd33a","type":"inject","z":"332ada08f7c3884c","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":620,"wires":[["0a43d4e75df79b55"]]},{"id":"3a4d50f5cdcd3f0c","type":"inject","z":"332ada08f7c3884c","name":"View Records","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":150,"y":680,"wires":[["509b81485d309434"]]},{"id":"82d575b127105d8d","type":"ui_template","z":"332ada08f7c3884c","group":"eac63b546871af8c","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":640,"y":680,"wires":[["4d536e61de01044f"]]},{"id":"99e2ec5923c2c192","type":"inject","z":"332ada08f7c3884c","name":"CREATE","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":120,"y":100,"wires":[["368fd7c606e21cb8"]]},{"id":"73088c78162be0b5","type":"inject","z":"332ada08f7c3884c","name":"INSERT","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":120,"y":300,"wires":[["994de9c68c3b6035"]]},{"id":"994de9c68c3b6035","type":"function","z":"332ada08f7c3884c","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":300,"wires":[["dbf51f2fcc3b143f","a09f5660dab67365"]]},{"id":"7599576986151811","type":"debug","z":"332ada08f7c3884c","name":"debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":710,"y":260,"wires":[]},{"id":"a09f5660dab67365","type":"debug","z":"332ada08f7c3884c","name":"debug","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":430,"y":240,"wires":[]},{"id":"4cbfc7dcace15357","type":"function","z":"332ada08f7c3884c","name":"Select","func":"msg.payload = `SELECT * FROM users `\n\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":270,"y":360,"wires":[["da2b4730565f24be","54a55197ee853aff"]]},{"id":"da2b4730565f24be","type":"debug","z":"332ada08f7c3884c","name":"debug  ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":450,"y":400,"wires":[]},{"id":"4c5713185701dd7a","type":"debug","z":"332ada08f7c3884c","name":"debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":630,"y":360,"wires":[]},{"id":"94046098bbf1addc","type":"function","z":"332ada08f7c3884c","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":520,"wires":[["efa890e372a46018","50114b26c3c776b4"]]},{"id":"efa890e372a46018","type":"function","z":"332ada08f7c3884c","name":"function  delete 1 id","func":"msg.payload = `DELETE FROM users\nWHERE user_id = ${msg.payload.user_id}\nRETURNING *;`\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":530,"y":520,"wires":[["d0d028556668a8c9","401d22293b1f6ebe"]]},{"id":"b178cc2ae4246ed8","type":"debug","z":"332ada08f7c3884c","name":"debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":870,"y":520,"wires":[]},{"id":"d0d028556668a8c9","type":"debug","z":"332ada08f7c3884c","name":"debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":710,"y":460,"wires":[]},{"id":"50114b26c3c776b4","type":"debug","z":"332ada08f7c3884c","name":"debug","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":470,"y":560,"wires":[]},{"id":"0a43d4e75df79b55","type":"function","z":"332ada08f7c3884c","name":"function  ","func":"\nmsg.payload = `DROP TABLE IF EXISTS users` \nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":340,"y":620,"wires":[["bd22e5d304c51305"]]},{"id":"f8d77aa8821a36f2","type":"debug","z":"332ada08f7c3884c","name":"debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":670,"y":620,"wires":[]},{"id":"509b81485d309434","type":"function","z":"332ada08f7c3884c","name":"Select","func":"msg.payload = `SELECT * FROM users `\n\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":310,"y":680,"wires":[["4471e445acf1cb53"]]},{"id":"4d536e61de01044f","type":"debug","z":"332ada08f7c3884c","name":"debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":790,"y":680,"wires":[]},{"id":"0955139fc98c2b3a","type":"inject","z":"332ada08f7c3884c","name":"INSERT 2","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":120,"y":160,"wires":[["a1389a3387c03c49","14c6411e71e4a2d8","87e58dab50e5374c"]]},{"id":"a1389a3387c03c49","type":"random-string","z":"332ada08f7c3884c","size":"6","characters":"abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMOPQRSTUVWXYZ","property":"payload","x":320,"y":160,"wires":[["883de01c6e83dbce"]]},{"id":"14c6411e71e4a2d8","type":"random-string","z":"332ada08f7c3884c","size":"2","characters":"莿桐佳冬牡丹蘆竹大樹路竹竹塘花壇柳營麻豆竹山楊梅卓蘭","property":"payload","x":320,"y":200,"wires":[["1fc83997d4b318b3"]]},{"id":"87e58dab50e5374c","type":"random","z":"332ada08f7c3884c","name":"","low":"10","high":"75","inte":"true","property":"payload","x":300,"y":120,"wires":[["0a351e32feb580bb"]]},{"id":"217866abe56a5664","type":"debug","z":"332ada08f7c3884c","name":"debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":830,"y":160,"wires":[]},{"id":"0a351e32feb580bb","type":"function","z":"332ada08f7c3884c","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":120,"wires":[[]]},{"id":"41f8d2287274be88","type":"function","z":"332ada08f7c3884c","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":160,"wires":[["217866abe56a5664","dbf51f2fcc3b143f"]]},{"id":"883de01c6e83dbce","type":"function","z":"332ada08f7c3884c","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":160,"wires":[["41f8d2287274be88"]]},{"id":"1fc83997d4b318b3","type":"function","z":"332ada08f7c3884c","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":200,"wires":[[]]},{"id":"711b8e83b4705634","type":"ui_button","z":"332ada08f7c3884c","name":"","group":"99c5c3145bc0ee30","order":1,"width":0,"height":0,"passthru":false,"label":"建立資料庫","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"str","topic":"topic","topicType":"msg","x":110,"y":60,"wires":[["368fd7c606e21cb8"]]},{"id":"fcbaaa9ea881c669","type":"ui_button","z":"332ada08f7c3884c","name":"","group":"99c5c3145bc0ee30","order":4,"width":0,"height":0,"passthru":false,"label":"刪除一筆資料","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"str","topic":"topic","topicType":"msg","x":140,"y":440,"wires":[["379b9d0827becc9d"]]},{"id":"76e85b59f83cc448","type":"ui_button","z":"332ada08f7c3884c","name":"","group":"99c5c3145bc0ee30","order":2,"width":0,"height":0,"passthru":false,"label":"新增一筆資料","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"str","topic":"topic","topicType":"msg","x":120,"y":200,"wires":[["87e58dab50e5374c","a1389a3387c03c49","14c6411e71e4a2d8"]]},{"id":"5dee94da6f9c5176","type":"ui_text_input","z":"332ada08f7c3884c","name":"","label":"刪除的id","tooltip":"","group":"99c5c3145bc0ee30","order":3,"width":0,"height":0,"passthru":true,"mode":"number","delay":300,"topic":"topic","sendOnBlur":true,"className":"","topicType":"msg","x":120,"y":480,"wires":[["88438e9442722009"]]},{"id":"88438e9442722009","type":"function","z":"332ada08f7c3884c","name":"function ","func":"var id=msg.payload;\nflow.set('id',id);\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":260,"y":480,"wires":[[]]},{"id":"12e5335234757b7c","type":"ui_button","z":"332ada08f7c3884c","name":"","group":"99c5c3145bc0ee30","order":5,"width":0,"height":0,"passthru":false,"label":"刪除資料庫","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"str","topic":"topic","topicType":"msg","x":130,"y":580,"wires":[["0a43d4e75df79b55"]]},{"id":"f466b482638634d4","type":"ui_button","z":"332ada08f7c3884c","name":"","group":"99c5c3145bc0ee30","order":6,"width":0,"height":0,"passthru":false,"label":"檢視資料","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"str","topic":"topic","topicType":"msg","x":120,"y":720,"wires":[["509b81485d309434"]]},{"id":"361c500b6ef7ac2b","type":"link out","z":"332ada08f7c3884c","name":"link out 54","mode":"link","links":["97ecb617f9fa6786"],"x":845,"y":380,"wires":[]},{"id":"97ecb617f9fa6786","type":"link in","z":"332ada08f7c3884c","name":"link in 48","links":["361c500b6ef7ac2b"],"x":295,"y":740,"wires":[["509b81485d309434"]]},{"id":"379b9d0827becc9d","type":"function","z":"332ada08f7c3884c","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":440,"wires":[["efa890e372a46018"]]},{"id":"022940eec34e041c","type":"postgres","z":"332ada08f7c3884c","postgresdb":"b9aae012f9a45a3d","name":"PostgreSQL2","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":560,"y":80,"wires":[["a6fd40995423c258"]]},{"id":"dd33360f7276583f","type":"postgres","z":"332ada08f7c3884c","postgresdb":"b9aae012f9a45a3d","name":"PostgreSQL2","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":720,"y":300,"wires":[["5d3303b659ae7f6a","361c500b6ef7ac2b"]]},{"id":"54a55197ee853aff","type":"postgres","z":"332ada08f7c3884c","postgresdb":"b9aae012f9a45a3d","name":"PostgreSQL2","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":480,"y":360,"wires":[["4c5713185701dd7a"]]},{"id":"401d22293b1f6ebe","type":"postgres","z":"332ada08f7c3884c","postgresdb":"b9aae012f9a45a3d","name":"PostgreSQL2","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":720,"y":520,"wires":[["b178cc2ae4246ed8"]]},{"id":"bd22e5d304c51305","type":"postgres","z":"332ada08f7c3884c","postgresdb":"b9aae012f9a45a3d","name":"PostgreSQL2","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":520,"y":620,"wires":[["f8d77aa8821a36f2"]]},{"id":"4471e445acf1cb53","type":"postgres","z":"332ada08f7c3884c","postgresdb":"b9aae012f9a45a3d","name":"PostgreSQL2","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":480,"y":680,"wires":[["82d575b127105d8d"]]},{"id":"eac63b546871af8c","type":"ui_group","name":"PostgreSQL2","tab":"564bbb77340e1ba1","order":3,"disp":true,"width":"6","collapse":false,"className":""},{"id":"99c5c3145bc0ee30","type":"ui_group","name":"PostSQL2選單","tab":"564bbb77340e1ba1","order":4,"disp":true,"width":"6","collapse":false,"className":""},{"id":"b9aae012f9a45a3d","type":"postgresdb","cfgname":"PostgreSQL2","hostname":"localhost","port":"5432","db":"PostgreSQL2","ssl":false,"credentials":{}},{"id":"564bbb77340e1ba1","type":"ui_tab","name":"PostgreSQL","icon":"dashboard","order":106,"disabled":false,"hidden":false}]