How to make a string from Array in plpgsql

Question

I've got a result from my function

EXECUTE format('SELECT ARRAY (SELECT tvmid from "%s".tvmtable order by tvmid)', operatorName) INTO tvms;

that gives mi array of tvms in format {1,2,3}. I need it to have a String divided with , so I'm trying like

SELECT ARRAY_TO_STRING(tvms, ",") INTO res;

but when I'm executing my function I got error

ERROR:  column "," does not exist

If it helps here is my whole function

create or replace function getTVMList(operatorName varchar)
returns varchar as $$
declare
tvms varchar[];
res varchar;
begin
EXECUTE format('SELECT ARRAY (SELECT tvmid from "%s".tvmtable order by tvmid)', operatorName) INTO tvms;
SELECT ARRAY_TO_STRING(tvms, ",") INTO res;
return res;
end;
$$
language plpgsql;

In this case, the aggregate function `string_agg` can ve used too. It will be faster, simpler and more readable. — Pavel Stehule, Mar 08 '21 at 06:27

score 1 · Accepted Answer · answered Mar 03 '21 at 11:08

1

Double quotes reference a column name.

You have to put the comma into single quotes instead of double quotes: ','

 SELECT ARRAY_TO_STRING(tvms, ',') INTO res;

answered Mar 03 '21 at 11:08

S-Man

22,521
7
40
63

How to make a string from Array in plpgsql

1 Answers1