postgres SQL update column with value from regex + more

Question

I want to update all matched url's in db.table.column. I want to add a param in the end of the url and leave everything else as it was before.

I have tried this:

UPDATE table SET column = regexp_replace(column, 'url_matcher', (substring(column, 'url_matcher') || 'end_param');

UPDATE
    "db"."emailTemplate"
set 
    "bodyText" = regexp_replace(
        "bodyText",
        '({{(@root\.)?app\.url}})[a-zA-Z0-9@:%._\+~#\-&={}\/?]{2,255}',
        (substring("bodyText" from '({{(@root\.)?app\.url}})[a-zA-Z0-9@:%._\+~#\-&={}\/?]{2,255}') || '&param={{param}}')
    ),
    "bodyHtml" = regexp_replace(
        "bodyHtml",
        '({{(@root\.)?app\.url}})[a-zA-Z0-9@:%._\+~#\-&={}\/?]{2,255}',
        (substring("bodyHtml" from '({{(@root\.)?app\.url}})[a-zA-Z0-9@:%._\+~#\-&={}\/?]{2,255}') || '&param={{param}}')
    );

but the substring does not match the whole url it only matches {{app.url}} or {{@root.app.url}} everything else of the url is not matched.

When I alone run regexp_replace("bodyHtml", [same pattern], 'replace_thing') it matches the whole url and replaces that with "replace_thing". So in one case the regex pattern works, and in the other it doesn't. All fields that didn't match a url is not updated, but when using the substring function also, as in my example. all fields that didn't match a url is updated with null.

1. Can I not use nested functions like this? if not - how can I achieve what I want otherwise?

2. Does other regex pattern rules apply to substring function than regexp_replace?

emaxple of column value: Hello user, click here {{app.url}}/home:search=true&item=33 to visit your page regexp_replace will match: {{app.url}}/home:search=true&item=33, substring will only match {{app.url}}

after update the column value should look like this:

Hello user, click here {{app.url}}/home:search=true&item=33&param={{param}} to visit your page

but it ends up looking like this:

Hello user, click here {{app.url}}&param={{param}} to visit your page

a field like this: Hello you have been registered! will look like this: null but should be untouched.

Answer 1

Regex patterns are used to search for matching texts. Inside the replacement patterns, you may not use regex, only specific constructs, e.g. replacement backreferences like \1 to refer to capturing group 1 value. Besides, you need special callbacks to modify match values during replacement, and this is not supported here.

You may wrap the whole pattern with ( and ) (to create a capturing group) and use a regex like this:

'({{(@root\.)?app\.url}}[a-zA-Z0-9@:%._+~#&={}/?-]{2,255})'

The replacement will look like

'\1&param={{param}}'

where \1 is a replacement backreference to Group 1 value. See the online demo

select regexp_replace('Hello user, click here {{app.url}}/home:search=true&item=33 to visit your page',
                      '({{(@root\.)?app\.url}}[a-zA-Z0-9@:%._+~#&={}/?-]{2,255})',
                      '\1&param={{param}}');