ORDER BY with EXCEPT?

From: "David E(dot) Wheeler" <david(at)kineticode(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: ORDER BY with EXCEPT?
Date: 2009-02-20 01:13:31
Message-ID: 56DB39A7-77D1-47C2-B3E7-D6FB587C9D00@kineticode.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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?

Thanks,

David

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Jeff Davis 2009-02-20 01:30:35 Re: ORDER BY with EXCEPT?
Previous Message David Fetter 2009-02-20 00:26:56 Re: Proposed Patch to Improve Performance of Multi-BatchHash Join for Skewed Data Sets