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

Hi Nitin,

> 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 had been tracking an issue with the same cause, and this fix
resolves it too. When a user-defined prefix "-" operator is visible in
the search_path, pg_get_viewdef() prints it as a bare "-", and running
that text again folds "- 1" into the constant -1, so the operator
silently disappears:

create schema s;
create function s.neg42(int) returns int language sql immutable
return 42;
create operator s.- (rightarg = int4, function = s.neg42);
set search_path = s, pg_catalog, public;
create view public.vneg as select -(1::int) as x;
select x from public.vneg; -- 42
select pg_get_viewdef('public.vneg', true) as def \gset
create view public.vneg_re as :def
select x from public.vneg_re;

Before the patch:

select pg_get_viewdef('public.vneg', true);
pg_get_viewdef
-------------------
SELECT - 1 AS x;
(1 row)

select x from public.vneg_re;
x
----
-1
(1 row)

With the patch:

select pg_get_viewdef('public.vneg', true);
pg_get_viewdef
----------------------------
SELECT - 1::integer AS x;
(1 row)

select x from public.vneg_re;
x
----
42
(1 row)

That case seems even less common than the reported one, and pg_dump
keeps the meaning since it prints OPERATOR(s.-), so I do not think
it changes the case for the fix, and I am not sure it is worth a test
of its own either.

On master, the code change alone leaves all existing regression
output unchanged, so the extra '::type' you mention does not show up
there; it only affects prefix operators applied to int4 and numeric
constants, the only ones printed without a label. I also built REL_15
through REL_19 with the patch and ran the regression tests: the code
applies cleanly to all of them, and the patch applies as it is from
REL_16 on; on REL_15 only the expected output needs regenerating, for
the column qualification pretty mode still prints there.

Best regards,
Henson