Re: ORDER BY with EXCEPT?

David E. Wheeler wrote:
> Howdy,
>
> I was just updating a function in pgTAP that, given a schema name and 
> an array of function names, returns a set of those function names that 
> are not in the named schema. I got it working with a subquery, and 
> David Fetter suggested that I try an EXCEPT query instead. The only 
> problem is that it doesn't like my ORDER BY clause. The function is:
>
> CREATE OR REPLACE FUNCTION  mytest(NAME, NAME[]) RETURNS setof text AS $$
>         SELECT quote_ident($2[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 s.i
> $$ LANGUAGE SQL;
>
> When I run this, PostgreSQL 8.3 tells me:
>
> ERROR:  missing FROM-clause entry for table "s"
> LINE 10:          ORDER BY s.i
>
> Um, really" Have I not put the ORDER BY clause in the right place? Is 
> this a bug?


The docs say 
<http://www.postgresql.org/docs/current/static/sql-select.html#SQL-ORDERBY>: 


"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" ?

cheers

andrew