PostgreSQL regexp replace function with condition

Question

There is a PostgreSQL table. This table has a field which contains the queries of the stored procedures as string. I am looking for a regexp replace solution which I am able to remove the part of the string with but only in that cases where the string contains 'tmp'.

Example string inputs:

...from schema1.table_1...
...from schema1.table_1_tmp...
...from schema1.table_2...
...from schema1.table_2_tmp...

Aim:

...from schema1.table_1...
...from table_1_tmp...
...from schema1.table_2...
...from table_2_tmp...

schema1 is a static value, only the table names are different. Some of them contains tmp substring, some of them not.

If it contains tmp, we should remove the schema1 string.

Answer 1

You could use regexp_replace() as follows:

regexp_replace(mycol, '\sschema1\.(\w+_tmp)\s', ' \1 ')

Regex breakdown:

\s           a space
schema1\.    litteral string "schema1."
(            beginning of a capturing group
    \w+          at many alphanumeric characters as possible (including "_")
    _tmp         litteral string "_tmp"
)            end of the capturing group
\s           a space

When the string matches the regex, the matching expression is replaced by: a space, then the captured part, then another space.

Demo on DB Fiddle:

with t as (
    select '... from schema1.table_1_tmp ...' mycol
    union all select '... from schema1.table_2 ...'
)
select mycol, regexp_replace(mycol, '\sschema1\.(\w+_tmp)\s', ' \1 ') newcol from t

mycol                            | newcol                      
:------------------------------- | :---------------------------
... from schema1.table_1_tmp ... | ... from table_1_tmp ...    
... from schema1.table_2 ...     | ... from schema1.table_2 ...