2018-03-03 12:07:05 -05:00
|
|
|
\timing
|
|
|
|
|
|
2018-03-03 12:17:22 -05:00
|
|
|
/*--------------------------------------------------
|
|
|
|
|
maintain statment level triggers to update a master log of keys
|
|
|
|
|
* table based listing
|
|
|
|
|
* composite type maintenance
|
|
|
|
|
|
|
|
|
|
potential updates sources/events
|
|
|
|
|
* tps.trans insert
|
|
|
|
|
* tps.trans re-map
|
|
|
|
|
--------------------------------------------------*/
|
|
|
|
|
|
2018-03-03 12:07:05 -05:00
|
|
|
WITH ok AS (
|
|
|
|
|
SELECT
|
|
|
|
|
srce,
|
|
|
|
|
ok.k,
|
|
|
|
|
jsonb_typeof(allj->ok.k) typeof,
|
|
|
|
|
COUNT(*)
|
|
|
|
|
FROM
|
|
|
|
|
tps.trans
|
|
|
|
|
JOIN LATERAL jsonb_object_keys(allj) ok(k) ON TRUE
|
|
|
|
|
GROUP BY
|
|
|
|
|
srce,
|
|
|
|
|
ok.k,
|
|
|
|
|
jsonb_typeof(allj->ok.k)
|
|
|
|
|
ORDER BY
|
|
|
|
|
srce
|
|
|
|
|
)
|
|
|
|
|
SELECT
|
|
|
|
|
srce
|
|
|
|
|
,k
|
|
|
|
|
,typeof
|
|
|
|
|
FROM
|
|
|
|
|
ok
|
