Created
November 8, 2020 10:56
-
-
Save pyramation/56a4f48b630f3ff06b6aa2ba284c0e98 to your computer and use it in GitHub Desktop.
This file contains 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
CREATE OR REPLACE FUNCTION smart_comments(tags jsonb, description text default null) | |
RETURNS text | |
AS $$ | |
DECLARE | |
key text; | |
value jsonb; | |
tvalue text; | |
attrs text[] = ARRAY[]::text[]; | |
_key text; | |
_value text; | |
BEGIN | |
FOR key IN SELECT jsonb_object_keys(tags) | |
LOOP | |
value = tags->key; | |
tvalue = tags->>key; | |
IF (jsonb_typeof(value) = 'boolean') THEN | |
IF (tvalue = 'true') THEN | |
attrs = array_append(attrs, concat('@', key)); | |
END IF; | |
ELSIF (jsonb_typeof(value) = 'array') THEN | |
FOR _value IN SELECT * FROM jsonb_array_elements(value) | |
LOOP | |
-- json text includes double quotes, so lets remove them! | |
attrs = array_append(attrs, concat('@', key, ' ', trim(both '"' from _value))); | |
END LOOP; | |
ELSE | |
attrs = array_append(attrs, concat('@', key, ' ', tvalue)); | |
END IF; | |
END LOOP; | |
IF (description IS NOT NULL) THEN | |
attrs = array_append(attrs, description); | |
END IF; | |
IF (array_length(attrs, 1) > 0) THEN | |
RETURN array_to_string(attrs, '\n'); | |
END IF; | |
RETURN NULL; | |
END; | |
$$ | |
LANGUAGE 'plpgsql' | |
STABLE; | |
SELECT * FROM smart_comments('{"a":1}', 'my description'); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
trim(both '"' from _value)
should probably bearray_to_json(ARRAY[_value])->>0
It's a bit weird, but it's the best way I know of turning a string JSON type (as opposed to object, array, boolean, null, number) back to the actual string.