PostgreSQL - CAST vs :: operator on LATERAL table function

While I can

SELECT
  elem[1], elem[2]
FROM
  (
    VALUES ('1,2'::TEXT)
  ) AS q(arr),
  LATERAL CAST(String_To_Array(q.arr, ',') AS INT[]) AS elem
;

using an explicit call to CAST, I can't

SELECT
  elem[1], elem[2]
FROM
  (
    VALUES ('1,2'::TEXT)
  ) AS q(arr),
  LATERAL String_To_Array(q.arr, ',')::INT[] AS elem
;

using the implicitly calling :: operator:

ERROR: syntax error at or near "::"

One other location at which an explicit CAST is required:

CREATE INDEX ON ... ( CAST( AS ) );

I doubt there is a syntactical reason, e.g. using extra enclosing parenthesis - which is incorrect here.

Is the explicit function call simply needed at this point as part of the low level implementation? Or does it follow any language rules?

It is a bit weird, yes, but the grammar will only accept something syntactically similar to a function call in a function-in-FROM expression.

So that is indeed a trick you can use if you want an arbitrary expression in a FROM clause: surround it with an unnecessary CAST expression.

PostgreSQL will happily treat anything that looks like a function as a table function in such a context.

StackExchange Database Administrators Q#280410, answer score: 6