Dynamic UPDATE fails due to unwanted parenthesis around string in plpgsql
@klin already addressed the bug you had been asking about. I am going to address your general approach.

Your DO statement would run a separate UPDATE for every column - which is almost n times as expensive as updating n columns at once.
Due to PostgreSQL's MVCC model this also produces n dead tuples in the table, leading to excessive table bloat and a lot of work for VACUUM.

What's worse, it updates every row n times even if nothing changes at all.

I suggest a much more efficient approach:


Update every row once.
Only update if anything actually changes.




CREATE OR REPLACE FUNCTION f_trim_cols(IN _tbl regclass, OUT row_ct integer
                                                       , OUT col_ct integer)
  RETURNS record AS
$func$
DECLARE
   _sql text;
BEGIN
   SELECT format('
      UPDATE %s SET (%2$s) = (trim(%3$s))
      WHERE (%2$s) IS DISTINCT FROM (trim(%3$s))' -- test if anything changes
                , _tbl
                , string_agg (quote_ident(attname), ', ')
                , string_agg (quote_ident(attname), '), trim(')
                )
        , count(*)::int  -- count columns
   INTO   _sql, col_ct
   FROM   pg_attribute
   WHERE  attrelid = _tbl
   AND    atttypid IN ('text'::regtype, 'varchar'::regtype) -- basic string types
   AND    NOT attisdropped 
   AND    attnum &gt; 0;

   -- RAISE NOTICE '%', _sql;  --  debug
   EXECUTE _sql;  GET DIAGNOSTICS row_ct = ROW_COUNT;
END
$func$  LANGUAGE plpgsql;

COMMENT ON FUNCTION f_trim_cols(regclass) IS 'Trim all columns of table _tbl ($1).
Return count of applicable columns (col_ct) and rows actually changed (row_ct) .
Call:
   SELECT * FROM f_trim_cols($$city$$)   --';


Produces SQL code like:

 UPDATE city SET (city, country) = (trim(city), trim(country))
 WHERE (city, country) IS DISTINCT FROM (trim(city), trim(country));


I also target text as well as varchar columns.
Everything is sanitized and safe against SQL injection.
More explanation, links and a fiddle in this related answer on SO:


Update multiple columns that start with a specific string