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:

    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

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:

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

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.

  • Couldn't you write the function in pl/pgsql? See postgresql.org/docs/current/plpgsql.html Oct 2 '20 at 1:41
  • I had hoped that I could avoid that, because I am using an ORM wrapper (typeorm) and use their query builder to build the translated queries. But maybe I will end up using pl/pgsql. I just thought it might be possible right there in the WHERE clause. Oct 2 '20 at 6:25
  • Perhaps I don't get something - what data type is entity.field1 ?
    – jakubiszon
    Oct 2 '20 at 9:23
  • 1
    As far as I understand it, you either need to detect the type of data before casting or you need to "try cast". I would recoomend reducing the scope of the question. Take a look at dba.stackexchange.com/questions/203934/…
    – jakubiszon
    Oct 2 '20 at 10:20
  • 1
    Is the ORM wrapper incapable of using a stored procedure (eg. called contains) in its queries? If not, check out how to use function overloading, which could solve your problem. Oct 2 '20 at 13:03

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 $$
      result boolean;
      CASE jsonb_typeof(haystack)
        WHEN 'array', 'object' THEN
          result := haystack ? needle;
          result := haystack LIKE '%' || needle || '%';
      END CASE;
      RETURN result;
  $$ LANGUAGE plpgsql;

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

CREATE OR REPLACE FUNCTION contains(haystack text[], needle text) RETURNS boolean AS $$
    RETURN needle = ANY(haystack);
$$ 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.