Pass the name of a database table into a function?

General
1

Hi

I am uploading data from Node-RED to my PostgreSQL database via a function.
Currently i have to change the INSERT INTO table in this function, what I would like to do is pass the table into this function via msg.table . I wish to change the name of energy to sensors

Would it be ...
const topic = INSERT INTO table ${Data_sequence}
const topic = INSERT INTO {$table} ',' {Data_sequence}

or something else ?

The code I use at the moment, that I want to change

let table    = msg.table;
let device   = msg.device;                                     
   
// Some code removed here as it is not needed in this question

const Data_sequence = `( device,device_data) values('${device}','${data}')` 
const topic = `INSERT INTO energy ${Data_sequence}`                         // The insert string to send to database.

msg.payload = topic;
msg.topic = "";

return msg;
2

How about something like this? (Not tested, I don't use Postgres)

const table = msg.table || "test"
const values = " (device, device_data) values ("  + msg.device + ", " + msg.data + ")"

msg.topic = "INSERT INTO " + msg.table + values
3

You need to surround that with triple backticks so that it appears here correctly.

4

You would just add ${table} where you have energy
e.g.

let table    = msg.table;
let device   = msg.device;                                     
   
// Some code removed here as it is not needed in this question

const Data_sequence = `( device,device_data) values('${device}','${data}')` 
const topic = `INSERT INTO ${table} ${Data_sequence}`                         // The insert string to send to database.

msg.payload = topic;
msg.topic = "";

return msg;
5

Hi

I used E1cid solution and it worked.

The other day I managed to to totally crash Node-RED by doing something wrong to do with the database, not sure what. So today I thought I would ask the wise ones :man_mage: on here before changing anything :smile::

Thanks to you all :+1:

6

In which case, I strongly recommend you stop doing dynamic SQL and start using prepared statements.

Since you haven't said which postgres nodes you are using I cannot be more specific than to give you an example from one specific node - see the section titled Parameterized query here: node-red-contrib-postgresql (node) - Node-RED

7

Hi Steve,

I wonder if you could give me an example how it would be used with my code ?
Then I will look online to learn more.

I am using node-red-contrib-re-postgres

But I also save the same data to a SQLite database, as I am comparing them to see which one I want to use in the future. So i don't know if it would work with this as well ?

This is my code, I have adapted it from what I have found on the internet, it does work but does look a bit clunky

Thanks :+1:

my code ...

let table    = msg.table;                                   // The database table.
let payload  = msg.payload;                                 // The data.
let device   = msg.device;                                  // The last part of the IP address.
let location = msg.location;                                // The Location.

let new_date = new Date();                                  // Does NOT change with time zones across the world.
let iso = new Date().toISOString();                         // RFC 3339 format ... for Grafana etc.
let timestamp = Math.round(new_date.getTime() / 1000);      // getTime = milliseconds ... this removes last 3 digits to change to seconds ... for Grafana etc.


let data =
{
    iso: iso,
    location: location,
    timestamp: timestamp,
    payload: payload
};


data = JSON.stringify(data);                                                 // Converts a JavaScript value to a JSON string

const Data_sequence = `( device,device_data) values('${device}','${data}')`  // This is the actual data.
const topic = `INSERT INTO ${table} ${Data_sequence}`                        // The insert string to send to database.

msg.payload = topic;
msg.topic = "";

return msg;
8

Why? It has a poor rating, has no link to the original source repository and is older than the other more popular postges nodes

node-red-contrib-re-postgres

image

node-red-contrib-postgresql is more up-to-date, is far more popular (has 2000+ downloads per week) and has detailed info on how to do prepared statements in its README.

I dont use node-red-contrib-re-postgres and its README is not very clear

If you were using node-red-contrib-postgresql then it would be something like this:

FUNCTION

const new_date = new Date()                                   // Does NOT change with time zones across the world.
const iso = new Date().toISOString()                          // RFC 3339 format ... for Grafana etc.
const timestamp = Math.round(new_date.getTime() / 1000)       // getTime = milliseconds ... this removes last 3 digits to change to seconds ... for Grafana etc.

const data = {
    iso: iso,
    location: msg.location,
    timestamp: timestamp,
    payload: msg.payload
}

msg.queryParameters = {}
msg.queryParameters.device = msg.device // gets passed to $device in the VALUE params
msg.queryParameters.data = JSON.stringify(data) // gets passed to $data in the VALUE params

msg.topic = "";

return msg;

In the PG node text box

INSERT INTO {{{table}}} (device, device_data)
VALUES ($device, $data)

Alternatively, I think you can pass the SQL in msg.topic e.g

// all code from above plus this:
msg.topic = `
INSERT INTO ${table} (device, device_data)
VALUES ($device, $data)
`
return msg

NOTE: The above is 100% untested (may not be working but should take you 99% of the way)

9

Hi, as I was a totally new to using SLQ and 1st tried InfluxDB and could not understand or get it to work with grafana.

So then I chose node-red-contrib-postgresql, but again could not get it to work ( my fault for not trying for long enough, but most probably a day ) then i tried node-red-contrib-re-postgres and got it to work.

At that time I didn't know how knowledgeable people on here are with databases.
So i stuck with that one, I may have a look in the near future at getting the one you suggested to work :slightly_smiling_face:

Thanks for the info guys, much appreciated :+1:

10

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.