~singpolyma/jmp-schemas

ref: 240b3444466fb458b517d84c83bd6a782a21934e jmp-schemas/deploy/customers.sql -rw-r--r-- 427 bytes
240b3444Stephen Paul Weber NOTIFY on low balance 7 months ago
                                                                                
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- Deploy jmp:customers to pg

BEGIN;

CREATE FOREIGN TABLE raw_customer_data (key text, val text)
	SERVER redis_server
	OPTIONS (tablekeyprefix 'jmp_customer_');

CREATE VIEW customers AS
	SELECT
		MAX(SPLIT_PART(key, '-', 2)) AS customer_id,
		JSONB_OBJECT_AGG(SUBSTRING(SPLIT_PART(key, '-', 1) FROM 14), val) AS data
	FROM raw_customer_data
	WHERE key NOT LIKE 'jmp_customer_id-%' GROUP BY SPLIT_PART(key, '-', 2);

COMMIT;