# Pass the name of a database table into a function?

**URL:** <https://discourse.nodered.org/t/pass-the-name-of-a-database-table-into-a-function/87560>\
**Category:** General\
**Created:** [28 April 2024 08:30 UTC](https://discourse.nodered.org/t/pass-the-name-of-a-database-table-into-a-function/87560 "2024-04-28T08:30:24Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![questuk](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/questuk/32/4059_2.png) [@questuk](https://discourse.nodered.org/u/questuk)\
**Post date:** [28 April 2024 08:30 UTC](https://discourse.nodered.org/t/pass-the-name-of-a-database-table-into-a-function/87560/1 "2024-04-28T08:30:24Z")

</div>

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

```auto
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;

```

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [28 April 2024 08:51 UTC](https://discourse.nodered.org/t/pass-the-name-of-a-database-table-into-a-function/87560/2 "2024-04-28T08:51:10Z")

</div>

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

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

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

```

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [28 April 2024 08:53 UTC](https://discourse.nodered.org/t/pass-the-name-of-a-database-table-into-a-function/87560/3 "2024-04-28T08:53:48Z")

</div>

> [@questuk](#):
>
> Would it be ...  
> const topic = `INSERT INTO table ${Data_sequence}`  
> const topic = `INSERT INTO {$table} ',' {Data_sequence}`

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

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [28 April 2024 09:02 UTC](https://discourse.nodered.org/t/pass-the-name-of-a-database-table-into-a-function/87560/4 "2024-04-28T09:02:38Z")

</div>

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

```auto
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;

```

---

<div class="post-metadata">

**Author:** ![questuk](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/questuk/32/4059_2.png) [@questuk](https://discourse.nodered.org/u/questuk)\
**Post date:** [28 April 2024 09:31 UTC](https://discourse.nodered.org/t/pass-the-name-of-a-database-table-into-a-function/87560/5 "2024-04-28T09:31:58Z")

</div>

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 🧙‍♂️ on here before changing anything 😄:

Thanks to you all 👍

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [28 April 2024 10:12 UTC](https://discourse.nodered.org/t/pass-the-name-of-a-database-table-into-a-function/87560/6 "2024-04-28T10:12:17Z")

</div>

> [@questuk](#):
>
> So today I thought I would ask the wise ones 🧙‍♂️ on here before changing anything

In which case, I [strongly recommend](https://www.google.com/search?hl=en-GB&q=why%20sql%20injection%20is%20dangerous) 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](https://flows.nodered.org/node/node-red-contrib-postgresql)

---

<div class="post-metadata">

**Author:** ![questuk](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/questuk/32/4059_2.png) [@questuk](https://discourse.nodered.org/u/questuk)\
**Post date:** [28 April 2024 11:56 UTC](https://discourse.nodered.org/t/pass-the-name-of-a-database-table-into-a-function/87560/7 "2024-04-28T11:56:59Z")

</div>

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 👍

my code ...

```auto
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;

```

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [28 April 2024 14:54 UTC](https://discourse.nodered.org/t/pass-the-name-of-a-database-table-into-a-function/87560/8 "2024-04-28T14:54:31Z")

</div>

> [@questuk](#):
>
> I am using **node-red-contrib-re-postgres**

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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/3/7/3737112e6598bbf08c534df155eb1f28b589f3de.png)

[node-red-contrib-postgresql](https://flows.nodered.org/node/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.

> [@questuk](#):
>
> I wonder if you could give me an example how it would be used with my code ?

I dont use `node-red-contrib-re-postgres` and its [README](https://flows.nodered.org/node/node-red-contrib-re-postgres) is not very clear

* * *

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

### FUNCTION

```auto
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

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

```

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

```auto
// 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)

---

<div class="post-metadata">

**Author:** ![questuk](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/questuk/32/4059_2.png) [@questuk](https://discourse.nodered.org/u/questuk)\
**Post date:** [28 April 2024 16:40 UTC](https://discourse.nodered.org/t/pass-the-name-of-a-database-table-into-a-function/87560/9 "2024-04-28T16:40:20Z")

</div>

> [@questuk](#):
>
> node-red-contrib-re-postgres

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 🙂

Thanks for the info guys, much appreciated 👍

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [12 May 2024 16:40 UTC](https://discourse.nodered.org/t/pass-the-name-of-a-database-table-into-a-function/87560/10 "2024-05-12T16:40:22Z")

</div>

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