Re: to_regtype() Raises Error

On 18/09/2023 00:57 CEST Vik Fearing <vik@postgresfriends.org> wrote:

> On 9/18/23 00:41, Erik Wienhold wrote:
> > On 18/09/2023 00:13 CEST David E. Wheeler <david@justatheory.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.

String 'inteval second' is a syntax error when interpreted as a type name.
The same when I want to create a table with that typo:

    test=# create table t (a inteval second);
    ERROR:  syntax error at or near "second"
    LINE 1: create table t (a inteval second);

And a custom function is always an option:

    create function to_regtype_lax(name text)
      returns regtype
      language plpgsql
      as $$
    begin
      return to_regtype(name);
    exception
      when others then
        return null;
    end
    $$;

    test=# select to_regtype_lax('inteval second');
     to_regtype_lax
    ----------------
     <NULL>
    (1 row)

--
Erik