将 postgreql 数组展开为行

在 PostgreSQL 中，将数组展开为行的最快方法是什么,

我们有:

a
-
{1,2}
{2,3,4}

我们需要:

b
-
1
2
2
3
4

我在用:

select explode_array(a) as a from a_table;

哪里是哪里是哪里是哪里是哪里:

create or replace function explode_array(in_array anyarray) returns setof anyelement as
$$
select ($1)[s] from generate_series(1,array_upper($1, 1)) as s;
$$

还有更好的办法吗？

125508 次浏览
小开
最佳答案

Use unnest. For example:

CREATE OR REPLACE FUNCTION test( p_test text[] )
RETURNS void AS
$BODY$
BEGIN
SELECT id FROM unnest( p_test ) AS id;
END;
$BODY$
LANGUAGE plpgsql IMMUTABLE
COST 1;
小开

unnest --> expand an array to a set of rows

unnest(ARRAY[1,2])
1
2

http://www.sqlfiddle.com/#!1/c774a/24

小开

If you have a table users_to_articles like:

user_id articles
1 {1,2}
2 {6,2,7}

And need to explode the articles array so to obtain:

user_id  article_id
1 1
1 2
2 6
2 2
2 7

You could run something like that:

CREATE TABLE "users_to_articles" (
"user_id" SERIAL PRIMARY KEY,
"articles" INT[]
);


INSERT INTO "users_to_articles" ("articles")
VALUES ('{1,2}'), ('{6,2,7}');


SELECT
"user_id"
, "article_id"
FROM "users_to_articles", unnest("articles") AS "article_id";

Here is a working sqlfiddle:
http://www.sqlfiddle.com/#!17/c26742/1


提交答案