Skip to content

The Postgres development platform. Supabase gives you a dedicated Postgres database to build your web, mobile, and AI applications.

1.6 KB
Raw Blame
import { ident, joinSqlFragments, literal, safeSql, type SafeSqlFragment } from './pg-format'

export const coalesceRowsToArray = (
  source: string,
  filter: SafeSqlFragment,
  // Optional ORDER BY (a column reference of `source`) applied INSIDE array_agg
  // to make the emitted array deterministic. Omit it to keep the historical
  // (plan-order) rendering byte-for-byte identical for existing callers.
  orderBy?: SafeSqlFragment
) => {
  const orderClause = orderBy ? safeSql` ORDER BY ${orderBy}` : safeSql``
  return safeSql`
COALESCE(
  (
    SELECT
      array_agg(row_to_json(${ident(source)})${orderClause}) FILTER (WHERE ${filter})
    FROM
      ${ident(source)}
  ),
  '{}'
) AS ${ident(source)}`
}

export function filterByList(include?: string[], exclude?: string[], defaultExclude?: string[]) {
  if (defaultExclude) {
    exclude = defaultExclude.concat(exclude ?? [])
  }
  if (include?.length) {
    return safeSql`IN (${joinSqlFragments(include.map(literal), ',')})`
  } else if (exclude?.length) {
    return safeSql`NOT IN (${joinSqlFragments(exclude.map(literal), ',')})`
  }
  return safeSql``
}

export function exceptionIdentifierNotFound(entityName: string, whereClause: string) {
  return safeSql`raise exception 'Cannot find ${ident(entityName)} with: %', ${literal(whereClause)};`
}

// Escape LIKE/ILIKE wildcards so user input is matched literally. ILIKE's
// default escape character is `\`, so we escape `\`, `%`, and `_` and let the
// pattern carry its own surrounding `%`s as wildcards.
export const escapeIlikeLiteral = (value: string) => value.replace(/([\\%_])/g, '\\$1')