Skip to content

Instantly share code, notes, and snippets.

@Nillth
Created November 3, 2021 04:34
Show Gist options
  • Save Nillth/326223530b7351a06f8c038100d99c17 to your computer and use it in GitHub Desktop.
Save Nillth/326223530b7351a06f8c038100d99c17 to your computer and use it in GitHub Desktop.
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