Incorporate tuples of information in JSON format stored in array in postresql-CodePudding

I have the following postgresql function that allows me to obtain a table with information from two parameters that are indicated. However, now I want to modify this code so that it allows me to incorporate each tuple of information in JSON format and store all of them in an array and I can not find a way to do it successfully. Thank you in advance for your interest and help.


CREATE OR REPLACE FUNCTION function_name ( date DATE, name text)
RETURNS TABLE (name text, age integer, address character varying (50) , locality character varying (50), ID integer) language plpgsql AS $$
BEGIN 
    RETURN QUERY
            SELECT name, age, address, locality, ID
            FROM people, addresses
END;$$

CodePudding user response：

Not tested as you did not supply example data.

Still here is a potential solution:


SELECT json_agg(row_to_json(row(name, age, address, locality, ID)))
            FROM people, addresses;

If you want jsonb just replace json in above with jsonb.

CodePudding user response：

If you want to return a json array of json objects whose key/value pairs correspond to the selected columns, then you can try this :

CREATE OR REPLACE FUNCTION function_name ( date DATE, name text)
RETURNS jsonb language sql AS
$$
BEGIN
     SELECT jsonb_agg(jsonb_build_object('name', name, 'age', age, 'address', address, 'locality', locality, 'ID', ID))
       FROM people, addresses
END;
$$

but I don't really understand why you have input parameters (date DATE, name text) that you don't use in your function ?