I am trying to execute this query using Postgresql 9.2
WITH udetail as (select(regexp_split_to_array('user@domain', E'\\@+')))
select * from udetail[1];
Buy it gives me a syntax error near where the start of '['. This same query is working fine under version 9.3. So I'm wonder if there's an alternative way of writing the query to get the same result.
I think you are looking for something like this:
WITH udetail(x) as (
select(regexp_split_to_array('user@domain', E'\\@+')))
select x[1] from udetail;
I don't think you can index a table expression like udetail in your case. It is the column of the table expression that is of array type. Hence, you can use an array subscript number on the column, not on the table itself.
You should use an alias either as the query parameter:
with udetail(arr) as (
select regexp_split_to_array('user@domain', E'\\@+')
)
select arr[1] from udetail;
or as column alias:
with udetail as (
select regexp_split_to_array('user@domain', E'\\@+') as arr
)
select arr[1] from udetail;
You can also do it without aliases:
with udetail as (
select regexp_split_to_array('user@domain', E'\\@+')
)
select regexp_split_to_array[1] from udetail;
