Create a gist now

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Some functions to convert arrays/hstore to json :)
CREATE OR REPLACE FUNCTION escape_json (text) RETURNS text AS $$
SELECT replace($1, '''', '\'''); $$ LANGUAGE SQL IMMUTABLE;
CREATE OR REPLACE FUNCTION to_json(text) RETURNS text AS $$
SELECT escape_json($1) $$ LANGUAGE SQL IMMUTABLE;
CREATE OR REPLACE FUNCTION to_json(KEY text, value text) RETURNS text AS $$
SELECT '''' || to_json($1) || ''': ''' || to_json($2) || ''''; $$ LANGUAGE SQL IMMUTABLE;
CREATE OR REPLACE FUNCTION to_json(hstore) RETURNS text AS $$
SELECT '{' || array_to_string(array_agg(to_json(item.KEY, item.value)), ', ') || '}'
FROM each($1) item; $$ LANGUAGE SQL IMMUTABLE;
CREATE OR REPLACE FUNCTION to_json(text[]) RETURNS text AS $$
SELECT to_json(hstore($1)); $$ LANGUAGE SQL IMMUTABLE;
SELECT to_json(hstore(array[array['a', 'b'], array['c', 'd']])) two_dimensional_array_to_hstore_to_json,
to_json(array[array['a', 'b'], array['c', 'd']]) two_dimensional_array_to_to_json,
to_json(hstore(array['a', 'b'], array['c', 'd'])) multi_array_to_hstore_to_json,
to_json(hstore(array['a', 'b', 'c', 'd'])) array_to_hstore_to_json,
to_json(array['a', 'b', 'c', 'd']) array_to_to_json;
@joevandyk

This comment has been minimized.

Show comment
Hide comment
@joevandyk

joevandyk Jan 21, 2013

Shouldn't json use double quotes, not single?

Shouldn't json use double quotes, not single?

@egbutter

This comment has been minimized.

Show comment
Hide comment
@egbutter

egbutter Mar 28, 2013

"A value can be a string in double quotes"
via json standard

Thank you for this great example, though!

"A value can be a string in double quotes"
via json standard

Thank you for this great example, though!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment