Received: from anchor-post-31.mail.demon.net (anchor-post-31.mail.demon.net [194.217.242.89]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f2EL7cx82997 for ; Wed, 14 Mar 2001 16:07:38 -0500 (EST) (envelope-from olly@lfix.co.uk) Received: from lfix.demon.co.uk ([158.152.59.127] helo=linda.lfix.co.uk) by anchor-post-31.mail.demon.net with esmtp (Exim 2.12 #1) id 14dIUZ-000HvC-0V; Wed, 14 Mar 2001 21:07:35 +0000 Received: from lfix.co.uk (olly@localhost [127.0.0.1]) by localhost (8.11.3/8.11.3/Debian 8.11.2-1) with ESMTP id f2EL7ZG21981; Wed, 14 Mar 2001 21:07:35 GMT Message-Id: <200103142107.f2EL7ZG21981@linda.lfix.co.uk> X-Mailer: exmh version 2.3.1 01/18/2001 (debian 2.3.1-1) with nmh-1.0.4+dev X-URL: http://www.lfix.co.uk/oliver X-face: "xUFVDj+ZJtL_IbURmI}!~xAyPC"Mrk=MkAm&tPQnNq(FWxv49R}\>0oI8VM?O2VY+N7@F- KMLl*!h}B)u@TW|B}6 cc: pgsql-sql@postgresql.org Subject: Re: need to join successive log entries into one In-reply-to: Message from George Young of Wed, 14 Mar 2001 12:24:12 EST. <01031413092300.02477@pen> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Date: Wed, 14 Mar 2001 21:07:35 +0000 From: "Oliver Elphick" X-Archive-Number: 200103/290 X-Sequence-Number: 1954 George Young wrote: ... >I need to produce a new table that coalesces immediately successive >operations on a run into one, e.g.: > >run | start | done > 1415| 2001-01-29 12:36:55| 2001-02-07 13:02:38 > 1415| 2001-02-14 07:40:04| > 1747| 2001-02-15 09:14:39| 2001-03-01 09:02:39 > 1747| 2001-03-05 13:13:58| > 1954| 2001-02-02 20:55:39| 2001-03-02 10:17:15 > >i.e. where a run has one or more steps with succesive seq values, >or equivalently, with abutting start/end values, then I want >the new table to have only one entry representing the full span of >time. Null 'done' just means it's not done yet. ... >Can anyone think of a way I can do this in postgres? I think you would be better off feeding the log out of the database into a perl script. SQL is a set-oriented language that can't do this sort of thing without some appalling contortions, if at all. (I can't think of a way to do it.) So, store the data in PostgreSQL, but do procedural processing in a more appropriate language. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "Let your light so shine before men, that they may see your good works, and glorify your Father which is in heaven." Matthew 5:16