Function Node send short Strings to PostgreSql?

General
1

Hi,

I using a function Node to add my Strings to Postgresql:

var totalkwh = msg.payload["opendtu/yieldtotal"];
var todaykwh = msg.payload["opendtu/yieldday"];
var voltage = msg.payload["opendtu/voltage"];
var ampere = msg.payload["opendtu/current"];

msg.payload = 
    {
        
       totalkwh: totalkwh,     
        todaykwh: todaywh,
        voltage: voltage,
        ampere: ampere     
        
    }
    
return msg;

Now i insert it into Postgresql like this:

INSERT INTO emeter (totalkwh) VALUES ({{msg.payload.totalkwh}})

Is there a way to shorten msg.payload.totalkwh to e.g. msg.totalkwh or only a variable $totalkwh

I have much payloads about 20 so the INSERT QUERY is very very long.

2

Yes, don't build dynamic SQL :wink:

Apart from the risk of SQL injection that dynamic SQL can expose, handling quotes and some other characters in strings can cause problems!

Instead, use the parameterised queries.

See an example here: Using PostgreSQL with Node-RED • FlowFuse

And the info in the README: GitHub - alexandrainst/node-red-contrib-postgresql: Node-RED node for PostgreSQL, supporting parameters, split, back-pressure

1 Like
3

I have a multi table design.

So i have one value field for 5 values that came from Mqtt

How can i set msg.params to handle this ?

e.g.

var total = msg.payload.MT175.E_in;
var watt = msg.payload.MT175.P;
var device = "emeter"
var field = "totalemeter"
var time = new Date();

msg.params = [time, device, field, total];
    
return msg;

The Total and the Watt Values are in the same Database column.

Can i send msg.params2 ? and a second INSERT INTO ?

4

I have it:

msg.params = [time, device, sensor, totalemeter, wattemeter, field, field2];

Use all 7 Variables with msg.parms to the Postgresql Insert Node then Edit the PostgreSql Insert Node as shown here:


INSERT INTO emeter (time, device, sensor, value, field) VALUES ($1, $2, $3, $4, $6),($1, $2, $3, $5, $7);

$4 and $6
$5 and $7

are inserted into value and field, that works fine.

5

If you send an object instead of an array you can use the property names, can make it a bit easier perhaps