PostgreSQL parses string literal as record before calling cast

castcomposite-typespostgresql

In my PostgreSQL 9.6 database, I have a composite type, let's call that type widget. I have a PL/pgSQL* function called text_to_widget that parses textual representations of widgets to widget values, and I have an implicit cast that calls this function:

CREATE CAST (text AS widget) WITH FUNCTION text_to_widget(text) AS IMPLICIT;

My cast is working well from variables or table values, but when I try to use the cast (either implicitly or explicitly) from string literals, I get an error:

postgres=# select 'my widget value'::widget;
ERROR:  malformed record literal: ""
LINE 1: select 'my widget value'::widget;
               ^
DETAIL:  Missing left parenthesis.

If I explicitly cast my string literal as text first, then it works fine:

postgres=# select 'my widget value'::text::widget;
      widget
-------------------
 (my,widget,value)
(1 row)

I'm just confused why Postgres is trying to parse my string literal as a record before passing it to my cast, and if there's a way to make it stop doing that.

* Note that this database is hosted in AWS RDS, so I can't use a C function for the cast.

Best Answer

As the linked answer on SO explains, a cast is only applicable to the exact input data type declared in the CREATE CAST definition. And a string literal is of data type unknown initially - even though the default data type of a string literal is text.

Postgres 10 ties up some loose ends for the data type unknown with this commit (with explanation).
And unknown is a pseudo-type now. Details in this commit.

But your problem still does not go away, because you try to cast a string literal directly, before the new default kicks in.

You write:

PostgreSQL parses string literal as record before calling cast

That's almost but not exactly what happens: A string literal representing a composite or row type is required to be enclosed in parentheses. That's the immediate cause of the error. The literal is not "cast as record" and the cast would never be called. This would actually work with the default "I/O conversion cast":

SELECT '(my,widget,value)'::widget;

You might do without the custom cast altogether and just use your function directly instead:

SELECT text_to_widget('my widget value');

Does exactly the same anyway. And you don't need to cast the function parameter because Function Type Resolution is smart enough to chose the right function (as long as the call is unambiguous).

Related Question