Skip to content

Instantly share code, notes, and snippets.

@afarber
Last active February 27, 2016 18:55
Show Gist options
  • Save afarber/9ee603289cc7f1f80cbb to your computer and use it in GitHub Desktop.
Save afarber/9ee603289cc7f1f80cbb to your computer and use it in GitHub Desktop.
Merge records belonging to same user - without losing payment or punishment information: http://stackoverflow.com/questions/35673982/using-jsonb-array-elements-with-where-in-condition
DROP TABLE IF EXISTS users;
CREATE TABLE users (
uid SERIAL PRIMARY KEY,
paid1_until timestamptz NULL,
paid2_until timestamptz NULL,
banned_until timestamptz NULL,
banned_reason varchar(255) NULL
);
INSERT INTO users (paid1_until, paid2_until, banned_until, banned_reason)
VALUES (NULL, NULL, NULL, NULL),
(current_timestamp + interval '1 month', NULL, NULL, NULL),
(current_timestamp + interval '2 month', current_timestamp + interval '4 month', NULL, NULL),
(NULL, current_timestamp + interval '8 month', NULL, NULL);
SELECT min(uid) as out_uid,
current_timestamp + sum(paid1_until - current_timestamp) as new_paid1,
current_timestamp + sum(paid2_until - current_timestamp) as new_paid2,
max(banned_until) as new_banned
FROM users;
CREATE OR REPLACE FUNCTION merge_users(
IN in_users jsonb,
OUT out_uid integer)
RETURNS integer AS
$func$
DECLARE
new_paid1 timestamptz;
new_paid2 timestamptz;
new_banned timestamptz;
new_reason varchar(255);
BEGIN
SELECT min(uid),
current_timestamp + sum(paid1_until - current_timestamp),
current_timestamp + sum(paid2_until - current_timestamp),
max(banned_until)
INTO
out_uid, new_paid1, new_paid2, new_banned
FROM users
WHERE uid IN (SELECT JSONB_ARRAY_ELEMENTS(in_users));
RAISE NOTICE 'out_uid = %', out_uid;
RAISE NOTICE 'new_paid1 = %', new_paid1;
RAISE NOTICE 'new_paid2 = %', new_paid2;
RAISE NOTICE 'new_banned = %', new_banned;
IF out_uid IS NOT NULL THEN
SELECT banned_reason
INTO new_reason
FROM users
WHERE new_banned IS NOT NULL
AND banned_until = new_banned
LIMIT 1;
RAISE NOTICE 'new_reason = %', new_reason;
DELETE FROM users
WHERE uid IN (SELECT JSONB_ARRAY_ELEMENTS(in_users))
AND uid <> out_uid;
UPDATE users
SET paid1_until = new_paid1,
paid2_until = new_paid2,
banned_until = new_banned,
banned_reason = new_reason
WHERE uid = out_uid;
END IF;
END
$func$ LANGUAGE plpgsql;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment