Returning an array as a field in a larger query?

From: "Alan T(dot) Miller" <amiller(at)hollywood101(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Returning an array as a field in a larger query?
Date: 2005-05-25 06:33:03
Message-ID: 00ab01c560f3$9a80f830$0b01a8c0@webdev
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Kevin Crenshaw 2005-05-25 14:27:25 Unable to Delete a Row
Previous Message Pradeepkumar, Pyatalo (IE10) 2005-05-25 04:17:42 Unable to vacuum database