Re: BUG #15611: pg_dump output changes after doing a restore with certain views

PG Bug reporting form <noreply@postgresql.org> writes:
> When I do the following:

> 1. Create a view with a `foo IN (arglist)` clause in the target list
> 2. pg_dump
> 3. Restore from the dump
> 4. Run pg_dump again

> The output of the two pg_dumps differs.

Yeah, unfortunately it's difficult to do much about that without creating
worse problems than we'd solve.

The core of the issue here is that type varchar doesn't have its own
equality operator, it uses text's.  While the coercion to text is done
implicitly when you first put in the expression, pg_dump shows it
explicitly in order to be sure that the view will be re-parsed using
the same operator as before.  And then the parser is "smart" about a
construct like "ARRAY[...]::type[]" and pushes the coercion down to
the array elements; that's a bit of a hack but people would be sad
if it went away.  I've experimented with trying to make that happen
while parsing IN initially, but that also fails, on examples like

    select * from pg_class
    where oid::regclass in ('sometable', 'someothertable');

Here it's *essential* to parse the literals as type regclass, not
type OID which is what the comparison operator's input type is.
So it's quite hard to twiddle any aspect of this behavior without
causing somebody's use-case to break.

TBH you could most easily dodge this problem by declaring your
table column as type text not varchar.

            regards, tom lane