I am pretty new to PostgreSQL and also to Node-RED.
It seems like you can create tables with dynamic names in PostgreSQL. With something like this or this:
-- gives error: syntax error at »LANGUAGE«
CREATE OR REPLACE FUNCTION table_per_meter(meter_secondary text)
RETURNS INTEGER AS
LANGUAGE plpgsql
$body$
BEGIN
RETURN QUERY EXECUTE
format('CREATE TABLE IF NOT EXISTS meter_value_%I(id SERIAL PRIMARY KEY, v NUMERIC(21, 2) NOT NULL, d TIMESTAMP WITH TIME ZONE NOT NULL);',
meter_secondary)
END;
$body$
I cannot spot the error here.
Is using RETURN QUERY EXECUTE wrong or overhead?
These examples look much more simple:
EXECUTE format('SELECT count(*) FROM %I '
'WHERE inserted_by = $1 AND inserted <= $2', tabname)
INTO c
USING checked_user, checked_date;
But my problems seems to be related to the way of function definition.
2
This is all messed up:
-
there is a semicolon missing at the end of the
RETURNstatement -
LANGUAGE plpgsqlis afterASinstead of before it -
the function is defined to return an
integer, but you are trying to return a query result -
RETURN QUERYis to return a result set, butCREATE TABLEreturns nothing
(Thanks to Adrian Klaver for pointing out some of these errors.)
For the last two points, define the function as RETURNS void and remove the RETURN QUERY before the EXECUTE.
But there is a deeper problem with the function code: if you supply a string as function argument that is not an SQL standard identifier, you will get the double quotes in the wrong place. For example, for the argument CamelCase, the table name will become meter_value_"CamelCase", which is not correct.
Proceed like this:
format(
'CREATE TABLE IF NOT EXISTS %I(...)',
'meter_value_' || meter_secondary
)
2