Dear community,
I am sharing my node with you. I would like to pass three parameters to a stored procedure. Unfortunately, this fails with a syntax error. Can you please help me out? It concerns the part of the node shown in the screenshot.
Procedure
CREATE OR REPLACE FUNCTION "Berger".update_(at_ numeric, waermebedarf_ numeric, flag_ character varying)
RETURNS character varying
LANGUAGE sql
AS $function$
update "Berger".messwerte
set waermebedarf = waermebedarf_, flag = flag_
where (at = at_ and flag = 'd')
or (at = at_ and flag = 'm' and waermebedarf_ > waermebedarf);
select 'ok';
;
$function$
;
Flow
[
{
"id": "c6281932c6357d5f",
"type": "tab",
"label": "Spielwiese",
"disabled": false,
"info": "",
"env": []
},
{
"id": "eccfa5c56b79ee3d",
"type": "inject",
"z": "c6281932c6357d5f",
"name": "",
"props": [
{
"p": "payload"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"payload": "-23.3",
"payloadType": "num",
"x": 130,
"y": 40,
"wires": [
[
"07ed769f880c4635"
]
]
},
{
"id": "07ed769f880c4635",
"type": "function",
"z": "c6281932c6357d5f",
"name": "Abfrage Wert >20 oder<-30 und runden",
"func": "var wert_neu = {wert: Math.round(msg.payload)}\nif (wert_neu.wert > 20 || wert_neu.wert < -30)\n{\n msg.payload = 999 \n}\nelse\n{\n msg.payload = wert_neu.wert\n}\n\nmsg.topic = \"aussentemperatur\"\nreturn msg;",
"outputs": 1,
"timeout": 0,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 400,
"y": 40,
"wires": [
[
"2faab1428884202a",
"2d4aac0138418a6e"
]
]
},
{
"id": "5697388293e21580",
"type": "inject",
"z": "c6281932c6357d5f",
"name": "",
"props": [
{
"p": "payload"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"payload": "500.8",
"payloadType": "num",
"x": 110,
"y": 140,
"wires": [
[
"da2c364dea10a442",
"db7ce56e354a7564"
]
]
},
{
"id": "da2c364dea10a442",
"type": "function",
"z": "c6281932c6357d5f",
"name": "Waermemenge",
"func": "msg.topic = \"waermemenge\"\nreturn msg;",
"outputs": 1,
"timeout": 0,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 340,
"y": 140,
"wires": [
[
"2faab1428884202a"
]
]
},
{
"id": "94141b471918e7d9",
"type": "debug",
"z": "c6281932c6357d5f",
"name": "debug 15",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "payload.aussentemperatur",
"targetType": "msg",
"statusVal": "",
"statusType": "auto",
"x": 820,
"y": 40,
"wires": []
},
{
"id": "2faab1428884202a",
"type": "join",
"z": "c6281932c6357d5f",
"name": "",
"mode": "custom",
"build": "object",
"property": "payload",
"propertyType": "msg",
"key": "topic",
"joiner": "\\n",
"joinerType": "str",
"accumulate": true,
"timeout": "",
"count": "2",
"reduceRight": false,
"reduceExp": "",
"reduceInit": "",
"reduceInitType": "",
"reduceFixup": "",
"x": 630,
"y": 100,
"wires": [
[]
]
},
{
"id": "41579c00b64ffe6c",
"type": "debug",
"z": "c6281932c6357d5f",
"name": "debug 16",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "payload.waermemenge",
"targetType": "msg",
"statusVal": "",
"statusType": "auto",
"x": 820,
"y": 140,
"wires": []
},
{
"id": "b7065d8d30924906",
"type": "comment",
"z": "c6281932c6357d5f",
"name": "prüfen",
"info": "/*select \"Berger\".update_({{msg.payload.at_}}, {{msg.payload.waermebedarf}}, {{msg.payload.status}}) */",
"x": 730,
"y": 520,
"wires": []
},
{
"id": "a11d6889584aa143",
"type": "inject",
"z": "c6281932c6357d5f",
"name": "",
"props": [
{
"p": "topic",
"vt": "str"
},
{
"p": "payload"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"payload": "",
"payloadType": "date",
"x": 140,
"y": 520,
"wires": [
[
"327342951cf3cbb4"
]
]
},
{
"id": "327342951cf3cbb4",
"type": "function",
"z": "c6281932c6357d5f",
"name": "function 6",
"func": "var at_ = -2\nvar waermebedarf = 2000\nvar status = 'm'\n/*\nmsg.payload =\n{\n at_: at_,\n waermebedarf: waermebedarf,\n status: status\n}\n*/\nmsg.params = [at_, waermebedarf, status]\nreturn msg;",
"outputs": 1,
"timeout": 0,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 320,
"y": 520,
"wires": [
[
"0c18d08db435201a"
]
]
},
{
"id": "2d4aac0138418a6e",
"type": "function",
"z": "c6281932c6357d5f",
"name": "function 7",
"func": "flow.set (\"sensor_at\", msg.payload)\nreturn msg;",
"outputs": 1,
"timeout": 0,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 320,
"y": 220,
"wires": [
[]
]
},
{
"id": "db7ce56e354a7564",
"type": "function",
"z": "c6281932c6357d5f",
"name": "function 8",
"func": "flow.set (\"sensor_waerme\", msg.payload)\nreturn msg;",
"outputs": 1,
"timeout": 0,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 320,
"y": 280,
"wires": [
[]
]
},
{
"id": "c58205ffbcb7f5f5",
"type": "function",
"z": "c6281932c6357d5f",
"name": "function 9",
"func": "let sensor_1 = flow.get(\"sensor_at\")\nlet sensor_2 = flow.get(\"sensor_waerme\")\nlet msg1 = {payload: sensor_1}\nlet msg2 = {payload: sensor_2}\nlet msg3 = {payload: \"\"}\nif (sensor_1 !== undefined && sensor_1 !== 999 && sensor_2 !== undefined)\n{\n msg1 = { payload: sensor_1 }\n msg2 = { payload: sensor_2 }\n msg3 = { payload: \"m\" } \n}\n\nreturn [msg1,msg2, msg3];",
"outputs": 3,
"timeout": 0,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 320,
"y": 340,
"wires": [
[
"fe03f6409bbc0a56"
],
[
"1a406871004f06c3"
],
[
"387d595141b1b3a4"
]
]
},
{
"id": "fa06128dc3301892",
"type": "inject",
"z": "c6281932c6357d5f",
"name": "",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"payload": "",
"payloadType": "date",
"x": 140,
"y": 340,
"wires": [
[
"c58205ffbcb7f5f5"
]
]
},
{
"id": "fe03f6409bbc0a56",
"type": "debug",
"z": "c6281932c6357d5f",
"name": "debug 24",
"active": true,
"tosidebar": true,
"console": true,
"tostatus": false,
"complete": "payload",
"targetType": "msg",
"statusVal": "",
"statusType": "auto",
"x": 550,
"y": 320,
"wires": []
},
{
"id": "1a406871004f06c3",
"type": "debug",
"z": "c6281932c6357d5f",
"name": "debug 25",
"active": true,
"tosidebar": true,
"console": true,
"tostatus": false,
"complete": "payload",
"targetType": "msg",
"statusVal": "",
"statusType": "auto",
"x": 550,
"y": 360,
"wires": []
},
{
"id": "387d595141b1b3a4",
"type": "debug",
"z": "c6281932c6357d5f",
"name": "debug 26",
"active": true,
"tosidebar": true,
"console": true,
"tostatus": false,
"complete": "payload",
"targetType": "msg",
"statusVal": "",
"statusType": "auto",
"x": 550,
"y": 420,
"wires": []
},
{
"id": "0c18d08db435201a",
"type": "pg",
"z": "c6281932c6357d5f",
"name": "",
"query": "select \"Berger\".update_($1, $2, $3)",
"pgConfig": "e38b9fdc4da34774",
"outputFormat": "mul",
"x": 530,
"y": 520,
"wires": [
[],
[]
]
},
{
"id": "e38b9fdc4da34774",
"type": "pgConfig",
"name": "postgres",
"host": "127.0.0.1",
"port": "5432",
"dbname": "template1",
"max": "10",
"idleTimeout": "1000",
"connectionTimeout": "10000",
"user": "postgres",
"password": "postgres"
}
]
ADMIN EDIT: Please post code into code blocks to avoid the need to download it!
