Is it possible to return an array as a field as part of a larger select? For
example... there are two tables, products and photos. There are many photos
for each product. What would be cool is if I could send one query and get
all product info as well as all photo info as an array field which I can
later use in my front end script in each returned row.
Example...
products {
id,
title,
description
}
photos {
id_product,
caption,
filename
}
SELECT
products.id,
products.title,
products.description,
(SELECT caption, filename FROM photos WHERE id_product = 10) AS photos
FROM products
WHERE id = 10;
The above does not work, but I was hoping to find out if anything like this
possible. It seems since PostgreSQL supports array column types that it
would be if only I could figure out the syntax to pull it off.
Thanks in advance.
Alan
