Re: ORDER BY with EXCEPT?

David E. Wheeler wrote:
> On Feb 19, 2009, at 5:45 PM, Andrew Dunstan wrote:
>
>> "A limitation of this feature is that an ORDER BY clause applying to 
>> the result of a UNION, INTERSECT, or EXCEPT clause can only specify 
>> an output column name or number, not an expression."
>>
>> Why not just say "order by 1" ?
>
> Well, in this case, I wanted the order to be the same as in the array 
> that was passed.


Yeah. you can do it like this:

select foo from (       SELECT quote_ident($2[i]) as foo, i         FROM generate_series(1, array_upper($2, 1)) AS s(i)
     EXCEPT       SELECT quote_ident(p.proname)         FROM pg_catalog.pg_proc p         JOIN pg_catalog.pg_namespace
n          ON p.pronamespace = n.oid          AND quote_ident(n.nspname) = quote_ident($1)        ORDER BY i ) x;
 

>
> At any rate, your quotation of this documentation that I obviously 
> missed answers my question. In the meantime, I got a different version 
> with a LEFT JOIN to do what I want, so I don't need the EXCEPT at all. 
> I just posted here because it looked like a bug. And though it's 
> clearly not, since it's documented, it is kinda weird…
>
>

There are many odd corners, unfortunately.

cheers

andrew