· 6 years ago · May 27, 2019, 10:54 PM
1-- First, create the two test tables, and create Dmitry's function (bottom of this gist)
2
3CREATE TABLE prop_values (
4 id integer,
5 status integer,
6 property_name varchar(40),
7 property_value varchar(40)
8);
9
10INSERT INTO prop_values VALUES (101,1,'first_name','Alex');
11INSERT INTO prop_values VALUES (101,1,'last_name','Addams');
12INSERT INTO prop_values VALUES (102,1,'first_name','Beth ');
13INSERT INTO prop_values VALUES (102,1,'last_name','Briggs');
14INSERT INTO prop_values VALUES (103,1,'first_name','Chris ');
15INSERT INTO prop_values VALUES (103,1,'last_name','Castro');
16INSERT INTO prop_values VALUES (102,0,'last_name','Smith');
17
18CREATE TABLE prop_values_json (
19 id integer,
20 status integer,
21 property_name varchar(40),
22 property_value jsonb
23);
24
25INSERT INTO prop_values_json VALUES (101,1,'name','{"first": "Alex", "last": "Addams"}');
26INSERT INTO prop_values_json VALUES (101,1,'stats','{"height": 180, "weight": 70}');
27INSERT INTO prop_values_json VALUES (102,1,'name','{"first": "Beth", "last": "Briggs"}');
28INSERT INTO prop_values_json VALUES (102,1,'stats','{"height": 130, "weight": 90}');
29INSERT INTO prop_values_json VALUES (103,1,'name','{"first": "Chris", "last": "Castro"}');
30INSERT INTO prop_values_json VALUES (103,1,'stats','{"height": 170, "weight": 80}');
31INSERT INTO prop_values_json VALUES (102,0,'name','{"first": "Beth", "last": "Smith"}');
32
33
34-- Running Dmitry's function works for tables where value type is string (such as below), and with numeric, date
35
36SELECT colpivot('prop_values_pivot',
37'SELECT id, status, property_name, property_value FROM prop_values',
38array['id','status'], array['property_name'],'#.property_value',null)
39
40select * from prop_values_pivot;
41 id | status | 'first_name' | 'last_name'
42-----+--------+--------------+-------------
43 101 | 1 | Alex | Addams
44 102 | 0 | Beth | Smith
45 102 | 1 | Beth | Briggs
46 103 | 1 | Chris | Castro
47(4 rows)
48
49
50-- Running Dmitry's function does not work for jsonb
51
52SELECT colpivot('prop_values_pivot_json',
53'SELECT id, status, property_name, property_value FROM prop_values_json',
54array['id','status'], array['property_name'],'#.property_value',null)
55
56-- ERROR: function max(jsonb) does not exist
57-- LINE 1: ... as select t.id , t.status , COALESCE(t."'name'", MAX("'name...
58 ^
59-- HINT: No function matches the given name and argument types. You might need to add explicit type casts.
60-- QUERY: create table prop_values_pivot_json as select t.id , t.status , COALESCE(t."'name'", MAX("'name'") OVER(PARTITION BY id)) AS "'name'" , COALESCE(t."'stats'", MAX("'stats'") OVER(PARTITION BY id)) AS "'stats'" from (select _key.id , _key.status , (_clsc_1.property_value) as "'name'" , (_clsc_2.property_value) as "'stats'" from (select id , status from __prop_values_pivot_json_in group by 1,2) _key left join __prop_values_pivot_json_in as _clsc_1 on _clsc_1.id = _key.id and _clsc_1.status = _key.status and _clsc_1.property_name = 'name' left join __prop_values_pivot_json_in as _clsc_2 on _clsc_2.id = _key.id and _clsc_2.status = _key.status and _clsc_2.property_name = 'stats' ) t
61-- CONTEXT: PL/pgSQL function colpivot(character varying,character varying,character varying[],character varying[],character varying,character varying) line 152 at EXECUTE statement
62
63
64-- Dmitry's function
65
66CREATE OR REPLACE FUNCTION public.colpivot(out_table character varying, in_query character varying, key_cols character varying[], class_cols character varying[], value_e character varying, col_order character varying)
67 RETURNS void
68 LANGUAGE plpgsql
69AS $function$
70 declare
71 in_table varchar;
72 col varchar;
73 ali varchar;
74 on_e varchar;
75 i integer;
76 rec record;
77 query varchar;
78 -- This is actually an array of arrays but postgres does not support an array of arrays type so we flatten it.
79 -- We could theoretically use the matrix feature but it's extremly cancerogenous and we would have to involve
80 -- custom aggrigates. For most intents and purposes postgres does not have a multi-dimensional array type.
81 clsc_cols text[] := array[]::text[];
82 n_clsc_cols integer;
83 n_class_cols integer;
84 tmp varchar;
85 pkCol varchar;
86 begin
87 in_table := quote_ident('__' || out_table || '_in');
88 execute ('create temp table ' || in_table || ' on commit drop as ' || in_query);
89 -- get ordered unique columns (column combinations)
90 query := 'select array[';
91 i := 0;
92 foreach col in array class_cols loop
93 if i > 0 then
94 query := query || ', ';
95 end if;
96 query := query || 'quote_literal(' || quote_ident(col) || ')';
97 i := i + 1;
98 end loop;
99 query := query || '] x from ' || in_table;
100 for j in 1..2 loop
101 if j = 1 then
102 query := query || ' group by ';
103 else
104 query := query || ' order by ';
105 if col_order is not null then
106 query := query || col_order || ' ';
107 exit;
108 end if;
109 end if;
110 i := 0;
111 foreach col in array class_cols loop
112 if i > 0 then
113 query := query || ', ';
114 end if;
115 query := query || quote_ident(col);
116 i := i + 1;
117 end loop;
118 end loop;
119 -- raise notice '%', query;
120 for rec in
121 execute query
122 loop
123 clsc_cols := array_cat(clsc_cols, rec.x);
124 end loop;
125 n_class_cols := array_length(class_cols, 1);
126 n_clsc_cols := array_length(clsc_cols, 1) / n_class_cols;
127 -- build target query
128 query := 'select ';
129 i := 0;
130 foreach col in array key_cols loop
131 if i > 0 then
132 query := query || ', ';
133 end if;
134 query := query || '_key.' || quote_ident(col) || ' ';
135 i := i + 1;
136 end loop;
137 if coalesce(array_length(clsc_cols, 1), 0) <> 0 then
138 for j in 1..n_clsc_cols loop
139 query := query || ', ';
140 col := '';
141 for k in 1..n_class_cols loop
142 if k > 1 then
143 col := col || ', ';
144 end if;
145 col := col || clsc_cols[(j - 1) * n_class_cols + k];
146 end loop;
147 ali := '_clsc_' || j::text;
148 query := query || '(' || replace(value_e, '#', ali) || ')' || ' as ' || quote_ident(col) || ' ';
149 end loop;
150 end if;
151 query := query || ' from (select ';
152 i := 0;
153 foreach col in array key_cols loop
154 if i > 0 then
155 query := query || ', ';
156 end if;
157 query := query || quote_ident(col) || ' ';
158 i := i + 1;
159 end loop;
160 query := query || ' from ' || in_table || ' group by ';
161 for j in 1..i loop
162 query := query || j::text;
163 if j < i then
164 query := query || ',';
165 end if;
166 end loop;
167 query := query || ') _key ';
168 if coalesce(array_length(clsc_cols, 1), 0) <> 0 then
169 for j in 1..n_clsc_cols loop
170 ali := '_clsc_' || j::text;
171 on_e := '';
172 i := 0;
173 foreach col in array key_cols loop
174 if i > 0 then
175 on_e := on_e || ' and ';
176 end if;
177 on_e := on_e || ali || '.' || quote_ident(col) || ' = _key.' || quote_ident(col) || ' ';
178 i := i + 1;
179 end loop;
180 for k in 1..n_class_cols loop
181 on_e := on_e || ' and ';
182 on_e := on_e || ali || '.' || quote_ident(class_cols[k]) || ' = ' || clsc_cols[(j - 1) * n_class_cols + k];
183 end loop;
184 query := query || 'left join ' || in_table || ' as ' || ali || ' on ' || on_e || ' ';
185 end loop;
186 end if;
187
188 tmp := '';
189 i := 0;
190 foreach col in array key_cols loop
191 if pkCol is null then
192 pkCol := quote_ident(col);
193 end if;
194 if i > 0 then
195 tmp := tmp || ', ';
196 end if;
197 tmp := tmp || 't.' || quote_ident(col) || ' ';
198 i := i + 1;
199 end loop;
200 if coalesce(array_length(clsc_cols, 1), 0) <> 0 then
201 for j in 1..n_clsc_cols loop
202 tmp := tmp || ', ';
203 col := '';
204 for k in 1..n_class_cols loop
205 if k > 1 then
206 col := col || ', ';
207 end if;
208 col := col || clsc_cols[(j - 1) * n_class_cols + k];
209 end loop;
210 tmp := tmp || 'COALESCE(t.' || quote_ident(col) || ', MAX(' || quote_ident(col) || ') OVER(PARTITION BY ' || pkCol || ')) AS ' || quote_ident(col) || ' ';
211 end loop;
212 end if;
213 query := 'select ' || tmp || ' from (' || query || ') t';
214
215
216 -- raise notice '%', query;
217 -- execute ('create temp table ' || quote_ident(out_table) || ' on commit drop as ' || query);
218 -- comment above and uncomment below for debugging
219 execute ('DROP TABLE IF EXISTS ' || quote_ident(out_table));
220 execute ('create table ' || quote_ident(out_table) || ' as ' || query);
221
222 -- cleanup temporary in_table before we return
223 execute ('drop table ' || in_table)
224 return;
225 end;
226$function$