Re: Select last there dates

Loredana Curugiu wrote:
> Richard, Andreas,
>
> thank you very much for your solutions. I took a look on
> both solutions, but I choosed Andreas's solution because
> is shorter :)

Not to mention clever, exploiting the fact that we know the length of a
text-representation of three comma-separated dates.

I think there might be a small typo though. The left-join is to:
(select uid, phone_numer, datum from t2 order by 2 )
Probably want to order by "datum DESC" too, to ensure you get the latest
dates for each telnum.

> So Andreas, would you please give some more explanations
> on your solution? I didn't work with functions and aggregate till
> now.
>
> I don't understand how this comma_aggregate works. I can see that this
> function it is defined with two arguments, but the aggredate it is called
> with a single argument.

The comma_aggregate function takes two parameters (RUNNING-TOTAL,
NEW-VALUE). The result forms the RUNNING-TOTAL for the next call. This
RUNNING-TOTAL has a type set by "stype" below and with an initial value
set by "initcond".

> And what is LANGUAGE sql IMMUTABLE STRICT ?

The function is pure SQL (mine was procedural plpgsql, other languages
are available). It's IMMUTABLE because the output depends only on the
inputs, not the contents of the database and begin STRICT if input
values are NULL then the output is automatically NULL.

> CREATE FUNCTION comma_aggregate(text,text) RETURNS text AS '
>> SELECT CASE WHEN $1 <> '''' THEN $1 || '', '' || $2 ELSE $2 END; '
>> LANGUAGE sql IMMUTABLE STRICT;
>>
>> CREATE AGGREGATE comma (basetype=text, sfunc=comma_aggregate, stype=text,
>> initcond='' );

--
Richard Huxton
Archonet Ltd