items_by_ids.sql

CREATE INDEX "items_alternate_ids_idx" ON "items" USING GIN ("alternate_ids");

CREATE OR REPLACE FUNCTION items_by_ids(ids text[], is_published boolean DEFAULT NULL)
  RETURNS SETOF items
AS $$
SELECT items.*
FROM unnest($1) WITH ORDINALITY t(value, ord), items
WHERE (
    items.core_id = t.value
      OR items.id = CASE WHEN isnumeric(t.value) THEN t.value::integer ELSE null END
      OR items.alternate_ids @> ARRAY[t.value]
   )
  AND ($2 IS NULL OR items.is_published = $2)
LIMIT 500
$$
LANGUAGE sql
STABLE;
COMMENT ON FUNCTION items_by_ids(text[], boolean) IS E'@simpleCollections both';