postgresql rows to comma separated string Edit

Murugan Andezuthu Dharmaratnam | 01 February 2022 | 191

PostgreSQL function to return comma delimited string from rows. In this example, I have a state table, and the function returns comma separated state names

PostgreSQL function

CREATE OR REPLACE FUNCTION public.commaseparated_rows(
	id1 text)
    RETURNS text
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
declare
	retval text;
	rec record;
begin
for rec in select distinct name from country  where stateid = id1
loop
	retval := coalesce(retval,'') || rec.name  || ', ';
end loop;
return retval;
end;
$BODY$;