postgresql - PostgreSQL 中两个数值数组的笛卡尔列表？

Question

尝试获取传递给 PostgreSQL 的 2 个数组的所有可能组合的列表，例如:

create function foobar(
    _foo varchar(250)[],
    _bar varchar(250)[] ) returns table
    (
    foo varchar(250),
    bar varchar(250)
    ) as $$
begin
    return query
    select distinct
        ???,
        ???
    from
        ??? cross join
        ???;
end;
$$ language plpgsql;

...所以如果我尝试:

select * from foobar('{"1", "2", "3", "4"}', '{"5", "6", "7", "8"}');

...我会得到一个看起来像这样的结果集:

foo bar
1   5
1   6
1   7
1   8
2   5
2   6
...

该查询会是什么样子？好像PostgreSQL会支持这样的东西，但我不知道如何以可搜索的方式询问。

谢谢!

更新在 Frank 的帮助下，我能够生成我正在寻找的查询。作为一个独立的查询，它看起来像这样:

select distinct
    foo, bar
from unnest(cast('{"1", "2", "3", "4"}' as varchar(250)[])) as foo
cross join unnest(cast('{"5", "6", "7", "8"}' as varchar(250)[])) as bar;

这正是我正在寻找的结果。

Answer 1

WITH foo(y) AS (
    SELECT unnest(a) FROM ( VALUES(ARRAY[1,2,3,4])) x(a) -- your input, first array
),
bar(y) AS (
    SELECT unnest(a) FROM ( VALUES(ARRAY[5,6,7,8])) x(a) -- second array
)
SELECT
    *
FROM
     foo CROSS JOIN bar
ORDER BY 
    foo.y, bar.y;