0

We have one database full of strings of type:

Cannot write block. Device at EOM. dev=$device ($device_path)

And then, we have our program which generates log entries, like, for example

2013-10-10 15:37:07program-sd JobId 50345: Fatal error: block.c:434 Cannot write block. Device at EOM. dev="st0" (/dev/st0)

So, we can use SELECT * FROM X WHERE Y LIKE="%LOG%" but, the strings are not the same, in our database, we have a clean string, while in our log we have the timestamp, more info and also the data for $device and $device_path, so the query will return 0 results, because both doesn't match...

We're trying to return a error code based on what we have on the database, for this example it will be RC: 1019 if the result for the query is not 0...

Is there any way to use regex or something to accomplish this?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Robert W. Hunter
  • 2,895
  • 7
  • 35
  • 73
  • OK, so you want to do m:n pattern matching, where you match a series of patterns against a string and find the best matching pattern / all matching patterns? That's quite hard, and possibly not best done in SQL. Look at dedicated text pattern matching engines. – Craig Ringer Dec 05 '13 at 11:02

1 Answers1

1

Suppose your table of error message templates looks like this:

create table error_templates(id serial primary key, template text);

then you can use a query like this:

select id 
from error_templates 
where $1 like '%' || regexp_replace(template, '\$\w+', '%', 'g') || '%';

$1 is a placeholder for the error log you are trying to find.

The regex_replace replaces the variables in the message with %. It assumes the variables consist of a dollar sign followed by one or more word characters (a-zA-Z0-9_) - you might need to adjust that depending on your actual variable names.

Also note that this may be slow. It will have to scan through the whole table; indexes cannot be used.

harmic
  • 28,606
  • 5
  • 67
  • 91