From: Richard Huxton <dev@archonet.com>
To: Loredana Curugiu <loredana.curugiu@gmail.com>
Cc: andreas.kretschmer@schollglas.com
Cc: pgsql-novice@postgresql.org
Cc: pgsql-sql@postgresql.org
Subject: Re: Select last there dates
Date: Thu, 21 Jun 2007 14:18:37 +0100
Message-ID: <467A7AAD.8090006@archonet.com> (raw)
In-Reply-To: <1c23c8e70706210600he74293ci83bdbc557ee2dba8@mail.gmail.com>
References: <1c23c8e70706210118h6b9490e3s11e2a6ba15cdff6c@mail.gmail.com>
<20070621095025.GG1405@a-kretschmer.de>
<1c23c8e70706210600he74293ci83bdbc557ee2dba8@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, loredana.curugiu@gmail.com, andreas.kretschmer@schollglas.com, pgsql-novice@postgresql.org
Subject: Re: Select last there dates
In-Reply-To: <467A7AAD.8090006@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox