postgresql - 在 Postgresql 中使用 ILIKE 的动态 RETURN EXECUTE QUERY

Question

下面函数中的搜索过滤器似乎不起作用。如果我不提供 search 参数，它就可以工作，否则我得不到任何记录集。我假设我把单引号和 ILIKE 弄得一团糟，但不确定如何正确地重写它。有什么建议吗？

CREATE OR REPLACE FUNCTION get_operator_basic_by_operator(
  _operator_id      UUID DEFAULT NULL,
  _search           TEXT DEFAULT NULL,
  _page_number      INTEGER DEFAULT 1,
  _page_size        INTEGER DEFAULT 10,
  _sort_col         TEXT DEFAULT 'username',
  _sort_dir         TEXT DEFAULT 'asc',
  _include_deleted  BOOLEAN DEFAULT FALSE
)
RETURNS TABLE (
  id                  UUID,
  party_id            UUID,
  party_name          TEXT,
  username            VARCHAR(32),
  profile_picture_uri VARCHAR(512),
  first_name          VARCHAR(64),
  last_name           VARCHAR(64),
  street              VARCHAR(128),
  specifier           VARCHAR(128),
  city                VARCHAR(64),
  state               VARCHAR(2),
  zipcode             VARCHAR(9),
  primary_email       CITEXT,
  primary_phone       VARCHAR(10),
  secondary_email     CITEXT,
  secondary_phone     VARCHAR(10),
  last_login          TIMESTAMP WITH TIME ZONE,
  created             TIMESTAMP WITH TIME ZONE,
  deleted             TIMESTAMP WITH TIME ZONE
)
AS $$
DECLARE
  _offset BIGINT;
BEGIN

  IF (_page_number < 1 OR _page_number IS NULL) THEN
    RAISE EXCEPTION '_page_number cannot be null or less than 1.';
  END IF;

  IF (_page_size < 1 OR _page_size IS NULL) THEN
    RAISE EXCEPTION '_page_size cannot be null or less than 1.';
  END IF;

  IF (_sort_dir <> 'asc' AND _sort_dir <> 'desc') THEN
    RAISE EXCEPTION '_sort_dir must be "asc" or "desc".';
  END IF;

  _offset := (_page_size * (_page_number-1)); 

  RETURN QUERY EXECUTE '
  SELECT
    o.id,
    p.id,
    p.party_name,
    o.username,
    o.profile_picture_uri,
    o.first_name,
    o.last_name,
    o.street,
    o.specifier,
    o.city,
    o.state,
    o.zipcode,
    o.primary_email,
    o.primary_phone,
    o.secondary_email,
    o.secondary_phone,
    o.last_login,
    o.created,
    o.deleted
  FROM
    operator o
  LEFT JOIN
    party p ON o.party_id = p.id
  WHERE ( -- include all or only those active, based on _include_deleted
    $1 OR o.deleted > statement_timestamp()
  )
  AND o.party_id IN ( -- limit to operators in same party
    SELECT oi.party_id FROM operator oi WHERE oi.id = $2
  )
  AND ( -- use optional search filter
      $3 IS NULL 
    OR
      o.username ILIKE ''%$3%''
    OR
      o.first_name ILIKE ''%$3%''
    OR
      o.last_name ILIKE ''%$3%''
    OR
      o.primary_email ILIKE ''%$3%''
    )
  ORDER BY ' || quote_ident(_sort_col) || ' ' || _sort_dir || '
  LIMIT
    $4
  OFFSET
    $5'
  USING _include_deleted, _operator_id, _search, _page_size, _offset;
END;

Answer 1

参数是作为带引号的文本插入的，所以你应该这样使用它:

...
o.username ILIKE concat(''%'', $3, ''%'')
...

我个人会使用 format() 和美元引号。