Postgres select using lateral I'm after running a query with a subquery (perhaps inefficient) that using a regular expression pattern it can return how many instances of a characters are included with a given record.

In doing so, I came across something like:

SELECT t."page",
            product_tree_length,
           Count(*), 
  FROM     "Table" t
  ,
  LATERAL (SELECT (LENGTH((regexp_matches(t."page", '^.+\/c\/(.+?)(?=\/\?|\?|\/$).*$', 'g'))[1]) - LENGTH(REPLACE(((regexp_matches(t."page", '^.+\/c\/(.+?)(?=\/\?|\?|\/$).*$', 'g'))[1]),'/','')))) AS "product_tree_length"
  where t.page like '%\/c/%'
  GROUP BY 1, product_tree_length;


However, in selecting the product_tree_length in the main query, the returned data type is a record and not an integer as expected.
If I try to cast the value, PostgreSQL raise an exception that the operation cannot be completed.

Any idea on what I am doing wrong?

Thanks
Well product_tree_length is a table alias, not a column alias. So if you use that alias in the SELECT list, obviously you get a record. 

You have two options to properly define an alias for the column

Solution one: use a column alias inside the derived table:

select t.page
       product_tree_length.len,
       count(*)
from "Table" t
  cross join lateral (
     SELECT length(....) as len
  ) AS product_tree_length
...


Solution two: define a column alias as part of the table alias:

select t.page
       product_tree_length.len,
       count(*)
from "Table" t
  cross join lateral (
     SELECT length(....)
  ) AS product_tree_length(len)
...