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.
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.
