PostgreSQL Error

Question

I tried to write function, which would add a new record to the table through input parameters.

Here's code

CREATE OR REPLACE FUNCTION addRec(
 nameuser VARCHAR,
 resultName VARCHAR,
 contents bytea)
 RETURNS void AS
$BODY$
DECLARE 
comm VARCHAR;
BEGIN
comm:='INSERT INTO Results_'||nameuser||'(resultName, user, contents) VALUES ('||resultName||', '||nameuser||', '||contents||');';
EXECUTE comm;
END;
$BODY$
 LANGUAGE plpgsql VOLATILE

This function call

SELECT addRecDocument ('Fedya', 'Picture", '323423423432')

But i have some error

ERROR: column "picture" does not exist
LINE 1: SELECT addRecDocument ('Fedya', Picture, 323423423432)
 ^
ERROR: column "picture" does not exist

Please, tell me, how fixed this

• Tim Biegeleisen: 'Picture" <-- Do you not see that you are mixing single and double quotes? Actually, I'm surprised you even got this error message.
• Travis Bincle: Ok, i tryied this SELECT addRecDocument ('Fedya', 'Picture', '323423423432')but the error has remained.
• Tim Biegeleisen: The function you defined is addRec, but you are calling addRecDocument.
• Travis Bincle: Ok, i rename on this SELECT addRec ('Fedya', 'Picture', '323423423432'), but the error has remained. It's strange. He tried find column with this name.

Answer 1

First read about Quoting Values In Dynamic Queries.

Use quote_literal or quote_nullable. From documentation:

Return the given string suitably quoted to be used as a string literal in an SQL statement string. Embedded single-quotes and backslashes are properly doubled. Note that quote_literal returns null on null input; if the argument might be null, quote_nullable is often more suitable.

comm:='INSERT INTO Results_' || nameuser || '(resultName, user, contents) VALUES (' || quote_literal(resultName) || ', ' || quote_literal(nameuser) || ', ' || quote_literal(contents) || ');';

• Adam: I'm glad I could help. If you have some time, please, read What should I do when someone answers my question? ;)