Question 281389 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Make postgres dynamic insert inside function more readable

I have a user defined Postgres function that does dynamic insert. Code is unread-able and it’s quite hard to modify it due to string escapes. Is there a way to make it look better?

Here 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'', ''

Tags:

2

Answers


  1. 0

    What I do is add comments – specifically what the resulting query looks like. You cannot alter the way the code works, and shouldn’t have to very often, when someone does knowing the end result will make it a lot easier to understand how the code builds it.

    Login or Signup to reply.
  2. 0

    Use format

    format('INSERT INTO %I (col1,col2,col3) VALUES (%L,%L,%L)', _table, _col1, _col2, _col3);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search