By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,009 Members | 1,487 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,009 IT Pros & Developers. It's quick & easy.

Multi dimensional arrays, sub selects...

P: 4
Hi All,

I have a system that stores information about a person, name addresses and so on and I would like to return a single row for each person in an SQL statement. This is so it can be placed in a view making it easy to obtain all information for person x.

Since a person can have more than one address I thought I could build an array holding a row for each address with each row also been an array to store the fields.

The schema is a person table with their name and id in it and an addresses table that holds the address information:

Person:
int8 id,
text name
...

Addresses
int8 id,
int8 personid,
text line1,
text line2,
text town,
...

For example the result I would like is

1,Jon, Doe, { {1 street,some place,some city}, {2 drive,some other place, some other city} }

However I cannot figure out the SQL to achieve this.

For the SQL I had something like this:

SELECT p.id, p.name,

ARRAY[COALESCE(a.line1,''),
COALESCE(a.line2 ,''),
COALESCE(a.Town,''),
] AS "Address",


FROM People AS p
JOIN Addresses AS a ON a.PersonId=p.id;

(Coalesce since cannot have nulls in arrays :( )

Which works in getting a single address into an array. Of course if the person has more than one address I get two rows returned for person x.

Attempting to re-work the SQL I could not get anything to run, the closest been:

SELECT p.id, p.name,

ARRAY[ ( SELECT COALESCE(a.line1,''),
COALESCE(a.line2 ,''),
COALESCE(a.Town,''),
FROM Addresses AS a ON a.PersonId=p.Id ) ] AS Addresses

FROM People AS p

Which obviously fails if there is more than one address.

Suggestions or ideas would be welcome.

Thanks
Feb 9 '07 #1
Share this Question
Share on Google+
2 Replies


P: 4
So I take that no one has any ideas?

Thanks.
Feb 12 '07 #2

Expert 100+
P: 534
Sorry, Mark,
this is not something I can answer without first trying some code, unfortunately the last few days were crazy and I still have no spare time...

I guess you may have already checked out the manual on array functions and operators?

http://www.postgresql.org/docs/8.0/s...ons-array.html

http://www.postgresql.org/docs/8.0/static/arrays.html

Michael.
Feb 13 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.