On Tue, Sep 14, 2010 at 2:07 AM, Gerhard Wiesinger <lists@xxxxxxxxxxxxx> wrote: > Hello Merlin, > > Seems to be a feasible approach. On problem which might be that when > multiple rows are returned that they are not ordered in each subselect > correctly. Any idea to solve that? > > e.g. > Raumsolltemperatur | Raumisttemperatur > Value from time 1 | Value from time 2 > Value from time 2 | Value from time 1 > > but should be > Raumsolltemperatur | Raumisttemperatur > Value from time 1 | Value from time 1 > Value from time 2 | Value from time 2 > > But that might be solveable by first selecting keys from the log_details > table and then join again. > > I will try it in the evening and I have to think about in detail. > > But thank you for the new approach and opening the mind :-) Using subquery in that style select (<subquery>), ... is limited to results that return 1 row, 1 column. I assumed that was the case...if it isn't in your view, you can always attempt arrays: CREATE OR REPLACE VIEW log_entries AS SELECT l.id AS id, l.datetime AS datetime, l.tdate AS tdate, l.ttime AS ttime, array(select value from log_details ld join key_description kd on ld.fk_keyid = kd.keyid where ld.fk_id = l.id and description = 'Raumsolltemperatur' order by XYZ) AS Raumsolltemperatur, [...] arrays might raise the bar somewhat in terms of dealing with the returned data, or they might work great. some experimentation is in order. XYZ being the ordering condition you want. If that isn't available inside the join then we need to think about this some more. We could probably help more if you could describe the schema in a little more detail. This is solvable. merlin -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
