unwrap postgresql array into rows
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])12
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