Create function gives syntax error in Postgres

Question

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.

1) Error syntax error at »LANGUAGE« 2) plpgsql structue. 3) Also CREATE FUNCTION. See the problem? Hint LANGUAGE is in the wrong place. — Adrian Klaver
– Adrian Klaver, Commented Sep 25, 2024 at 14:58
Adding here, as a final nitpick: the last semicolon inside the body is optional at END; $body$ but the code's missing an obligatory one after it to terminate the statement: END; $body$; or END $body$;. In practice, most clients handle unterminated statements fine. — Zegarek
– Zegarek, Commented Sep 25, 2024 at 16:12

Laurenz Albe · Accepted Answer · 2024-09-25 15:37:08Z

3

This is all messed up:

there is a semicolon missing at the end of the RETURN statement
LANGUAGE plpgsql is after AS instead of before it
the function is defined to return an integer, but you are trying to return a query result
RETURN QUERY is to return a result set, but CREATE TABLE returns 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
)

edited Sep 25, 2024 at 15:37

answered Sep 25, 2024 at 14:58

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Adrian Klaver Over a year ago

The bigger issues are 1) ... RETURNS INTEGER AS LANGUAGE plpgsql ... which leads to syntax error at »LANGUAGE« and 2) ERROR: cannot use RETURN QUERY in a non-SETOF function

BairDev Over a year ago

Thanks folks! Right version is now (tested with calling function and doing INSERTS):

CREATE OR REPLACE FUNCTION table_per_meter(meter_secondary text) RETURNS void LANGUAGE plpgsql AS  $body$ BEGIN   EXECUTE     format(         'CREATE TABLE IF NOT EXISTS %I(id SERIAL PRIMARY KEY, v NUMERIC(21, 2) NOT NULL, d TIMESTAMP WITH TIME ZONE NOT NULL, CONSTRAINT date UNIQUE(d))',         'meter_values_' || meter_secondary); END $body$;

Collectives™ on Stack Overflow

Create function gives syntax error in Postgres

Is using `RETURN QUERY EXECUTE` wrong or overhead?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Is using RETURN QUERY EXECUTE wrong or overhead?

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Is using `RETURN QUERY EXECUTE` wrong or overhead?