Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- single field is unnested / decomposed automatically
SELECT n FROM unnest (ARRAY[1,2]) n;
SELECT n FROM unnest (ARRAY[1,2]) n(n);
SELECT n FROM unnest (ARRAY[1,2]) t(n);
SELECT t FROM unnest (ARRAY[1,2]) t(n);  -- except output column name is "t"

n

1

2

SELECT 2

n

1

2

SELECT 2

n

1

2

SELECT 2

t

1

2

SELECT 2

-- but not with more than one fields
SELECT t FROM unnest (ARRAY[1,2,3]) WITH ORDINALITY t(n);  -- requires 9.4

t

(1,1)

(2,2)

(3,3)

SELECT 3

-- registered composite type
CREATE TYPE my_type AS (id int, txt text);

CREATE TYPE

-- same: more than one field, so not unnested:
SELECT n FROM unnest(ARRAY[(1, 'foo')::my_type, (2, 'bar')::my_type]) n;

n

(1,foo)

(2,bar)

SELECT 2

-- Guess the result!
SELECT n FROM unnest(ARRAY[(1, 'foo')::my_type, (2, 'bar')::my_type]) n(n);
SELECT n FROM unnest(ARRAY[(1, 'foo')::my_type, (2, 'bar')::my_type]) t(n);
SELECT * FROM unnest(ARRAY[(1, 'foo')::my_type, (2, 'bar')::my_type]) t(n);

n

1

2

SELECT 2

n

1

2

SELECT 2

n
txt

1
foo

2
bar

SELECT 2

-- However (because only a single wrapper is unnested):
SELECT t FROM unnest(ARRAY[(1, 'foo')::my_type, (2, 'bar')::my_type]) t(n);

t

(1,foo)

(2,bar)

SELECT 2

-- In Postgres 9.4+ with the new form of `unnest()` taking multiple array arguments:
SELECT *
FROM   unnest (ARRAY[(1, 'foo')::my_type, (2, 'bar')::my_type]
             , ARRAY[(3, 'baz')::my_type, (4, 'bak')::my_type]) n;

id
txt
id
txt

1
foo
3
baz

2
bar
4
bak

SELECT 2

-- column aliases for only the first two columns
SELECT *
FROM   unnest (ARRAY[(1, 'foo')::my_type, (2, 'bar')::my_type]
             , ARRAY[(3, 'baz')::my_type, (4, 'bak')::my_type]) n(a, b);

a
b
id
txt

1
foo
3
baz

2
bar
4
bak

SELECT 2

-- column aliases for for all output columns
SELECT *
FROM   unnest(ARRAY[(1,'foo')::my_type, (2,'bar')::my_type]
            , ARRAY[(3,'baz')::my_type, (4,'bak')::my_type]) n(a,b,c,d);

a
b
c
d

1
foo
3
baz

2
bar
4
bak

SELECT 2