Remove_non_UTF8_29022016 - invalid SQL

[sammcj]

The sql provided by @eshebanin is invalid SQL.

The error is:

database_name=# SELECT process_non_utf8_at_column(p_table_name := 'tbl_bi_objects', p_column_name := 'definition_xml');
NOTICE:  table "x_list" does not exist, skipping
CONTEXT:  SQL statement "DROP TABLE IF EXISTS x_list;"
PL/pgSQL function process_non_utf8_at_column(character varying,character varying) line 8 at EXECUTE statement
ERROR:  column "row_value" does not exist
LINE 1: ... row_value, row_message, row_chunk)  SELECT ctid, row_value,...
                                                             ^
QUERY:   INSERT INTO X_LIST (rid, row_value, row_message, row_chunk)  SELECT ctid, row_value, message,  (Row_Number() Over())/50000 + 1 As row_chunk  FROM tbl_bi_objects
CONTEXT:  PL/pgSQL function process_non_utf8_at_column(character varying,character varying) line 22 at EXECUTE statement

@rvassellini and @georgehansper have made some changes to try to fix it up as such:

--- Remove_non_UTF8_29022016.sql    2016-03-07 12:29:32.000000000 +1100
+++ Remove_non_UTF8_29022016.sql.new    2016-03-09 13:58:51.713539677 +1100
@@ -3,8 +3,9 @@
 $BODY$
 DECLARE
   v_string VARCHAR;
+  cur_part record;
 BEGIN
-  v_string ;= p_string;
+  v_string := p_string;
   FOR cur_part IN ( SELECT
                       p_string AS word,
                       SUBSTRING(p_string, numgen.num, 1) AS symbol,
@@ -23,7 +24,7 @@
                                              $$[\xF1-\xF3][\x80-\xBF]{3}|$$||          -- planes 4-15
                                              $$\xF4[\x80-\x8F][\x80-\xBF]{2}$$||      -- plane 16
                                              ')*$' )
-                    ORDER BY 3 DESC )
+                    ORDER BY 3 DESC ) AS tb )
    LOOP
      v_string := LEFT(v_string, c_part.num - 1)|| RIGHT(v_string, LENGTH(v_string) - c_part.num);
    END LOOP;
@@ -47,7 +48,7 @@
   EXECUTE 'CREATE TEMPORARY TABLE x_list ( ' ||
           ' rid TID NOT NULL, ' ||
           ' row_value NUMERIC(11,2) NOT NULL, ' ||
-          ' row_message character varying(250), ' ||
+          ' row_message text, ' ||
           ' row_chunk bigint ) ON COMMIT DELETE ROWS; ';

   EXECUTE 'CREATE INDEX chunk_idx ON x_list (row_chunk);';
@@ -56,7 +57,7 @@
                  ' SELECT ctid, Row_Number() Over(), %I,' ||
                  ' (Row_Number() Over())/50000 + 1 As row_chunk ' ||
                  ' FROM %I', p_column_name, p_table_name);
-  EXECUTE (' INSERT INTO X_LIST (rid, row_value, row_message, row_chunk) ' ||
+  EXECUTE FORMAT(' INSERT INTO X_LIST (rid, row_value, row_message, row_chunk) ' ||
                  ' SELECT ctid, row_value, message, ' ||
                  ' (Row_Number() Over())/50000 + 1 As row_chunk ' ||
                  ' FROM %I', p_table_name);

[sammcj]

@georgehansper is updating the file in the repo.

@eshebanin , @georgehansper , @rvassellini can you please all work from this git repo now rather than email?

[sammcj]

FYI @eshebanin , try testing out the SQL on http://sqlfiddle.com (make sure to select PostgreSQL at the top first!)