Обсуждение: ORDER BY with EXCEPT?

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

Thanks,

David

On Thu, 2009-02-19 at 17:13 -0800, David E. Wheeler wrote:
> 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;
> 

You can make it work by naming the first quote_ident like
"quote_ident($2[i]) AS foo" and then doing ORDER BY foo.

It seems a little strange to me, too, but I assume that it's SQL
standard behavior.

Regards,Jeff Davis

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

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.

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…

Thanks,

David

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

I wrote:
>
>
> 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;
>
>
This won't work of course.

brain malfunctioning again :-(

sorry for noise.

andrew