For backwards compatibility I guess you can have a GUC flag controlling that behavior that can be set into backwards compatibility mode if required.
The previous functionality can be declared deprecated and removed (with the flag) once the current version becomes unsupported.
Seems more like a per-session setting than a GUC.
Here's a suggestion off the top of my head.
We create a session setting inferred_column_name_template.
The template takes a formatting directive %N which is just a counter
SET inferred_column_name_template = 'col_%N'
which would give you col_1, col_2, regardless of what kind of expression the columns were
We could introduce another directive, %T
SET inferred_column_name_template = '%T_%N'
which prints the datatype short name of the column. In this case, %N would increment per datatype, so text_1, integer_1, text_2, timestamptz_1, text_3
Getting fancier, we could introduce something less datatype centric, %F
SET inferred_column_name_template = '%F_%N'
Which would walk the following waterfall and stop on the first match
1. The datatype short name if the expression is explicitly casted (either CAST or ::)
2. the name of the function if the outermost expression was a function (aggregate, window, or scalar), so sum_1, substr_1
3. 'case' if the outermost expression was case
4. 'expr' if the expression was effectively an operator ( SELECT 3+4, 'a' || 'b' etc)
5. the datatype short name for anything that doesn't match any of the previous, and for explicit casts
Keeping track of all the %N counters could get silly, so maybe a %P which is simply the numeric column position of the column, so your result set would go like: id, name, col_3, last_login, col_5.
We would have to account for the case where the user left either %N or %P out of the template, so one of them would be an implied suffix if both were absent, or we maybe go with
SET inferred_column_name_prefix = '%F_';
SET inferred_column_name_counter = 'position'; /* position, counter, per_type_counter */
Or we just cook up a few predefined naming schemes, and let the user pick from those.
One caution I have is that I have seen several enterprise app database designs that have lots of user-customizable columns with names like varchar1, numeric4, etc. Presumably the user would know their environment and not pick a confusing template.
