How to pass msg int value, to postgresql

[retegi]

Hi! I have a int value y msg, but now i need insert this value into a postgresql.

Postgresql query incorrect because cant read "msg":
INSERT INTO scada_level (datetimeLevel,level) VALUES (current_timestamp,msg);

How can i put msg value in query?

Thanks!

[zenofmud]

There’s a great page in the docs (Working with messages : Node-RED) that will explain how to use the debug panel to find the right path to any data item.

Pay particular attention to the part about the buttons that appear under your mouse pointer when you over hover a debug message property in the sidebar.

[retegi]

Here:
I only need payload to insert in query.

How can i insert msg.payload into a query?

thanks!

[Steve-Mcl]

@retegi

How can i insert msg.payload into a query

Build the query using the msg parts you need, getting the path to the items of interest like @zenofmud showed you.

There are examples on the nodes readme page (assuming you are using node-red-contrib-postgresql - you didnt say!)

e.g...

SQL query template

This node uses the Mustache template system to generate queries based on the message:

-- INTEGER id column
SELECT * FROM table WHERE id = {{{ msg.id }}};

-- TEXT id column
SELECT * FROM table WHERE id = '{{{ msg.id }}}';

This works but is prone to SQL Injection and quote errors.

This is better...

Parameterized query (numeric)

Parameters for parameterized queries can be passed as a parameter array msg.params:

// In a function, provide parameters for the parameterized query
msg.params = [ msg.id ];

-- In this node, use a parameterized query
SELECT * FROM table WHERE id = $1;

Named parameterized query

As an alternative to numeric parameters, named parameters for parameterized queries can be passed as a parameter object msg.queryParameters:

// In a function, provide parameters for the named parameterized query
msg.queryParameters.id = msg.id;

-- In this node, use a named parameterized query
SELECT * FROM table WHERE id = $id;

[retegi]

@Steve-Mcl

msg.params = [ msg.id ];

Thnaks! Now is correct with msg.params = [ msg.payload ];