gp_legacy_string_agg
gp_legacy_string_agg
The gp_legacy_string_agg module re-introduces the single-argument string_agg() function that was present in Greenplum Database 5.
The gp_legacy_string_agg module is a Greenplum Database extension.
Installing and Registering the Module
The gp_legacy_string_agg module is installed when you install Greenplum Database. Before you can use the function defined in the module, you must register the gp_legacy_string_agg extension in each database where you want to use the function. Refer to Installing Additional Supplied Modules for more information about registering the module.
Using the Module
The single-argument string_agg() function has the following signature:
string_agg( text )
You can use the function to concatenate non-null input values into a string. For example:
SELECT string_agg(a) FROM (VALUES('aaaa'),('bbbb'),('cccc'),(NULL)) g(a); WARNING: Deprecated call to string_agg(text), use string_agg(text, text) instead string_agg -------------- aaaabbbbcccc (1 row)
The function concatenates each string value until it encounters a null value, and then returns the string. The function returns a null value when no rows are selected in the query.
string_agg() produces results that depend on the ordering of the input rows. The ordering is unspecified by default; you can control the ordering by specifying an ORDER BY clause within the aggregate. For example:
CREATE TABLE table1(a int, b text); INSERT INTO table1 VALUES(4, 'aaaa'),(2, 'bbbb'),(1, 'cccc'), (3, NULL); SELECT string_agg(b ORDER BY a) FROM table1; WARNING: Deprecated call to string_agg(text), use string_agg(text, text) instead string_agg -------------- ccccbbbb (1 row)
Migrating to the Two-Argument string_agg() Function
Greenplum Database 6 includes a native, two-argument, text input string_agg() function:
string_agg( text, text )
The following function invocation is equivalent to the single-argument string_agg() function that is provided in this module:
string_agg( text, '' )
You can use this conversion when you are ready to migrate from this contrib module.