1

Using Postgresql 11.6. I have values in tab_a.sysdescr that I want to convert using regex_replace and update those converted values into tab_b.os_type.

Here is table tab_a that contains the source string in sysdescr :

   hostname  |   sysdescr      |
-------------+-----------------+
   wifiap01  | foo HiveOS bar  | 
   switch01  | foo JUNOS bar   | 
   router01  | foo IOS XR bar  |

Here is table tab_b that is the target for my update, in column os_type :

   hostname  |   mgmt_ip    | os_type
-------------+--------------+---------
   wifiap01  | 10.20.30.40  | 
   switch01  | 20.30.40.50  | 
   router01  | 30.40.50.60  |

This is example desired state for tab_b :

   hostname  |   mgmt_ip    | os_type
-------------+--------------+---------
   wifiap01  | 10.20.30.40  | hiveos
   switch01  | 20.30.40.50  | junos
   router01  | 30.40.50.60  | iosxr

I have a working query that will work against a single os_type. In this example, HiveOS :

UPDATE tab_b
SET os_type = (
    SELECT REGEXP_REPLACE(sysdescr, '.*HiveOS.*', 'hiveos')
    FROM tab_a
    WHERE tab_a.hostname = tab_b.hostname
)
WHERE EXISTS (
    SELECT sysdescr
    FROM tab_a
    WHERE tab_a.hostname = tab_b.hostname
);

What I can't figure out is how I can "chain" multiple regex_replace functions together into a single query, or via nested sub-queries. Adding 'OR' after that SELECT REGEX_REPLACE line doesn't work, and haven't been able to find examples online of something like this.

End-goal is a single query function that will replace the strings as specified, updating the replaced string on all rows in tab_b. I was hoping to avoid having to delve into PL/Python but if that is the best way to solve this, that's okay. Ideally, I could define a third table that contains the pattern and replacement_string arguments - and could iterate over that somehow.

Edit: Example of what I am trying to accomplish

This is not valid code, but hopefully demonstrates what I am trying to accomplish. A single query that can be executed once, and will translate/transform every sysdescr in a table into proper values for os_type in a new table.

UPDATE tab_b
SET os_type = (
    SELECT REGEXP_REPLACE(sysdescr, '.*HiveOS.*', 'hiveos') OR
    SELECT REGEXP_REPLACE(sysdescr, '.*JUNOS.*', 'junos') OR
    SELECT REGEXP_REPLACE(sysdescr, '.*IOS XR.*', 'iosxr')
    FROM tab_a
    WHERE tab_a.hostname = tab_b.hostname
)
WHERE EXISTS (
    SELECT sysdescr
    FROM tab_a
    WHERE tab_a.hostname = tab_b.hostname
);
refriedjello
  • 657
  • 8
  • 18

2 Answers2

0

If foo and bar are consistent in all rows (as indicated in your example), then this should work:

postgres=# SELECT lower(replace(regexp_replace('foo IOS XR bar','foo (.*) bar','\1'),' ',''));
 lower 
-------
 iosxr
(1 row)

In short, this does the following:

  1. Trim off foo and bar from the front and back with regexp_replace()
  2. Remove the spaces with replace()
  3. Lower-case the text with lower()

If you need to do anything further to remove foo and bar, you can nest the string functions as demonstrated above.

richyen
  • 8,114
  • 4
  • 13
  • 28
  • foo and bar are just sample text that will surround the match strings I am looking for. Extracting / translating the values I need is something I have working (see question). But it only works on a single source/pattern set. I am looking for a way to define a single query that will match against multiple string patterns. I edited my question to try to explain it better. Thanks, – refriedjello Nov 28 '19 at 19:09
  • Have you looked at this post: https://stackoverflow.com/questions/28938905/sql-query-to-match-one-of-multiple-strings – richyen Nov 28 '19 at 22:14
  • I posted answer just now. Was able to solve using a 3rd (lookup) table. Thanks! – refriedjello Nov 28 '19 at 22:18
0

I was able to solve this using a third table (lookup table). It contains two columns, one holding the match string and one holding the return string.

New table tab_lookup:

 id |                   match_str                   | return_str
----+-----------------------------------------------+------------
  1 | HiveOS                                        | hiveos
  2 | IOS XR                                        | iosxr
  3 | JUNOS                                         | junos
  5 | armv                                          | opengear
  6 | NX-OS                                         | nxos
  7 | Adaptive Security Appliance                   | asa
 17 | NetScreen                                     | netscreen
 19 | Cisco Internetwork Operating System Software  | ios
 18 | Cisco IOS Software                            | ios
 20 | ProCurve                                      | hp
 21 | AX Series Advanced Traffic Manager            | a10
 22 | SSG                                           | netscreen
 23 | M13, Software Version                         | m13
 24 | WS-C2948                                      | catos
 25 | Application Control Engine Appliance          | ace

Using this query I can update tab_b.os_type with the appropriate value from tab_lookup.return_str:

UPDATE tab_b
SET os_type = (
    SELECT return_str
    FROM tab_lookup
    WHERE EXISTS (
        SELECT regexp_matches(sysdescr, match_str)
        FROM tab_a
        WHERE tab_a.hostname = tab_b.hostname
    )
);

The only catch I have encountered is that there must be only one match against a given row. But this is easily accomplished by verbose match_str values. E.g, don't use 'IOS' but instead use 'Cisco IOS Software'.

All in all, very happy with this solution since it provides an easy way to update the lookup values, as more device types are added to the network.

refriedjello
  • 657
  • 8
  • 18