Re: to_regtype() Raises Error

On 18/09/2023 00:13 CEST David E. Wheeler <david@justatheory.com> wrote:

> The docs for `to_regtype()` say, “this function will return NULL rather than
> throwing an error if the name is not found.” And it’s true most of the time:
>
> david=# select to_regtype('foo'), to_regtype('clam');
>  to_regtype | to_regtype
> ------------+------------
>  [null]     | [null]
>
> But not others:
>
> 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.

> I presume this has something to do with not catching errors from the parser?
>
> david=# select to_regtype('clam bake');
> ERROR:  syntax error at or near "bake"
> LINE 1: select to_regtype('clam bake');
>              ^
> CONTEXT:  invalid type name "clam bake"

Double-quoting the type name to treat it as an identifier works:

    test=# select to_regtype('"clam bake"');
     to_regtype
    ------------
     <NULL>
    (1 row)

So it's basically a matter of keywords vs. identifiers.

--
Erik