q5xJ45eq

1abc::http://localhost:5000/pic/img/1.jpg|def:::blahblahblah_http://localhost:5000/hij/klm/hxyhgbcbxjgxz.jpeg:jgfgv/hgh/
2	
3UPDATE table01 
4  SET field01 = replace(field01, 'http://localhost:5000', 'https://example.com')
5	
6DO $$
7declare
8  mysql varchar;
9  Trec record;
10  Crec record;
11  vcomma varchar := '';
12begin
13  for Trec in SELECT table_schema, table_name FROM information_schema.tables t
14             where table_schema = 'public'
15                -- and table_name = 't1'
16               and table_type != 'VIEW'
17               and exists ( select null from information_schema.columns c
18                             where c.table_schema = t.table_schema
19                             and c.table_name = t.table_name
20                             and c.character_maximum_length >= 30)
21  loop
22    raise notice 'Current table is %', quote_ident(Trec.table_name);
23    mysql := format('update %s set ',Trec.table_name);
24    vcomma := '';
25    for Crec in SELECT column_name FROM information_schema.columns c
26             where table_schema = Trec.table_schema
27               and c.table_name = Trec.table_name
28               and c.character_maximum_length >= 30
29    loop
30      raise notice 'Current column is %', quote_ident(Crec.column_name);
31      mysql := mysql || vcomma ||  Crec.column_name
32               || ' = replace(' || Crec.column_name
33               || ',' || '''http://the.olde.domain/the_olde_url'','
34               || '''http://the.newer.domain/the_newer_url'')';
35      vcomma := ', ';
36    end loop;
37    raise notice 'SQL is %', mysql;
38    execute mysql;
39  end loop;
40end $$;
41	
42UPDATE table01 
43SET    field01 = replace(field01, 'http://localhost:5000', 'https://example.com')
44WHERE  field01 ~ 'http://localhost:5000';
45	
46replace(field01, 'http://localhost:5000', 'https://example.com')
47	
48regexp_replace(field01, 'http://localhost:5000M', 'https://example.com', 'g')
49	
50UPDATE table01 
51SET    field01 =  regexp_replace(field01, 'http://localhost:5000M'
52                                        , 'https://example.com', 'g')
53WHERE  field01 ~ 'http://localhost:5000M';
54	
55CREATE OR REPLACE FUNCTION f_replace_everywhere(_pattern text, _new_string text, _tbl regclass, OUT updated_rows int) AS
56$func$
57DECLARE
58   -- basic string types, possibly extend with citext, domains or custom types:
59   _typ  CONSTANT regtype[] := '{text, bpchar, varchar}';
60   _sql  text;
61BEGIN
62   SELECT INTO _sql     -- build command
63          format('UPDATE %s SET %s WHERE %s'
64               , _tbl
65               , string_agg(format($$%1$s = regexp_replace(%1$s, $1, $2, 'g')$$, col), ', ')
66               , string_agg(col || ' ~ $1', ' OR '))
67   FROM  (
68      SELECT quote_ident(attname) AS col
69      FROM   pg_attribute
70      WHERE  attrelid = _tbl            -- valid, visible, legal table name 
71      AND    attnum >= 1                -- exclude tableoid & friends
72      AND    NOT attisdropped           -- exclude dropped columns
73      AND    NOT attnotnull             -- exclude columns defined NOT NULL!
74      AND    atttypid = ANY(_typ)       -- only character types
75      ORDER  BY attnum
76      ) sub;
77
78   -- Test
79   -- RAISE NOTICE '%', _sql;
80
81   -- Execute
82   IF _sql IS NULL THEN
83      updated_rows := 0;                         -- nothing to update
84   ELSE
85      EXECUTE _sql
86      USING _pattern, _new_string;
87
88      GET DIAGNOSTICS updated_rows = ROW_COUNT;  -- Report number of affected rows
89   END IF;
90END
91$func$  LANGUAGE plpgsql;
92	
93SELECT f_replace_everywhere( 'http://localhost:5000M'
94                           , 'https://example.com'
95                           , 'my_table');