Thanks Filip for the User Defined Aggregates information I will look
into it, and compare its performance with the another probable
solution (explained next).
I do recall making use of arrays (then array_to_string()) to do this
kind of collapsing as a subquery in the select clause of a group by
query, but I can not recall the actual syntax.
Allan.
On Wed, Dec 10, 2008 at 4:45 PM, Filip Rembiałkowski
<filip.rembialkowski@gmail.com> wrote:
>
>
> 2008/12/10 Allan Kamau <allank@sanbi.ac.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
>
