String comparison between postgresql and log

Question

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?

Answer 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.