Hello, I am connecting to a postgresql database through Node-red dashboard
I would like to do a dashboard that allow me to dynamically make a query on any tables in the database (populating the Query form using a SQL request) and then display dynamically all columns that exists using an html template (without caring the name of each columns in each table). I am almost capable to make it using a fix database and a fix columns format but lack the knowledge to do it with more adaptative function. Thanks if you can provide some help or guidance.
Here is the Node
[
{
"id": "51dc0a4e8246dba1",
"type": "ui_dropdown",
"z": "4447f1fa.c7a27",
"name": "",
"label": "DB List",
"tooltip": "",
"place": "Select option",
"group": "8e3625ca10f0866b",
"order": 1,
"width": 6,
"height": 1,
"passthru": true,
"multiple": false,
"options": [
{
"label": "table1",
"value": "table1",
"type": "str"
},
{
"label": "table2",
"value": "table2",
"type": "str"
},
{
"label": "table3",
"value": "table3",
"type": "str"
}
],
"payload": "",
"topic": "DB_name",
"topicType": "str",
"className": "",
"x": 580,
"y": 820,
"wires": [
[
"7e6d3ab8d5b1e854"
]
]
},
{
"id": "7e6d3ab8d5b1e854",
"type": "function",
"z": "4447f1fa.c7a27",
"name": "Query Builder",
"func": "\nvar topic=msg.topic;\nvar payload=msg.payload;\n/////////////////////////////\nif (topic==\"DB_name\")\ndata.table=msg.payload; \n////////////////////////////\n\n var query=\"\";\n/////////////////////////////\nif(topic==\"submit\")\n\n\nquery=\"SELECT creation_date as col1, name_table1 as col2, description_table1 as col3 FROM \" + data.table;\n\n \nmsg.payload=query;\nreturn;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 810,
"y": 820,
"wires": [
[
"4b13afba042bfa5e",
"5e66eef3629fc25e"
]
]
},
{
"id": "4b13afba042bfa5e",
"type": "postgresql",
"z": "4447f1fa.c7a27",
"name": "Read Data",
"query": "{{ msg.payload }}",
"postgreSQLConfig": "0a602bea5bc68f3e",
"split": false,
"rowsPerMsg": "100",
"outputs": 1,
"x": 1060,
"y": 820,
"wires": [
[
"5e66eef3629fc25e",
"faf458afa373a57d"
]
]
},
{
"id": "faf458afa373a57d",
"type": "ui_template",
"z": "4447f1fa.c7a27",
"group": "bf26ebd359905ce9",
"name": "HTML template",
"order": 1,
"width": 0,
"height": 0,
"format": "<style>\ntable\n{\n background:grey;\n}\n.main\n{\n height:500px;\n}\n</style>\n<div class=\"main\">\n<table style=\"width:100%\">\n <tr>\n <th>Column1</th> \n <th>Column2</th>\n <th>Column3</th> \n\n \n </tr>\n <tr ng-repeat=\"x in msg.payload | limitTo:100\">\n <td>{{msg.payload[$index].col1}}</td>\n <td>{{msg.payload[$index].col2}}</td>\n <td>{{msg.payload[$index].col3}}</td> \n</tr>\n</table>\n</div>",
"storeOutMessages": true,
"fwdInMessages": true,
"resendOnRefresh": false,
"templateScope": "local",
"className": "",
"x": 1290,
"y": 820,
"wires": [
[]
]
},
{
"id": "dfdaa278ab532efd",
"type": "inject",
"z": "4447f1fa.c7a27",
"name": "show t1",
"props": [
{
"p": "payload"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"payload": "SELECT creation_date as col1, name_table1 as col2, description_table1 as col3 FROM public.table1;",
"payloadType": "str",
"x": 550,
"y": 600,
"wires": [
[
"4b13afba042bfa5e"
]
]
},
{
"id": "c99bf5b882029390",
"type": "inject",
"z": "4447f1fa.c7a27",
"name": "show t2",
"props": [
{
"p": "payload"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"payload": "SELECT creation_date as col1, name_table2 as col2, description_table2 as col3 \tFROM public.table2;",
"payloadType": "str",
"x": 560,
"y": 650,
"wires": [
[
"4b13afba042bfa5e"
]
]
},
{
"id": "07b75af870ee5452",
"type": "inject",
"z": "4447f1fa.c7a27",
"name": "show t3",
"props": [
{
"p": "payload"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"payload": "SELECT creation_date as col1, name_table3 as col2, description_table3 as col3 \tFROM public.table3;",
"payloadType": "str",
"x": 560,
"y": 710,
"wires": [
[
"4b13afba042bfa5e"
]
]
},
{
"id": "be228c26f3961137",
"type": "ui_button",
"z": "4447f1fa.c7a27",
"name": "submit",
"group": "8e3625ca10f0866b",
"order": 2,
"width": 6,
"height": 1,
"passthru": false,
"label": "Submit",
"tooltip": "",
"color": "",
"bgcolor": "",
"className": "",
"icon": "",
"payload": "1",
"payloadType": "str",
"topic": "submit",
"topicType": "str",
"x": 570,
"y": 890,
"wires": [
[
"7e6d3ab8d5b1e854"
]
]
},
{
"id": "5e66eef3629fc25e",
"type": "debug",
"z": "4447f1fa.c7a27",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "payload",
"targetType": "msg",
"statusVal": "",
"statusType": "auto",
"x": 1300,
"y": 710,
"wires": []
},
{
"id": "e45f4f8c9d2336a5",
"type": "comment",
"z": "4447f1fa.c7a27",
"name": "TRIAL SIMPLE WAY",
"info": "",
"x": 170,
"y": 520,
"wires": []
},
{
"id": "80dec39cc35bcf39",
"type": "inject",
"z": "4447f1fa.c7a27",
"name": "",
"props": [
{
"p": "payload"
}
],
"repeat": "",
"crontab": "",
"once": true,
"onceDelay": 0.1,
"topic": "",
"payload": "",
"payloadType": "str",
"x": 110,
"y": 820,
"wires": [
[
"30f46453b3a9690a"
]
]
},
{
"id": "30f46453b3a9690a",
"type": "postgresql",
"z": "4447f1fa.c7a27",
"name": "LIST ALL public TABLE",
"query": "SELECT * from pg_catalog.pg_tables WHERE schemaname ='public' ORDER by tablename",
"postgreSQLConfig": "94962faa5cafebbf",
"split": false,
"rowsPerMsg": "100",
"outputs": 1,
"x": 330,
"y": 820,
"wires": [
[
"a6baf3d6043f4ff1",
"0198ccf055727557"
]
]
},
{
"id": "a6baf3d6043f4ff1",
"type": "debug",
"z": "4447f1fa.c7a27",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "payload",
"targetType": "msg",
"statusVal": "",
"statusType": "auto",
"x": 470,
"y": 990,
"wires": []
},
{
"id": "0198ccf055727557",
"type": "ui_template",
"z": "4447f1fa.c7a27",
"d": true,
"group": "bf26ebd359905ce9",
"name": "",
"order": 1,
"width": 0,
"height": 0,
"format": "<style>\ntable\n{\n background:grey;\n}\n.main\n{\n height:500px;\n}\n</style>\n<div class=\"main\">\n<table style=\"width:100%\">\n <tr>\n <th>tablename DATE</th> \n <th>tablename</th>\n <th>tableowner</th> \n\n </tr>\n <tr ng-repeat=\"x in msg.payload | limitTo:100\">\n <td>{{msg.payload[$index].schemaname}}</td>\n <td>{{msg.payload[$index].tablename}}</td>\n <td>{{msg.payload[$index].tableowner}}</td> \n</tr>\n</table>\n</div>",
"storeOutMessages": true,
"fwdInMessages": true,
"resendOnRefresh": false,
"templateScope": "local",
"className": "",
"x": 520,
"y": 760,
"wires": [
[]
]
},
{
"id": "8e3625ca10f0866b",
"type": "ui_group",
"name": "QUERY",
"tab": "d18b2f90a4b5b820",
"order": 1,
"disp": true,
"width": "6",
"collapse": false,
"className": ""
},
{
"id": "0a602bea5bc68f3e",
"type": "postgreSQLConfig",
"name": "Postgres",
"host": "127.0.0.1",
"hostFieldType": "str",
"port": "5432",
"portFieldType": "num",
"database": "postgres",
"databaseFieldType": "str",
"ssl": "false",
"sslFieldType": "bool",
"max": "10",
"maxFieldType": "num",
"min": "1",
"minFieldType": "num",
"idle": "1000",
"idleFieldType": "num",
"connectionTimeout": "10000",
"connectionTimeoutFieldType": "num",
"user": "admin",
"userFieldType": "str",
"password": "F!berty9817",
"passwordFieldType": "str"
},
{
"id": "bf26ebd359905ce9",
"type": "ui_group",
"name": "Results",
"tab": "d18b2f90a4b5b820",
"order": 2,
"disp": true,
"width": 14,
"collapse": false,
"className": ""
},
{
"id": "94962faa5cafebbf",
"type": "postgreSQLConfig",
"name": "DB_local",
"host": "127.0.0.1",
"hostFieldType": "str",
"port": "5432",
"portFieldType": "num",
"database": "postgre",
"databaseFieldType": "str",
"ssl": "false",
"sslFieldType": "bool",
"max": "10",
"maxFieldType": "num",
"min": "1",
"minFieldType": "num",
"idle": "1000",
"idleFieldType": "num",
"connectionTimeout": "10000",
"connectionTimeoutFieldType": "num",
"user": "admin",
"userFieldType": "str",
"password": "admin",
"passwordFieldType": "str"
},
{
"id": "d18b2f90a4b5b820",
"type": "ui_tab",
"name": "DB_QUERYYYY",
"icon": "dashboard",
"order": 16,
"disabled": false,
"hidden": false
}
]
And the CREATE Command for Postgres:
CREATE TABLE IF NOT EXISTS public.table1
(
"ID" bigint NOT NULL,
creation_date timestamp without time zone DEFAULT now(),
name_table1 character varying COLLATE pg_catalog."default",
description_table1 character varying COLLATE pg_catalog."default",
CONSTRAINT "Table1_pkey" PRIMARY KEY ("ID")
)
TABLESPACE pg_default;
ALTER TABLE IF EXISTS public.table1
OWNER to postgres;
CREATE TABLE IF NOT EXISTS public.table2
(
id2 bigint NOT NULL DEFAULT nextval('table2_id2_seq'::regclass),
creation_date timestamp with time zone,
name_table2 character varying COLLATE pg_catalog."default",
description_table2 character varying COLLATE pg_catalog."default",
CONSTRAINT table2_pkey PRIMARY KEY (id2)
)
TABLESPACE pg_default;
ALTER TABLE IF EXISTS public.table2
OWNER to postgres;
-- Table: public.table3
-- DROP TABLE IF EXISTS public.table3;
CREATE TABLE IF NOT EXISTS public.table3
(
id3 bigint NOT NULL,
creation_date timestamp with time zone,
name_table3 character varying COLLATE pg_catalog."default",
description_table3 character varying COLLATE pg_catalog."default",
"Another_col_different_from other_table" character varying COLLATE pg_catalog."default",
CONSTRAINT table3_pkey PRIMARY KEY (id3)
)
TABLESPACE pg_default;
ALTER TABLE IF EXISTS public.table3
OWNER to postgres;
