Search code examples
postgresqlplpgsqlstring-constant

How to make a string from Array in plpgsql

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;
Solution

  • 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;