STRAGG Aggregate Function

1. Purpose

This document describes the sys.stragg aggregate function in IvorySQL, which concatenates multiple string values within a group into a single comma-separated string in a manner compatible with Oracle.

2. Functional Description

  • sys.stragg(text) is an aggregate function that joins all non-NULL text values within a group using a comma (,) as the separator.

  • NULL values are silently ignored; they do not appear in the result and do not produce extra commas.

  • When all values in a group are NULL, or when the group is an empty set, the function returns NULL (not an empty string).

  • Output order is not guaranteed. To obtain a deterministic order, use an ORDER BY clause inside the aggregate call (a PostgreSQL aggregate extension).

  • The function is defined in the sys schema and is available in both Oracle compatibility mode (don’t need sys prefix) and PG mode.

3. Syntax

sys.stragg(expr [ORDER BY sort_expr [ASC | DESC] [, ...]])
Parameter Description

expr

Any expression that can be implicitly cast to text. NULL values are skipped.

ORDER BY

Optional. Specifies the concatenation order within the group. If omitted, order is indeterminate.

Return type: text

4. Test Cases

4.1. Test Environment Setup

CREATE TABLE stragg_test (dept TEXT, name TEXT);
INSERT INTO stragg_test VALUES ('HR', 'Alice');
INSERT INTO stragg_test VALUES ('HR', 'Bob');
INSERT INTO stragg_test VALUES ('HR', 'Carol');
INSERT INTO stragg_test VALUES ('IT', 'Dave');
INSERT INTO stragg_test VALUES ('IT', 'Eve');

4.2. Basic Aggregation

-- Single-group aggregation with ORDER BY for deterministic output
SELECT sys.stragg(name ORDER BY name) FROM stragg_test WHERE dept = 'HR';
-- Expected: Alice,Bob,Carol

-- Aggregation with GROUP BY, grouped by department
SELECT dept, sys.stragg(name ORDER BY name)
FROM stragg_test
GROUP BY dept
ORDER BY dept;
-- Expected:
--  HR | Alice,Bob,Carol
--  IT | Dave,Eve

4.3. NULL Value Handling

-- Insert a row with a NULL value
INSERT INTO stragg_test VALUES ('HR', NULL);

-- NULL is ignored; result is the same as without the NULL row
SELECT sys.stragg(name ORDER BY name) FROM stragg_test WHERE dept = 'HR';
-- Expected: Alice,Bob,Carol

-- All-NULL input returns NULL
SELECT sys.stragg(name) IS NULL FROM stragg_test WHERE name IS NULL;
-- Expected: t

4.4. Empty Set Handling

-- No matching rows: returns NULL
SELECT sys.stragg(name) IS NULL FROM stragg_test WHERE dept = 'NONE';
-- Expected: t

4.5. Single Value

-- Only one value in the group: result contains no comma
SELECT sys.stragg(name) FROM stragg_test WHERE dept = 'IT' AND name = 'Dave';
-- Expected: Dave

4.6. Usage in PG Compatibility Mode

SET ivorysql.compatible_mode = pg;
SELECT sys.stragg(name ORDER BY name) FROM stragg_test WHERE dept = 'HR';
-- Expected: Alice,Bob,Carol
RESET ivorysql.compatible_mode;

4.7. Test Environment Cleanup

DROP TABLE stragg_test;

5. Behavioral Differences from Oracle

Scenario Oracle STRAGG IvorySQL sys.stragg

Empty set

NULL

NULL (compatible)

All-NULL group

NULL

NULL (compatible)

NULL values

Ignored

Ignored (compatible)

Concatenation order

Indeterminate

Indeterminate; can be fixed with an ORDER BY clause

ORDER BY clause

Not supported (no official syntax)

Supported (PostgreSQL aggregate extension syntax)

6. Comparison with LISTAGG

Feature LISTAGG (Oracle / IvorySQL) STRAGG (Oracle / IvorySQL)

Separator

Any delimiter can be specified

Fixed comma

ORDER BY

Specified via WITHIN GROUP (ORDER BY …​)

Order not guaranteed; IvorySQL supports PostgreSQL aggregate ORDER BY

Result length limit

Oracle enforces a 4000-byte limit (IvorySQL checks via ora_listagg_check)

No limit

Typical use case

When precise control of delimiter and order is required

Quick concatenation; migration of legacy Oracle code