Created
November 3, 2021 04:34
-
-
Save Nillth/326223530b7351a06f8c038100d99c17 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SET statement_timeout = 0; | |
SET lock_timeout = 0; | |
SET client_encoding = 'UTF8'; | |
SET standard_conforming_strings = on; | |
SET check_function_bodies = false; | |
SET client_min_messages = warning; | |
CREATE OR REPLACE FUNCTION public.validate_payload(IN payload JSON) RETURNS TEXT AS $$ | |
BEGIN | |
RETURN payload->>'XXXXX'; | |
EXCEPTION | |
WHEN OTHERS THEN RETURN 'XXXXX'; | |
END | |
$$ LANGUAGE plpgsql; | |
DO $$ | |
BEGIN | |
UPDATE public.log_entries | |
SET payload = REGEXP_REPLACE(payload::TEXT, '\\u0000', '<UNICODE_NULL>', 'g')::JSON | |
WHERE id IN ( | |
SELECT d.id | |
FROM ( | |
SELECT id, validate_payload(payload) AS payload | |
FROM public.log_entries | |
) AS d | |
WHERE d.payload = 'XXXXX' | |
); | |
UPDATE public.archive_entries | |
SET payload = REGEXP_REPLACE(payload::TEXT, '\\u0000', '<UNICODE_NULL>', 'g')::JSON | |
WHERE id IN ( | |
SELECT d.id | |
FROM ( | |
SELECT id, validate_payload(payload) AS payload | |
FROM public.archive_entries | |
) AS d | |
WHERE d.payload = 'XXXXX' | |
); | |
END $$ LANGUAGE plpgsql; | |
DROP FUNCTION IF EXISTS public.validate_payload(IN payload JSON); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment