PostgreSQL Function: Return Multiple Rows as JSON

Question

I have a PostgreSQL function which successfully returns its data as

table(key character varying, value integer)

but I want to return JSON as it's more convenitent. I have looked at some other answers, and documentation, but it gets complicated when aliases are used (I'd like to return 'key' and 'value' as the column names).

Can someone please recommend the most simple and concise way of expressing this function, in a way that does not require extra complexity to call:

DROP FUNCTION get_document_metadata_integer_records_by_document_id(integer);

CREATE OR REPLACE FUNCTION get_document_metadata_integer_records_by_document_id(document_id integer)
  RETURNS table(key character varying, value integer) AS
$BODY$

  SELECT document_metadata_records.key, document_metadata_integer_records.value FROM document_metadata_integer_records
  LEFT OUTER JOIN document_metadata_records
  ON document_metadata_integer_records.document_metadata_record_id = document_metadata_records.id
  WHERE document_metadata_records.document_id = $1

$BODY$
  LANGUAGE sql VOLATILE
  COST 100;
ALTER FUNCTION get_document_metadata_integer_records_by_document_id(integer)
  OWNER TO postgres;

Clodoaldo Neto · Accepted Answer · 2014-08-19 17:16:15Z

1

Use cross join lateral as a clean and easy path to create a composite type that can be used by row_to_json

create or replace function get_document_metadata_integer_records_by_document_id(
    _document_id integer
) returns setof json as $body$

select row_to_json(s)
from
    document_metadata_integer_records dmir
    left outer join
    document_metadata_records dmr on dmir.document_metadata_record_id = dmr.id
    cross join lateral
    (select dmr.key, dmir.value) s
where dmr.document_id = _document_id
;
$body$ language sql stable;

then use it as

select get_document_metadata_integer_records_by_document_id(1) as pair;

or

select pair from get_document_metadata_integer_records_by_document_id(1) j(pair);

or make it return a table

) returns table (pair json) as $body$

and use as

select pair from get_document_metadata_integer_records_by_document_id(1);

With older versions of Postgresql without lateral it is possible to do it in a subquery

select row_to_json(s)
from
    (
        select dmr.key, dmir.value
        from 
            document_metadata_integer_records dmir
            left outer join
            document_metadata_records dmr on dmir.document_metadata_record_id = dmr.id
        where dmr.document_id = _document_id
    ) s

edited Aug 19, 2014 at 17:16

answered Aug 19, 2014 at 0:58

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1315172 Over a year ago

What is the 'stable' at the end for?

Clodoaldo Neto Over a year ago

@user1315172 Check the definitions of volatile, stable and immutable

Collectives™ on Stack Overflow

PostgreSQL Function: Return Multiple Rows as JSON

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related