Hi,
I am to achieve something with PostreSql 8.4 but I'm not sure how to tackle it, I was hoping someone here would have some insight.
For example, having a generic table like this:
create table test
(
user_id int,
created_on timestamp,
value int
);
What I'm interested in, is doing a:
select first(value when ordered internally in the group by the created_on field)
from test
group by user_id;
Where "first(...)" actually returns the value for the first event in the group. Alternatively it can store it in an array, then sort it in the "finalfunc" and return the first value - problem lies here that one needs to create a custom type (to put in the array) in order to hold both columns, and that cannot be accomplished with pseudo columns (and you need two of them too).
At first I thought I'd write a user defined aggregate to solve this problem generically using the available polymorphism in PostgreSql.
Alas, soon enough I encountered several problems.
When defining the "stype" in an aggregate, it needs to store both "created_on" and "value" in this example, but doing it generically requires two different types that are not known in advance - which is where the polymorphism breaks as "anyelement" can represent only a single type.
Is there any way to solve such a problem in a generic way (there is no guarantee that "created_on" is unique anywhere within each group).
I'd be curious to hear any ideas you've got.
Tzvi.