CODEWITHSUNDEEP
sqldatabasepostgresql
Phil
Phil

Reputation: 1117

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);
}

Upvotes: 0

Views: 51

Answers (1)

redneb
redneb

Reputation: 23850

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);

Upvotes: 1

Related Questions