2

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.

  • 1
    Have a look: stackoverflow.com/a/34191049/3270427 – McNets May 30 '17 at 17:22
  • Why not disclose the composite type definition in question, so everybody can test? – Erwin Brandstetter May 31 '17 at 5:30
  • @ErwinBrandstetter: Unfortunately, I think McNets hit it on the head. The question he linked to seems to answer my question (string literals aren't text values, their type is unknown; and no, there isn't a way around this behavior). My composite type, and my problem, are actually almost exactly the same as the linked question. I can post a more specific repro, but perhaps given the duplicate nature of this question with the one on SO, I should simply delete this question. – P Daddy May 31 '17 at 19:41
  • @PDaddy: Nothing unfortunate about this. The linked answer is good. But my advice concerning your question still stands - which is very interesting, regardless. There is more to it and there is also related news for the upcoming Postgres 10. I added an answer here. – Erwin Brandstetter Jun 1 '17 at 3:57
2

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.