JSON output in Postgresql

json, postgresql, sql

Solution

There is built-in support for JSON since PostgreSQL 9.2 and it has increased with many other features in more recent versions(For example: JSON functions in PostgreSQL 0.4).

Specially the `row_to_json` converts a record into a JSON object and the `array_to_json` turns arrays into JSON arrays.

For example, both functions can be combined to easily turn the results of a `SELECT` query into JSON:

SELECT array_to_json(array_agg(row_to_json(t))) FROM 
    (SELECT col1, col2, col3 FROM example_table) t

Problem

I hope I am not missing something very obvious here, I want to get JSON output from a postgres function (I imagine many other had already needed this) and I'd be happy to install an extension of contrib functions on my server, Is there any way to get JSON output from sql or plpgsql functions (or with help of db-server-side python)? Specifically I want to get my `record[]` results as JSON.

Original source