Re: to_regtype() Raises Error

On Sun, Sep 17, 2023 at 6:25 PM Chapman Flack <chap(at)anastigmatix(dot)net> wrote:

> On 2023-09-17 20:58, David G. Johnston wrote:
> > Put differently, there is no syntax involved when the value being
> > provided
> > is the text literal name of a type as it is stored in pg_type.typname,
> > so
> > the presence of a syntax error is wrong.
>
> Well, the situation is a little weirder than that, because of the
> existence
> of SQL standard types with multiple-token names; when you provide the
> value 'character varying', you are not providing a name found in
> pg_type.typname (while, if you call the same type 'varchar', you are).
> For 'character varying', the parser is necessarily involved.
>

Why don't we just populate pg_type with these standard mandated names too?

>
> The case with 'interval second' is similar, but even different still;
> that isn't a multiple-token type name, but a type name with a
> standard-specified bespoke way of writing a typmod. Another place
> the parser is necessarily involved, doing another job. (AFAICT,
> to_regtype is happy with a typmod attached to the input, and
> happily ignores it, so to_regtype('interval second') gives you
> interval, to_regtype('character varying(20)') gives you
> character varying, and so on.)
>
>
Seems doable to teach the lookup code that suffixes of the form (n) should
be ignored when matching the base type, plus maybe some kind of special
case for standard mandated typmods on their specific types. There is some
ambiguity possible when doing that though:

create type "interval second" as (x int, y int);
select to_regtype('interval second'); --> interval

Or just write a function that deals with the known forms dictated by the
standard and delegate checking for valid names against that first before
consulting pg_type? It might be some code duplication but it isn't like
it's a quickly moving target and I have to imagine it would be faster and
allow us to readily implement the soft-error contract.

David J.