PostgreSQL – Dynamically Deciding Which Operator to Use Based on Value Type

postgresqlquery

I'm currently implementing a translation between a proprietary expression language and postgres queries at work. So far a lot of stuff fell in place quite nicely, but now I have trouble with one function.
The source language contains a function (contains) that checks whether the first argument contains the second. That means for

  • strings: It checks whether the second arg is a substring of the first arg.
  • arrays: The second arg is an element of the array (first arg)
  • objects/maps/dicts: The second argument is a key of the object (first arg)

Now comes the issue: The translation doesn't know anything about the DB schema, so the queries need to be generic enough to work on different kinds of schemas. So I don't know whether a given field is of a specific type. It is okay to fail in some cases, but I would at least like the code to work for the types text/varchar , text[] and jsonb. But I'm struggling to get even the first two to work.
Here is the (incomplete) WHERE clause of a query that is generated by the translator:

CASE
    WHEN pg_typeof(entity.field1)::text = ANY('{text,character varying}') THEN ( entity.field1 LIKE '%' || entity.field2|| '%' )
    WHEN pg_typeof(entity.field1)::text = 'text[]' THEN ( entity.field2=ANY(entity.field1))
    [...]
    ELSE false
END

Let's assume (for now) that entity.field2 is a text column. entity.field1 is

  • a) a text column
  • b) a text[] column

The query works fine for a), as expected. But it fails for b) with:

operator does not exist: text[] ~~ text

So I broke the query down to:

CASE
    WHEN false THEN ( entity.field1 LIKE '%' || entity.field2 || '%' )
    ELSE false
END

For a) false is returned, as expected. For b) the error is still thrown. So it seems like the operator type check is run, even if there is no way to reach the code in the THEN clause.

I tried to find the reason for that in the docs but I didn't. This paragraph contains some info on early evaluation, but not related to operator type checks.

So, is there a way to get this to work by catching errors, casting (have tried naively casting to text, that works for text[] but not for jsonb), or anything else?

Thanks in advance for your help, it is greatly appreciated.

Best Answer

So after some helpful comments here and from my colleagues at work I ended up creating functions (with overloading) to solve the issue:

CREATE OR REPLACE FUNCTION contains(haystack jsonb, needle text) RETURNS boolean AS $$
    DECLARE
      result boolean;
    BEGIN
      CASE jsonb_typeof(haystack)
        WHEN 'array', 'object' THEN
          result := haystack ? needle;
        ELSE
          result := haystack LIKE '%' || needle || '%';
      END CASE;
      RETURN result;
    END;
  $$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION contains(haystack text, needle text) RETURNS boolean AS $$
  BEGIN
    RETURN haystack LIKE '%' || needle || '%';
  END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION contains(haystack text[], needle text) RETURNS boolean AS $$
  BEGIN
    RETURN needle = ANY(haystack);
  END;
$$ LANGUAGE plpgsql;

Then I can just include contains(entity.field1, entity.field2) in my query and it works for all three types I need right now. I can also easily extend it to work for more types.

Thanks for all your help

Related Question