Multi-Dimensional PG Queries?

csn

I used MS's ADO and family once upon a time. With it you could do "multi-dimensional" queries. For example, table buildings:

id
name
type

table architects:

id
name

table building architects:

building_id
architect_id

You could create a query that would return a recordset as a column - say "building_architects" - of a query that returned buildings. Is there anything comparable in PostgreSQL? With sub-queries maybe? Or is there no way around having to do two separate queries?

BTW, are there any plans to add multi-dimensional/OLAP capabilities to PG? I have no idea if that sort of stuff is even in the SQL spec 😉.

Sxooter

I'm not sure what you're asking, exactly. Are you looking at getting back a result like this:

$rows[0]['architect'] = "Jones, John";
$rows[0]['building'] = "Sears Tower";
...

$rows[99]['architect'] = "Smith, George";
...

Or what?

csn

Like that, but the $rows[x]['building_architects'] column would be an array of rows:

$rows[x]['building_architects'][0]['name'] = 'im pei'
$rows[x]['building_architects'][1]['name'] = 'frank lloyd wright'

Sxooter

But we'd still need the [x] at the beginning or no?

$rows[x]['building_architects'][0]['name'] = 'im pei'
$rows[x]['building_architects'][1]['name'] = 'frank lloyd wright'

$rows['building_architects'][0]['name'] = 'im pei'
$rows['building_architects'][1]['name'] = 'frank lloyd wright'

csn

The x is the different buildings, i.e.:

$rows[0]['name'] = 'search tower'
$rows[0]['building_architects'][0]['name'] = 'im pei'
$rows[0]['building_architects'][1]['name'] = 'frank lloyd wright'

$rows[1]['name'] = 'empire state building'
$rows[1]['building_architects'][0]['name'] = 'godzilla'
$rows[1]['building_architects'][1]['name'] = 'mothra'
$rows[1]['building_architects'][2]['name'] = 'king kong'

or as you'd actually retrieve them (with fetch_assoc):

$row['name'] = 'empire state building'
$row['building_architects'][0]['name'] = 'godzilla'
$row['building_architects'][1]['name'] = 'mothra'
$row['building_architects'][2]['name'] = 'king kong'

csn

foreach ($row['building_architects'] as $architect) {
  print $architect['name'];
}

Sxooter

OK. I see where you're getting to. I'm not sure you'd get exactly that kind of output easily (by the way, that's EXACTLY how LDAP output works...) but you could probably write some kind of stored procedure to do something like it, and iterate across it with PHP to get your data structure.

Maybe even a view would give you what you want.

csn

The only things I can think of are:

return 'building_architects' as a PostgreSQL array.
Using a sub-select/function to return columns like architect1_name, architect2_name, etc.

But, I'll probably just retrieve them with a second query in PHP. No big deal at any rate 😉.

I found some more stuff though:

Yantra
http://raghuram.virtualave.net/proj.html

Multidimensional Data Cubes with EFEU and PostgreSQL
http://www.informit.com/isapi/product_id~%7B78B6BFD4-D0D4-45A8-A1C2-44EC746DFD7B%7D/session_id~%7BD9AE6730-26B0-48DF-BF7B-9E33CA435048%7D/content/index.asp

gOLAP
http://sourceforge.net/projects/golap

PG SQL conformance (there's some "OLAP facilities" stuff):
http://www.postgresql.org/docs/7.3/static/features.html

Sxooter

I've heard lots of good about efeu. I'm not that familiar with the other. You might wanna look at plR is you're looking at this kinda stuff.