/*
In this example we have a table (denorm)
containing ids and an array of 
other ids.  We want to to flatten
the arrays, creating parent and child
tables.

This is also an example of using a
function as a subquery.
*/
DROP TABLE IF EXISTS denorm CASCADE;
DROP TABLE IF EXISTS parent CASCADE;
DROP TABLE IF EXISTS child;

CREATE TABLE denorm(
	id SERIAL PRIMARY KEY,
	other_ids INTEGER[] NOT NULL);

CREATE TABLE parent(id SERIAL PRIMARY KEY);

CREATE TABLE child(
	parent_id INTEGER NOT NULL REFERENCES parent, 
	kid_id INTEGER);

INSERT INTO denorm(other_ids) VALUES
	(ARRAY[1,2,3]), (ARRAY[4,5,6]), (ARRAY[2,3,4]);

INSERT INTO parent SELECT id FROM denorm;

INSERT INTO child SELECT d.id, o.id 
FROM denorm d CROSS JOIN LATERAL unnest(d.other_ids) AS o(id);

\echo 'denorm'
select * from denorm;
\echo 'parent'
select * from parent;
\echo 'child'
select * from child;