To: pgsql-sql@postgresql.org
From: Harald Fuchs <hari.fuchs@gmail.com>
Subject: Re: Window function trouble
Date: Thu, 10 Dec 2009 22:02:00 +0100
Lines: 26
Message-ID: <puy6la8rw7.fsf@srv.protecting.net>
References: <pud42mahnr.fsf@srv.protecting.net>
	<pu7hsuafcq.fsf@srv.protecting.net>
	<25983.1260468559@sss.pgh.pa.us>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.2 (gnu/linux)
Cancel-Lock: sha1:A8x4aW8fo6E22VNeY9KGy1TYzjc=

In article <25983.1260468559@sss.pgh.pa.us>,
Tom Lane <tgl@sss.pgh.pa.us> writes:

> The SQL standard says the default name for any output column other
> than a simple column reference is implementation-dependent.  I think
> our implementation involves looking at the default value for a CASE.

Thanks for the clarification, Tom - so it's not a PostgreSQL bug, but
a misfeature of the SQL standard.

That being said, I still think that PostgreSQL could do better - how
about naming expression columns so that they are distinct from column
names?  The current implementation throws an error e.g. for

  SELECT adr,
         CASE lag(adr) OVER (ORDER BY adr)
         WHEN adr THEN NULL
         ELSE adr
         END,
         usr, count(*)
  FROM tbl
  WHERE host = 'h1'
  GROUP BY adr, usr
  ORDER BY adr, usr

namely `ORDER BY "adr" is ambiguous' which I find somewhat confusing.