Returning all data in one row

By : Phil
Source: Stackoverflow.com
Question!

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);
}
By : Phil


Answers

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);
By : redneb


This video can help you solving your question :)
By: admin