array Q - Mailing list pgsql-general

From André M. Winter - Carto.net
Subject array Q
Date
Msg-id 42D21F05.5000709@carto.net
Whole thread Raw
List pgsql-general
hi,

i am working on a dataset containing *ordered* lists of points. in order
to keep things simple, i only have to tables:
- points with ids,
- tours with ids and arrays of points-ids (called 'stations').

there is no other relation between those tables. on a certain point i am
doing an implicit join over both with something like

SELECT DISTINCT points.id
WHERE [...]
AND  points.id = ANY (tours.stations);

this works nice as long as tours.stations contains data of type int[]
(like '{6,5,7,8,4}'). making things more complicated, i wanted some
switches on my stations list in introducing a structure of int[][], e.g.
like '{{6,1},{5,0},{7,1},{8,1},{4,0}}', where the second dimension holds
some type-definition on points in the specific tour.

although i am able to define int[][] and work with it properly, i am not
able to do a similar implicit join like above:

[...] AND '{points.id,1'} = ANY (tours.stations);

in that case (however i do write my left hand side sub-array) i get the
message that int[] cannot be compared to int. indeed the 2-dimension
array within the ANY statement gets linearized into one dimension as
'{6,1,5,0,7,1,8,1,4,0}'. can this be done another way?

i do know about the fact that arrays are not yet fully implemented and
that there are better ways of handing this, but for now it would
represent an easy way of working with my dataset.

thanks for hints on this,
andré

--
___________________________________________________________________
andre m. winter,
 cartography for internet and multimedia applications
 a6091 goetzens, tyrol, austria
 tel.: ++43.5234.32732
 email: <winter@carto.net>

<http://www.vectoreal.com/>          SVG consulting and development
<http://www.carto.net/>          online cartography focusing on SVG
<http://www.carto.at/>     print and online touristic map solutions


pgsql-general by date:

Previous
From: "Andrus"
Date:
Subject: How to fix invalid multibyte character for locale error
Next
From: Geert Jansen
Date:
Subject: Transaction isolation levels