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'))) - LENGTH(REPLACE(((regexp_matches(t."page", '^.+\/c\/(.+?)(?=\/\?|\?|\/$).*$', 'g'))),'/','')))) 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?