Re: BUG #19767: pg_get_viewdef prints ORDER BY (- '3'::int4) as (- 3), which reparses as column position -3

Hi,

On Fri, Oct 16, 2026 at 9:46 PM PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:
>
> pg_get_viewdef() prints that term as "(- 3)". When that text is parsed
> again, the grammar folds "- 3" into the integer constant -3 (doNegate),
> which ORDER BY then reads as a column position. So the printed
> definition of a valid view is not a valid view, and pg_dump, which
> prints views the same way, writes a dump that does not restore.
>
> ```
> CREATE TABLE t (a int);
> INSERT INTO t VALUES (1), (2);
> CREATE VIEW o AS SELECT a FROM t ORDER BY (- '3'::int4);
> CREATE VIEW g AS SELECT count(*) AS n FROM t GROUP BY (- '3'::int4);
> SELECT * FROM o; -- 1, 2
> SELECT * FROM g; -- 2
>

I'm not sure if this kind of use case is common enough to warrant
concern. But I looked into a possible fix. The fix is small where
during deparsing, get_oper_expr checks if the argument to a prefix
operator is a constant and forces adding '::type' to it (so it would
create 3::int4 in this case). It might lead to some extra '::type'
prints in other cases unaffected by the bug but that seems fine to me.

I'm attaching the patch with the fix and a couple of tests. Please take a look.

Thanks & Regards,
Nitin Motiani
Google