Re: to_regtype() Raises Error

On Sun, Sep 17, 2023 at 5:34 PM Erik Wienhold <ewie(at)ewie(dot)name> wrote:

> On 18/09/2023 00:57 CEST Vik Fearing <vik(at)postgresfriends(dot)org> wrote:
>
> > On 9/18/23 00:41, Erik Wienhold wrote:
> > > On 18/09/2023 00:13 CEST David E. Wheeler <david(at)justatheory(dot)com>
> wrote:
> > >
> > >> david=# select to_regtype('inteval second');
> > >> ERROR: syntax error at or near "second"
> > >> LINE 1: select to_regtype('inteval second');
> > >> ^
> > >> CONTEXT: invalid type name "inteval second”
> > >
> > > Probably a typo and you meant 'interval second' which works.
> >
> > No, that is precisely the point. The result should be null instead of
> > an error.
>
> Well, the docs say "return NULL rather than throwing an error if the name
> is
> not found".

> To me "name is not found" implies that it has to be valid syntax
> first to even have a name that can be looked up.
>

Except there is nothing in the typed literal value that is actually a
syntactical problem from the perspective of the user. IOW, the following
work just fine:

select to_regtype('character varying'), to_regtype('interval second');

No need for quotes and the space doesn't produce an issue (and in fact
adding double quotes to the above causes them to not match since the
quoting is taken literally and not syntactically)

The failure to return NULL exposes an implementation detail that we
shouldn't be exposing. As Tom said, maybe doing better is too hard to be
worthwhile, but that doesn't mean our current behavior is somehow correct.

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.

David J.