CODEWITHSUNDEEP
sqlpostgresql
Sleepy Bear
Sleepy Bear

Reputation: 33

Can you concatenate a string value multiple times in a PostgreSql SELECT statement?

In Sql Server you can run the following query to combine values from multiple rows into a single string variable.

DECLARE @x NVARCHAR(MAX) = N'';

SELECT TOP 5 @x = @x + name + ', '
FROM sys.objects

SELECT @x

Which results in

"sysrscols, sysrowsets, sysclones, sysallocunits, sysfiles1,"

Is there a way to do something similar in PostgreSql? I've tried creating a PostgreSql function that does a SELECT INTO stringVariable but that is only resulting in a single row's worth of values.

Upvotes: 2

Views: 408

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

Not exactly. The code you have in SQL Server is not actually guaranteed to work. In Postgres, you would use:

select string_agg(name, ', ')
from sys.objects;

If you have a sys.objects table.

Note that in SQL Server, you should do the same thing:

select string_agg(name, ', ')
from sys.objects;

as demonstrated in this SQL Fiddle.

As a note: Your use of the variable is directly contradicted by the documentation:

If a SELECT statement returns more than one row and the variable references a non-scalar expression, the variable is set to the value returned for the expression in the last row of the result set.

That said, I know what you are doing actually works in practice. But I would recommend using string_agg() or in earlier versions using XML for string concatenation.

Upvotes: 3

Related Questions