SQL: asign array return value as a tuple-CodePudding

Let's say I have a value like xxx.yyy. I want to "extract" xxx.yyy and yyy and give them names.

I can use regexp_matches in postgres like so:

with const as (
  SELECT regexp_matches('xxx.yyy' ,'((. \.).*)') as foo
)
select foo from const;

This returns:

{xxx.yyy,xxx.}

Now I know I can get the individual elements from the result by doing (...)[1] etc, but I want to know if there is a way of doing something like this:

with const as (
  SELECT regexp_matches('xxx.yyy' ,'((. \.).*)') as (bar, baz)
)
select bar, baz from const;

the above syntax of course gives me an error. so I guess I want to know if there is a way to destructure/pattern match on an array in SQL.

CodePudding user response：

It's like this

with const as (
  SELECT regexp_matches('xxx.yyy' ,'((. \.).*)') as foo
)
select unnest(foo) from const;

or as named tuples

with const as (
  SELECT regexp_matches('xxx.yyy' ,'((. \.).*)') as foo
)
select * from unnest((select foo from const), ARRAY['bar','baz']) as x(a,b);