⤵

PostgreSQL Set-Returning Functions

Complete reference for PostgreSQL set-returning functions (SRFs) that return multiple rows from a single function call. Covers generate_series(), unnest(), json_each(), regexp_split_to_table(), and LATERAL join usage patterns. Updated for PostgreSQL 16.

6 functions

Jump to function

generate_series generate_subscripts unnest (set-returning context)json_each / jsonb_each (SRF)json_array_elements (SRF)regexp_split_to_table (SRF)

What are PostgreSQL Set-Returning Functions?

PostgreSQL set-returning functions are functions that return a set of rows rather than a single scalar value. generate_series() generates sequences of integers or timestamps, unnest() expands arrays into individual rows, and json_each() expands JSON objects into key-value row pairs. Set-returning functions can appear in the FROM clause or SELECT list and are commonly combined with LATERAL joins for row-by-row expansion and data generation.

generate_series

PG 8.0+ (integer/numeric); PG 8.4+ (timestamp/timestamptz)→ setof same type as inputs

Generates a series of values from start to stop (inclusive) with the given step. Works with integers, numeric, and timestamps.

DeveloperData EngDBA

Signatures

generate_series ( start integer, stop integer [, step integer] ) → setof integer

generate_series ( start bigint, stop bigint [, step bigint] ) → setof bigint

generate_series ( start numeric, stop numeric [, step numeric] ) → setof numeric

generate_series ( start timestamp, stop timestamp, step interval ) → setof timestamp

generate_series ( start timestamptz, stop timestamptz, step interval ) → setof timestamptz

Parameters

Parameter	Type	Description
start	integer, numeric, or timestamp	Starting value (inclusive)
stop	same as start	Stopping value (inclusive)
step	same as start, or interval for timestamps	Increment (default 1). Negative step counts down.

Examples

sql

SELECT generate_series(1, 5);

→1, 2, 3, 4, 5

sql

SELECT generate_series(0, 10, 2);

→0, 2, 4, 6, 8, 10

sql

SELECT generate_series(now(), now() + interval '6 hours', interval '1 hour') AS hour;

→7 hourly timestamps

sql

SELECT date_trunc('day', generate_series('2025-01-01'::date, '2025-01-07'::date, '1 day'));

→7 days in January

⚠Anti-Pattern

Use `generate_series` + LEFT JOIN to ensure all time slots appear in a report, even with zero counts. `WITH dates AS (SELECT generate_series(...)) SELECT d.ts, count(e.id) FROM dates d LEFT JOIN events e ON date_trunc('hour', e.ts) = d.ts GROUP BY d.ts`.

example

SELECT g.day, count(o.id) AS orders FROM generate_series('2025-01-01'::date, '2025-01-31'::date, '1 day') AS g(day) LEFT JOIN orders o ON o.created_at::date = g.day GROUP BY 1 ORDER BY 1;

→Daily order counts including days with zero orders

generate_subscripts

PG 8.4+→ setof integer

Generates a series of valid subscripts (indices) for the given dimension of the array.

DeveloperData EngDBA

Signature

generate_subscripts ( anyarray, integer [, boolean] ) → setof integer

Parameters

Parameter	Type	Description
array	anyarray	Array to generate subscripts for
dim	integer	Array dimension (1 for 1D)
reverse	boolean	If true, generate in reverse order

Examples

sql

SELECT generate_subscripts(ARRAY['a','b','c'], 1);

→1, 2, 3

sql

SELECT i, arr[i] FROM generate_subscripts(ARRAY[10,20,30], 1) AS i, (VALUES (ARRAY[10,20,30])) AS t(arr);

→Index and value pairs

sql

SELECT i FROM generate_subscripts(ARRAY[5,4,3,2,1], 1, true) AS i;

→5, 4, 3, 2, 1 (reverse order)

sql

SELECT i, arr[i] FROM (SELECT ARRAY['x','y','z'] AS arr) t, generate_subscripts(t.arr, 1) AS i;

→1|x, 2|y, 3|z using lateral reference

⚠Anti-Pattern

Prefer `unnest(arr) WITH ORDINALITY` for index+value pairs — it's cleaner. Use `generate_subscripts` when you need subscripts for non-1-based arrays or multi-dimensional access.

example

SELECT i, arr[i] FROM (VALUES (ARRAY['a','b','c'])) t(arr), generate_subscripts(arr, 1) AS i;

→1|a, 2|b, 3|c

unnest (set-returning context)

PG 8.4+ (WITH ORDINALITY requires PG 9.4+)→ setof anyelement

Expands an array into a set of rows. When used in a FROM clause, returns one row per element. Can unnest multiple arrays in parallel.

DeveloperData EngDBA

Signature

unnest ( anyarray ) → setof anyelement

Parameters

Parameter	Type	Description
array	anyarray	Array to expand

Examples

sql

SELECT * FROM unnest(ARRAY[1,2,3]) AS t(n);

→n=1, n=2, n=3 (3 rows)

sql

SELECT * FROM unnest(ARRAY['a','b'], ARRAY[1,2]) AS t(letter, num);

→a|1, b|2 (parallel unnest)

sql

SELECT val, pos FROM unnest(ARRAY['x','y','z']) WITH ORDINALITY AS t(val, pos);

→x|1, y|2, z|3

sql

SELECT p.id, tag FROM products p, unnest(p.tags) AS tag WHERE tag ILIKE '%organic%';

→All products whose tags array contains an 'organic' tag

⚠Anti-Pattern

Add `WITH ORDINALITY` to any set-returning function to get an automatic position counter. For unnest: `unnest(arr) WITH ORDINALITY AS t(val, pos)` gives both the value and its 1-based position.

example

SELECT product_id, rank FROM unnest(ranked_products) WITH ORDINALITY AS t(product_id, rank);

→Products with their rank position

json_each / jsonb_each (SRF)

json_each: PG 9.2+; jsonb_each: PG 9.4+→ setof (key text, value json/jsonb)

Set-returning function that expands a JSON object into key-value rows. See also json category.

DeveloperData EngDBA

Signatures

json_each ( json ) → setof (key text, value json)

jsonb_each ( jsonb ) → setof (key text, value jsonb)

Parameters

Parameter	Type	Description
json_col	json or jsonb	JSON object to expand

Examples

sql

SELECT key, value FROM jsonb_each('{"a":1,"b":2}'::jsonb);

→a|1, b|2

sql

SELECT id, key, value FROM config, jsonb_each(settings);

→All config key-value pairs as rows

sql

SELECT key, value::text FROM json_each('{"name":"Alice","age":30}'::json);

→name|Alice, age|30

sql

SELECT p.id, k.key, k.value #>> '{}' AS val FROM products p, jsonb_each(p.attributes) AS k ORDER BY 1, 2;

→All product attribute key-value pairs as text

⚠Anti-Pattern

Combine `jsonb_each` with a lateral join to pivot dynamic JSON keys into rows. Useful for EAV-like queries on JSON columns.

example

SELECT p.id, k.key, k.value FROM products p, jsonb_each(p.attributes) AS k WHERE k.value::text != '""';

→All non-empty product attributes as rows

json_array_elements (SRF)

json_array_elements: PG 9.2+; jsonb_array_elements: PG 9.4+→ setof json or jsonb

Set-returning function that expands a JSON array into individual JSON values — one row per element.

DeveloperData EngDBA

Signatures

json_array_elements ( json ) → setof json

jsonb_array_elements ( jsonb ) → setof jsonb

Parameters

Parameter	Type	Description
json_array_col	json or jsonb	JSON array to expand

Examples

sql

SELECT value FROM jsonb_array_elements('[1,2,3]'::jsonb);

→1, 2, 3 (3 rows)

sql

SELECT o.id, e ->> 'sku' AS sku FROM orders o, jsonb_array_elements(o.items) AS e;

→SKU per line item per order

sql

SELECT elem ->> 'name' AS name, (elem ->> 'score')::numeric AS score FROM jsonb_array_elements('[{"name":"A","score":9.5},{"name":"B","score":8.0}]'::jsonb) AS elem ORDER BY score DESC;

→A|9.5, B|8.0

sql

SELECT pos, value FROM jsonb_array_elements('["x","y","z"]'::jsonb) WITH ORDINALITY AS t(value, pos);

→1|x, 2|y, 3|z (with position)

⚠Anti-Pattern

After `jsonb_array_elements(col) AS e`, access fields with `e ->> 'key'` for text or `e -> 'key'` for JSON. Cast to the right type: `(e ->> 'price')::numeric`.

example

SELECT sum((item ->> 'qty')::int * (item ->> 'price')::numeric) AS total FROM carts, jsonb_array_elements(items) AS item;

→Cart total from JSON line items

regexp_split_to_table (SRF)

PG 8.3+→ setof text

Splits a string by a regex pattern and returns each part as a separate row.

DeveloperData EngDBA

Signature

regexp_split_to_table ( string text, pattern text [, flags text] ) → setof text

Parameters

Parameter	Type	Description
string	text	String to split
pattern	text	Regex delimiter

Examples

sql

SELECT * FROM regexp_split_to_table('one two  three', '\s+') AS word;

→one, two, three (3 rows)

sql

SELECT word, length(word) AS len FROM regexp_split_to_table('PostgreSQL is powerful', '\s+') AS word ORDER BY len DESC;

→Words sorted by length descending

sql

SELECT pos, part FROM regexp_split_to_table('a,b,,c', ',') WITH ORDINALITY AS t(part, pos);

→1|a, 2|b, 3|(empty), 4|c — preserves empty segments

sql

SELECT p.id, word FROM posts p, regexp_split_to_table(lower(p.body), '[^a-z]+') AS word WHERE word <> '';

→All non-empty words across all posts

⚠Anti-Pattern

In a FROM clause, `regexp_split_to_table` applies per row. Combine with a lateral join: `FROM posts p, regexp_split_to_table(p.content, '\s+') AS word`.

example

SELECT p.id, word, count(*) OVER (PARTITION BY p.id) AS word_count FROM posts p, regexp_split_to_table(lower(p.title), '\s+') AS word WHERE length(word) > 3;

→Word frequency analysis per post title

PostgreSQL Set-Returning Functions

Jump to function

What are PostgreSQL Set-Returning Functions?

generate_series

Signatures

Parameters

Examples

generate_subscripts

Signature

Parameters

Examples

unnest (set-returning context)

Signature

Parameters

Examples

json_each / jsonb_each (SRF)

Signatures

Parameters

Examples

json_array_elements (SRF)

Signatures

Parameters

Examples

regexp_split_to_table (SRF)

Signature

Parameters

Examples

Related PostgreSQL Categories