Postgresql, escape character in string_agg function

0 votes
1 answer
3633 views
                          **Question**, can I add escape character \ just for fields param_name and value for this?

    string_agg(distinct '{name:"' || param_name || '",value:"' || value || '"}',',') as "params",

Result is.

    {name:"připojení",value:"1/2""},

I need this (use \ before " only for fields/vars inside "param_name" and "value", in case, when value has " character.

    {name:"připojení",value:"1/2\""},

Thanks.

**Update**, no json, I need export to csv. Some code here.

    copy(	
    
    with recursive
    cte as(
    select
    category_id,
    category_parent,
    category_name::text,
    category_id::text category_ids
    from s_category as c
    where category_parent = 0
    union all
    select
    c.category_id,
    c.category_parent,
    concat(cte.category_name, ' > ', c.category_name),
    concat(cte.category_ids, ':', c.category_id::text)
    from s_category as c,cte
    where cte.category_id = c.category_parent
    )
    
    select distinct
    s_product.product_id as "itemID",

    ...

    string_agg(distinct '{name:"' || param_name || '",value:"' || value || '"}',',') as "params",
    case when price_tax = price_rec then '' else 'Akce' end as "label"
    
    from
    s_product
    left join s_cf_j_product_value on s_product.product_id = s_cf_j_product_value.product_id
    left join s_product_image on s_product.product_id = s_product_image.product_id
    left join s_pricelist_generated_lists on s_product.product_id = s_pricelist_generated_lists.product_id
    left join s_producer on s_product.producer_id = s_producer.producer_id
    left join s_category on s_product.category_id = s_category.category_id
    left join cte on s_product.category_id = cte.category_id
    left join s_cf_j_product_value as a on s_product.product_id = a.product_id
    left join s_cf_value as v on a.value_id = v.value_id
    left join s_cf_param as p on v.param_id = p.param_id
    
    where

    ...
    
    group by
    s_product.product_id,
    s_product_image.filename,
    s_category.category_id,
    s_pricelist_generated_lists.price_tax,
    s_pricelist_generated_lists.price_rec,
    s_producer.producer_name,
    cte.category_name,
    cte.category_ids
    
    ) TO stdout DELIMITER ',' CSV;

It returns this.

    ... {name:""připojení"",value:""1/2""""},{name:""připojovací rozteč"",value:""558 mm""},{name:""rozměry"",value:""600 x 960 mm""}",""

But I need this.

    ... {name:"připojení",value:"1/2\""},{name:"připojovací rozteč",value:"558 mm"},{name:"rozměry",value:"600 x 960 mm"}","



                        
Asked by genderbee (183 rep)
Aug 7, 2019, 08:57 AM
Last activity: Nov 21, 2020, 09:04 AM
Postgresql, escape character in string_agg function

Related Questions
