Phil Phil - 2 months ago 7
SQL Question

Returning all data in one row

In my Postgresql Database, I have multiple reference data tables.
Example:

- Gender(id:Serial, name:varchar)
- Sexuality(id:Serial, name:varchar)
- Location(id:Serial,name:varchar)


To retrieve all of the information in all of the tables I am doing 3 separate select statements.
Example:

- "SELECT name from Gender;
- "SELECT name from Sexuality;
- "SELECT name from Location;


How can I make this into one call so that it returns one row like this:

ReferenceData(allGenders:varchar[], allSexualities:varchar[], allLocations:varchar[])


I would like to be able to be able to do something like this client side -->

var genders = results.row[0].allGenders;
for gender in genders {
print(gender);
}

Answer

You can use the array_agg aggragate function to combine values from multiple rows into a single array, e.g.

SELECT array_agg(name) FROM Gender

will return all genders as a single array. If you want all the information from all 3 tables at once, you can do it like that:

SELECT
    (SELECT array_agg(name) FROM Gender),
    (SELECT array_agg(name) FROM Sexuality),
    (SELECT array_agg(name) FROM Location);
Comments