problem with postgres substring(bytea, n, m)

[Rob Sargent]

I failed thrice at sending this from the google forum.  That editor is beyond painful.

Using jOOQ 3.14, java 15 and Postgres (12)

Postgres overloads substring to work on bytea data, so against

    Column    | Type  | Collation | Nullable | Default
    --------------+-------+-----------+----------+---------
     id           | uuid  |           | not null |
     person_id    | uuid  |           | not null |
     markerset_id | uuid  |           | not null |
     calls        | bytea |           | not null |

I can

Using jOOQ 3.14, java 15 and Postgres (12)

Postgres overloads substring to work on bytea data, so against

    Column    | Type  | Collation | Nullable | Default
    --------------+-------+-----------+----------+---------
     id           | uuid  |           | not null |
     person_id    | uuid  |           | not null |
     markerset_id | uuid  |           | not null |
     calls        | bytea |           | not null |

I can

    select substring(calls,1, 10) from viv.genotype where calls is not null limit 3;
       substring       
    ------------------------
     \x12111222212112121121
     \x22112211111122221111
     \x22112211111122221111
    (3 rows)
    *** NOTE: 20 characters

But my naive attempt in code

    Result<?> peddata = dsl
      .select(PEOPLE.NAME.as("peoplename"),
          ego.NAME,
              (coalesce(ma.NAME,"0")).as("maname"),
              (coalesce(pa.NAME, "0")).as("paname"),
          choose(ego.GENDER)
          .when("m", 1)
          .when("f", 2)
          .otherwise(0).as("regender"),
          substring(GENOTYPE.CALLS, startIndex, markerCount).as("gtdata"))
      .from(PEOPLE)
      .join(PROBANDSET).on(PEOPLE.ID.equal(PROBANDSET.PEOPLE_ID))
      .join(SEGMENT).on(PROBANDSET.ID.equal(SEGMENT.PROBANDSET_ID))
      .join(PEOPLE_MEMBER).on(PEOPLE.ID.equal(PEOPLE_MEMBER.PEOPLE_ID))
      .join(ego).on(PEOPLE_MEMBER.PERSON_ID.equal(ego.ID))
      .join(ascendTrim(PROBANDSET.PROBANDS)).on(ego.ID.equal(ASCEND_TRIM.ID))
      .leftJoin(pa).on(ego.PA.equal(pa.ID))
      .leftJoin(ma).on(ego.MA.equal(ma.ID))
      .leftJoin(GENOTYPE).on(ego.ID.equal(GENOTYPE.PERSON_ID).and(GENOTYPE.MARKERSET_ID.equal(SEGMENT.MARKERSET_ID)))
      .where(SEGMENT.ID.equal(segmentId))
      .fetch();
generates a compilation error

    error: no suitable method found for substring(TableField<GenotypeRecord,byte[]>,int,int)
          substring(GENOTYPE.CALLS, startIndex, markerCount).as("gtdata"))
          ^
    method DSL.substring(Field<String>,Field<? extends Number>,Field<? extends Number>) is not applicable
      (argument mismatch; TableField<GenotypeRecord,byte[]> cannot be converted to Field<String>)
    method DSL.substring(Field<String>,int,int) is not applicable
      (argument mismatch; TableField<GenotypeRecord,byte[]> cannot be converted to Field<String>)

I don't see any reference to this substring(bytea) in the docs.  Short
of straight sql am I left with the jOOQ equivalent of

   *** NOTE: double the length of the requested string

    select substring(calls,1, 20) from viv.genotype where calls is not null limit 3;
       substring       
    ------------------------
     \x12111222212112121121
     \x22112211111122221111
     \x22112211111122221111
    (3 rows)
    *** NOTE: 20 characters

But my naive attempt in code

    Result<?> peddata = dsl
      .select(PEOPLE.NAME.as("peoplename"),
          ego.NAME,
              (coalesce(ma.NAME,"0")).as("maname"),
              (coalesce(pa.NAME, "0")).as("paname"),
          choose(ego.GENDER)
          .when("m", 1)
          .when("f", 2)
          .otherwise(0).as("regender"),
          substring(GENOTYPE.CALLS, startIndex, markerCount).as("gtdata"))
      .from(PEOPLE)
      .join(PROBANDSET).on(PEOPLE.ID.equal(PROBANDSET.PEOPLE_ID))
      .join(SEGMENT).on(PROBANDSET.ID.equal(SEGMENT.PROBANDSET_ID))
      .join(PEOPLE_MEMBER).on(PEOPLE.ID.equal(PEOPLE_MEMBER.PEOPLE_ID))
      .join(ego).on(PEOPLE_MEMBER.PERSON_ID.equal(ego.ID))
      .join(ascendTrim(PROBANDSET.PROBANDS)).on(ego.ID.equal(ASCEND_TRIM.ID))
      .leftJoin(pa).on(ego.PA.equal(pa.ID))
      .leftJoin(ma).on(ego.MA.equal(ma.ID))
      .leftJoin(GENOTYPE).on(ego.ID.equal(GENOTYPE.PERSON_ID).and(GENOTYPE.MARKERSET_ID.equal(SEGMENT.MARKERSET_ID)))
      .where(SEGMENT.ID.equal(segmentId))
      .fetch();
generates a compilation error

    error: no suitable method found for substring(TableField<GenotypeRecord,byte[]>,int,int)
          substring(GENOTYPE.CALLS, startIndex, markerCount).as("gtdata"))
          ^
    method DSL.substring(Field<String>,Field<? extends Number>,Field<? extends Number>) is not applicable
      (argument mismatch; TableField<GenotypeRecord,byte[]> cannot be converted to Field<String>)
    method DSL.substring(Field<String>,int,int) is not applicable
      (argument mismatch; TableField<GenotypeRecord,byte[]> cannot be converted to Field<String>)

I don't see any reference to this substring(bytea) in the docs.  Short
of straight sql am I left with the jOOQ equivalent of

   *** NOTE: double the length of the requested string
    select substring(calls::text,1, 20) from viv.genotype where calls is not null limit 3;
      substring      
    ----------------------
     \x121112222121121211
     \x221122111111222211
     \x221122111111222211
    (3 rows)

[Lukas Eder]

On Fri, Dec 4, 2020 at 1:57 AM Rob Sargent <robjs...@gmail.com> wrote:

I failed thrice at sending this from the google forum.  That editor is beyond painful.

You can send mails to the list with any email client, to jooq...@googlegroups.com

 

Using jOOQ 3.14, java 15 and Postgres (12)

Postgres overloads substring to work on bytea data, so against [...]

We don't support that function. There are probably a lot of bytea overloads in PostgreSQL that are a bit weird but can be seen to make sense, like this substring() function. A bytea isn't really a string, but I can see how some PostgreSQL developer thought this was a good thing to do... :)

You can generate the pg_catalog schema and access this function as an ordinary stored function. Or, you can use plain SQL templating, as always, to add lacking functionality to jOOQ:

https://www.jooq.org/doc/latest/manual/sql-building/plain-sql-templating/

I hope this helps

Lukas 

[Rob Sargent]

I suspected that might be the case and understand perfectly.  I’ll likely make my own function which calls substring. 

Thanks. 

PS I did send direct email. I assume your ellipsis trimmed off the detail

On Dec 4, 2020, at 1:12 AM, Lukas Eder <lukas...@gmail.com> wrote:



--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO6tKP5%2BnY80eru%2Bk7JW3KpiaDs7dTSZHqa7Q-Lcih4_%3Dw%40mail.gmail.com.