Skip site navigation (1) Skip section navigation (2)

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

From: Filip Rembiałkowski <filip(dot)rembialkowski(at)gmail(dot)com>
To: "Allan Kamau" <allank(at)sanbi(dot)ac(dot)za>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Collapsing (select) row values into single text field.
Date: 2008-12-10 14:45:36
Message-ID: 92869e660812100645i1ec3edc3ld7329217425cae65@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
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

In response to

Responses

pgsql-sql by date

Next:From: Tom LaneDate: 2008-12-10 17:43:33
Subject: Re: Is there a bug in PostgreSQL ?
Previous:From: Pascal TufenkjiDate: 2008-12-10 14:33:07
Subject: Is there a bug in PostgreSQL ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group