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