2008/12/10 Allan Kamau <[EMAIL PROTECTED]> > Hi all, > I would like to concatenate the field values of several rows in a table > that meet some similarity criteria based on a the values of some other > field (more like a group by). Then I would also like to also include the > lowest value of another associated field along. > > I have a table that contains 3 fields of interest. > create table temp > (id INTEGER NOT NULL > ,location TEXT NOT NULL --this will hold the zip code > ,lowest_temp NUMERIC(5,2) NOT NULL --The lowest temperature at some given > night > ,location_bit_data VARBIT NOT NULL > ,PRIMARY KEY(id) > ); > > There will be usually more than one record for a location > (location+lowest_temp is not unique either). > Now I would like to collapse the data in this table (an populate another > table) as follows. > Lets assume this table has the structure below. > > create table temp_major > (id INTEGER NOT NULL > ,location TEXT NOT NULL --this will hold the zip code > ,lowest_overall_temp NUMERIC(5,2) NOT NULL --The lowest temperature at some > given night > ,overall_location_bit_data VARBIT NOT NULL > ,PRIMARY KEY(id) > ,UNIQUE(location) > ); > > The new table (temp_major) is population as follows: the > "location_bit_data" values for a given location are "grouped" into one entry > (to create a concatenation effect), the lowest_temp reading across all the > records of the given location is noted and the location is also noted, this > data is used in populating the table. > > The solution I have so far involves using a stored procedure and cursors > (on Select .. order by location) to continuously "grow" the data for a given > location's "overall_location_bit_data" field. > > Allan. >
sounds like you need a custom aggregate function. http://www.postgresql.org/docs/current/static/xaggr.html however it's not clear how you want to aggregate; what does your actual grouping function do? general pattern is: CREATE FUNCTION varbit_concat(varbit,varbit) returns varbit as 'whatever you need' language 'of your choice' immutable; CREATE AGGREGATE agg_varbit_concat ( varbit ) ( SFUNC = varbit_concat, STYPE = varbit -- check CREATE AGGREGATE syntax, maybe you need something fancy here ); -- and finally: SELECT location, min(lowest_temp) as lowest_overall_temp, agg_varbit_concat(location_bit_data) as overall_location_bit_data FROM temp; -- Filip Rembiałkowski
