Keep the "group by" value in new table (ST_MakeLine)

I would like to keep some information about a group after grouping points into lines.

My data.

A table of points (Points are grouped by segments and segments by frame, ordered by time)

I have the following SQL Query to create a new table of the points grouped by frame:

-- Create frames from points

CREATE TABLE data.greenland_2011_p3_frames
(frm_id serial PRIMARY KEY,
geom GEOMETRY (LineString,3413));

INSERT INTO data.greenland_2011_p3_segs(geom) 

SELECT ST_MakeLine(geom) as point_geom 
FROM (SELECT * FROM data.greenland_2011_p3 ORDER BY time) a GROUP BY frame;

I want to keep in the new table the frame value and segment value (from the original table)

So the new table would be of the form:

frm_id      geom       frame   segment
1        linestring    ######   ######
2        linestring    ######   ######
3        linestring    ######   ######
...          ...         ...      ...

Here is an example frame/segment

SEGMENT = 2011031614  (YYYYMMDD(SEG))  ***segment 14

FRAME = 2011031614001  or 2011031614002 (YYYYMMDD(SEG)(FRM))   ***segment 14 frames 1 and 2

EDIT:

I'd like to add a secondary step to this. With the solution from Mike I now have a table with the following:

frm_id      geom       frame   segment
1        linestring   *14001    *14
2        linestring   *14002    *14
3        linestring   *14003    *14
4        linestring   *15001    *15
5        linestring   *15002    *15
....

I'd now like to create something like this:

seg_id      geom      segment         frames
1        linestring     *14    <*14001,*14002,*14003>
2        linestring     *14       <*15001,*15002>
....

How can this be accomplished?

Solution

Say you have a master table of point geometries, frames, segments and times:

create table greenland_2011_p3 (
  gid serial primary key,
  geom geometry(Point,3413),
  frame integer,
  segment integer,
  time timestamp
);
insert into greenland_2011_p3(geom, frame, segment, time)
values
('SRID=3413;POINT(0 0)', 10, 20, '2011-08-03 10:30'),
('SRID=3413;POINT(0 1)', 10, 20, '2011-08-03 10:32'),
('SRID=3413;POINT(1 1)', 12, 20, '2011-08-03 10:35'),
('SRID=3413;POINT(1 0)', 12, 20, '2011-08-03 10:38');

You can write an aggregate that has the "ORDER BY" within the select statement, and identify which other columns need to be grouped for the result:

SELECT ST_MakeLine(geom ORDER BY time) AS geom, frame, segment
FROM greenland_2011_p3
GROUP BY frame, segment;

Or if you want to list all of the frames as a string, you can use the string_agg aggagrate function:

SELECT ST_MakeLine(geom ORDER BY time) AS geom, segment,
    '<' || array_agg(DISTINCT frame::text, ',') || '>' AS frames
FROM greenland_2011_p3
GROUP BY segment;

There is also an array_agg function if you want an array object for other purposes.