Trying to execute the following:
DO $$
BEGIN
EXECUTE format('INSERT INTO public."EWcfgvars" (idinstrum, varname, enable, subaddr, id_device, id_synchronization_request, pupdate) VALUES (4, %s, 1, 0, 122, 1, 1464022764);', 'test');
END
$$;
I get this error:
[42703] ERROR: column "test" does not exist Where: PL/pgSQL function inline_code_block line 3 at EXECUTE statement
What's wrong with my DDL?
The placeholder %s will put the value from the parameter "as is" into the string. But you want the parameter to be treated as a literal (constant).
You need to use the placeholder %L for that:
format('.... (4, %L, 1, 0, 122, 1, 1464022764)', 'test')For an execute you also don't need to terminate the statement with a ;
EXECUTE. It's required between multiple statements, though. Just like in the body of an SQL functions: the semicolon after the last command is noise. Also, it's preferable for values to be passed in with a USING clause. Concatenating them as string literals is more expensive and error prone.@Olaf and @a_horse explained your problem. The solution is:
Either
EXECUTE format($$
INSERT INTO public."EWcfgvars" (idinstrum, varname, enable, subaddr, id_device, id_synchronization_request, pupdate) VALUES
(4, %L, 1, 0, 122, 1, 1464022764);
$$', 'test'
);
Or execute without format
EXECUTE $$
INSERT INTO public."EWcfgvars" (idinstrum, varname, enable, subaddr, id_device, id_synchronization_request, pupdate)
VALUES (4, $1, 1, 0, 122, 1, 1464022764);
$$ using 'test';
USING for values is the way to go.What's wrong with my DDL?
The function format outputs a string according to some printf style format string. Your string
format('INSERT INTO public."EWcfgvars" (...) VALUES (4, %s, 1, 0, 122, 1, 1464022764);', 'test')
becomes
INSERT INTO public."EWcfgvars" (...) VALUES (4, test, 1, 0, 122, 1, 1464022764);
which is an insert statement trying to insert the value of column test for the second value.
To insert test as a string, you may try a Dollar-quoted String Constants, e.g.
format('INSERT INTO public."EWcfgvars" (...) VALUES (4, %s, 1, 0, 122, 1, 1464022764);', $$'test'$$)
