Re: Collapsing (select) row values into single text field.

2008/12/10 Allan Kamau <allank(at)sanbi(dot)ac(dot)za>

> 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