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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
