converting SETOF into a string

Question

The function json_object_keys(json) returns a setof text. How do I transform this setof text to a string with all elements separated by a ',' ? I have to use the function array_to_string() but it accepts an array, and not a setof. So how do I convert a setof to array. For example:

DECLARE 
    custom_fields   json;
BEGIN
    custom_fields:='{"name":"John Smith","age":23}';
    keys:=array_to_string(json_object_keys(custom_fields),','::text);
END

The above doesnt work, I am getting:

ERROR:  function array_to_string(text, text) does not exist

So, how do I convert a SETOF to an ARRAY?

Answer 1

The function json_object_keys() is a set-returning function, so you should use it as a row source (= in a FROM clause). With the string_agg() function you then get your result:

SELECT string_agg(cf, ',') INTO keys
FROM json_object_keys(custom_fields) jok(cf);

Note that this only works in a PL/pgSQL function because of the INTO clause.