Need
FIELD like this, with ' and without , at the end of whole field.
{name:'color',value:'red'},{name:'width',value:'300 ml'}
Tried
string_agg(distinct '{name:\'' || param_name || '\',value:\'' || value || '\'},','') as "FIELD"
but error behind 2nd \
SQL Error [42601]: ERROR: syntax error at or near "\"
With , at the end of whole field, I don't know how to do that.
Thanks.
to escape a ' in a string in postgres, you have to double it
List of escape sequences for Postgresql string data types
so this give you this :
select string_agg(distinct '{name:''' || name || ''',value:''' || value || '''}',',') as "FIELD"
I tested it, it works =)
However, i don't know why you want to create this field, but it's probably better to follow a_horse_with_no_name's answer and use Jsonb_agg and look around Json manipulation in Postgres
If you want to generate JSON, it is easier to use JSON functions for that, e.g.:
string_agg(distinct jsonb_build_object('name', param_name, 'value', value)::text, ',')
Or create a proper JSON array of the result:
jsonb_agg(distinct jsonb_build_object('name', param_name, 'value', value))
Online example: https://rextester.com/EPOX97933