Make dynamic INSERT inside function more readable

Question

I have a user defined Postgres function that does dynamic INSERT. Code is unreadable and it's quite hard to modify it due to string escapes. Is there a way to make it look better?

Here is my code, some parts omitted for brevity:

BEGIN
    -- some code

    -- escape nightmare for teammates goes here

EXECUTE 'INSERT INTO ' || _table || E'(col1, col2, col3)  VALUES (\'' || _col1 ||
                E'\', \'' || _col2 || E'\''||  E', \'' || _col3::text ||E'\') ON CONFLICT DO NOTHING RETURNING code' INTO _code;

    -- more code
END

Is there a way to simplify things like E'\', \''?

Answer 1

Use format(), like Richard already instructed. That's not only more readable, it also fixes the glaring SQL injection problem of your original.

What's more, don't concatenate values at all. Pass them as values with the USING clause:

EXECUTE format(
   'INSERT INTO %I (col1,col2,col3) VALUES ($1,$2,$3)
    ON CONFLICT DO NOTHING
    RETURNING code', _table)
INTO  _code
USING _col1, _col2, _col3::text;  -- do you need to cast?

To be sure, use a schema-qualified table name - which cannot be passed as single identifier like in the above code. Schema and table name have to be quoted separately. See:

• Table name as a PostgreSQL function parameter
• INSERT with dynamic table name in trigger function
• Converting the result of a dynamic query to json
• Refactor a PL/pgSQL function to return the output of various SELECT queries

And be sure to understand and use dollar-quoting:

• What are '$$' used for in PL/pgSQL
• Insert text with single quotes in PostgreSQL