Home:ALL Converter>Check if array param is not null in SQLDelight (PostgreSQL)

Check if array param is not null in SQLDelight (PostgreSQL)

Ask Time:2021-08-29T22:39:15         Author:P. Savrov

Json Formatter

I've tested that select with PostgreSQL and it seems to work fine - return a list of items with resourceId mentioned in :resourceIdList (if any). If :resourceIdList is null - then return all of them.

select b.resourceid,
       b.datecreatedutc as basecreated,
       b.datedeletedutc as basedeleted
from productbasetable b
where b.datedeletedutc is null
  and case
          when :resourceIdList is not null
              then b.resourceid in :resourceIdList
          else true
    end;

The problem occurs when I'm trying to use it in SqlDelight: the generated code has a problem with when :resourceIdList is not null (encode fun expects a T type, not a List) enter image description here

Is there another way to check if :resourceIdList is null/empty before the IN statement?

Author:P. Savrov,eproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/68973971/check-if-array-param-is-not-null-in-sqldelight-postgresql
yy