Hello!
This seems like a good feature, thanks for working on it.
I have two questions:
* ALL is added to the shared sort_clause, wouldn't be a separate
select-level nonterminal (used only by the select_no_parens
productions and PLpgSQL_Expr) simpler?
* Is Query.orderByAll needed? Isn't the list printed by the existing
get_rule_orderby the same?
And some initial testing findings:
1. pg_dump can't restore an empty select list WITH TIES
create table tt(a int); insert into tt values (1),(2);
create view v_ties as select from tt order by all fetch first 1 row with ties;
select pg_get_viewdef('v_ties');
-- SELECT
-- FROM tt
-- FETCH FIRST (1) ROWS WITH TIES;
2. The deparse only looks at the first column's sort operator
create type pair as (x int, y int);
create view v1 as select row(1,2)::pair as p, 1 as n order by all;
create view v2 as select 1 as n, p from (values (row(1,2)::pair),
(row(2,1)::pair)) s(p) order by all;
-- later: CREATE OPERATOR CLASS ... DEFAULT FOR TYPE pair USING btree
(reversed ops)
select pg_get_viewdef('v1'); -> ORDER BY ALL USING < NULLS LAST;
-- syntax error on restore
select pg_get_viewdef('v2'); -> ORDER BY ALL; -- restores, but
returns (2,1),(1,2) instead of (1,2),(2,1)
-- same view with "order by n, p": ORDER BY 1::integer, p USING < NULLS LAST
3. errors can lose print error positions
(select 1 order by 1) order by all;
-- ERROR: multiple ORDER BY clauses not allowed
with recursive r(n) as (select 1 union all select n+1 from r where n<3
order by all) select * from r;
-- ERROR: ORDER BY in a recursive query is not implemented
4.
+ /*
+ * Ignore junk TLEs. These are system columns (like
ctid, xmin)
+ * or internal working columns that shouldn't be part
of the
+ * user-visible ordering.
+ */
+ if (tle->resjunk)
+ continue;
but
select ctid, a from t order by all; -- works, sorts by ctid
select xmin, a from t order by all; -- fails, no operator for xmin
so that comment seems wrong