Message-ID: <467A7AAD.8090006@archonet.com>
Date: Thu, 21 Jun 2007 14:18:37 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.0 (X11/20070326)
MIME-Version: 1.0
To: Loredana Curugiu <loredana.curugiu@gmail.com>
Cc: andreas.kretschmer@schollglas.com, pgsql-novice@postgresql.org,
	pgsql-sql@postgresql.org
Subject: Re: Select last there dates
References: <1c23c8e70706210118h6b9490e3s11e2a6ba15cdff6c@mail.gmail.com>	
	<20070621095025.GG1405@a-kretschmer.de>
	<1c23c8e70706210600he74293ci83bdbc557ee2dba8@mail.gmail.com>
In-Reply-To: <1c23c8e70706210600he74293ci83bdbc557ee2dba8@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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