CODEWITHSUNDEEP
postgresqlplpgsql
goralph
goralph

Reputation: 1086

Performing a LIKE operation only on columns of the supported type

[Homework]

I have written a plpgsql function that takes a table name as input for each column in that table runs some query involving the LIKE operator.

For tables containing columns with types such as integer I get the following, unsurprising, error:

ERROR: operator does not exist: integer ~~ unknown

This makes sense, since LIKE makes no sense on an integer type.

I could check to make sure that the type of each column is not integer, date, ... however it seems hacky to hardcode the data types that do / do not support the LIKE operation, i.e that are not "textual". Additionally, there are a lot of custom data types used in the DB that represent strings of some sort.

I'm wondering what would be a good way to approach this issue. Is there a way to check that a certain column of some type can support a LIKE query?

Upvotes: 1

Views: 25

Answers (1)

Tom-db
Tom-db

Reputation: 6868

Not sure that is possible to check if generically a type supports the LIKE operator, but you can always run the query and trap the error if it occurs .

The LIKE operator throws in this case a undefined_function error. You can catch this error within a a exception block and do something else instead. See this example function:

CREATE or replace FUNCTION test() RETURNS void AS
$BODY$
BEGIN      
  BEGIN -- begin of the exception block
    PERFORM 1 LIKE '10'; --  <--this code throws a exception
    EXCEPTION
      WHEN undefined_function THEN
        RAISE WARNING '%', 'could not execute query!';
  END;  -- end of the exception block
END;
$BODY$
LANGUAGE plpgsql;

See for more info the documentation and the list all error codes

Upvotes: 1

Related Questions