use super::{to_f64, value_to_string, Result, SQLError, TemporalValue, Value};
pub(super) fn typeof_value(v: &Value) -> String {
match v {
Value::Null => "null".into(),
Value::Void => "void".into(),
Value::Bool(_) => "boolean".into(),
Value::Int(_) => "integer".into(),
Value::Float(_) => "double precision".into(),
Value::Decimal(_) => "numeric".into(),
Value::Str(_) => "text".into(),
Value::FixedChar(_) => "character".into(),
Value::Bytes(_) => "bytea".into(),
Value::Temporal(value) => match value {
TemporalValue::Date { .. } => "date".into(),
TemporalValue::Time { .. } => "time without time zone".into(),
TemporalValue::TimeTz { .. } => "time with time zone".into(),
TemporalValue::Timestamp { .. } => "timestamp without time zone".into(),
TemporalValue::TimestampTz { .. } => "timestamp with time zone".into(),
TemporalValue::Interval { .. } => "interval".into(),
},
Value::Json(_) => "json".into(),
Value::JsonB(_) => "jsonb".into(),
Value::Array(_) => "array".into(),
Value::List(_) => "array".into(),
Value::Row(_) | Value::Record(_) => "record".into(),
Value::Map(_) => "jsonb".into(),
}
}
pub(super) fn point_xy(v: &Value) -> Result<(f64, f64)> {
match v {
Value::List(items) if items.len() == 2 => Ok((to_f64(&items[0])?, to_f64(&items[1])?)),
Value::Str(s) | Value::FixedChar(s) => {
let cleaned = s.trim_matches(|c: char| c == '(' || c == ')' || c == '[' || c == ']');
let parts: Vec<&str> = cleaned.split(',').map(str::trim).collect();
if parts.len() != 2 {
return Err(SQLError::TypeMismatch(format!("point: cannot parse {s:?}")));
}
let x: f64 = parts[0]
.parse()
.map_err(|e| SQLError::TypeMismatch(format!("point.x: {e}")))?;
let y: f64 = parts[1]
.parse()
.map_err(|e| SQLError::TypeMismatch(format!("point.y: {e}")))?;
Ok((x, y))
}
other => Err(SQLError::TypeMismatch(format!(
"point: not coercible {other:?}"
))),
}
}
pub struct CompiledLikePattern {
case_insensitive: bool,
pattern_chars: Vec<LikePatternToken<char>>,
pattern_ascii: Option<Vec<LikePatternToken<u8>>>,
}
#[derive(Clone, Copy, PartialEq, Eq)]
enum LikePatternToken<T> {
Literal(T),
AnyOne,
AnyMany,
DanglingEscape,
}
impl CompiledLikePattern {
#[must_use]
pub fn new(pattern: &str, case_insensitive: bool) -> Self {
Self::with_escape(pattern, case_insensitive, None)
.expect("the default LIKE escape is exactly one character")
}
#[must_use]
pub fn from_value(pattern: &Value, case_insensitive: bool) -> Self {
Self::new(&value_to_string(pattern), case_insensitive)
}
pub fn with_escape(
pattern: &str,
case_insensitive: bool,
escape: Option<&str>,
) -> Result<Self> {
let escape = like_escape_character(escape)?;
let pattern_chars = compile_like_pattern(pattern, case_insensitive, escape);
let pattern_ascii = pattern_chars
.iter()
.map(|token| match token {
LikePatternToken::Literal(character) if character.is_ascii() => {
Some(LikePatternToken::Literal(*character as u8))
}
LikePatternToken::Literal(_) => None,
LikePatternToken::AnyOne => Some(LikePatternToken::AnyOne),
LikePatternToken::AnyMany => Some(LikePatternToken::AnyMany),
LikePatternToken::DanglingEscape => Some(LikePatternToken::DanglingEscape),
})
.collect::<Option<Vec<_>>>();
Ok(Self {
case_insensitive,
pattern_chars,
pattern_ascii,
})
}
#[must_use]
pub fn is_match(&self, haystack: &str) -> bool {
self.try_is_match(haystack).unwrap_or(false)
}
pub fn try_is_match(&self, haystack: &str) -> Result<bool> {
if self.case_insensitive {
let normalized = haystack.to_lowercase();
if let Some(pattern) = self
.pattern_ascii
.as_deref()
.filter(|_| normalized.is_ascii())
{
return wildcard_match(normalized.as_bytes(), pattern);
}
let haystack = normalized.chars().collect::<Vec<_>>();
return wildcard_match(&haystack, &self.pattern_chars);
}
if let Some(pattern) = self
.pattern_ascii
.as_deref()
.filter(|_| haystack.is_ascii())
{
return wildcard_match(haystack.as_bytes(), pattern);
}
let haystack = haystack.chars().collect::<Vec<_>>();
wildcard_match(&haystack, &self.pattern_chars)
}
#[must_use]
pub fn matches_value(&self, haystack: &Value) -> bool {
self.try_matches_value(haystack).unwrap_or(false)
}
pub fn try_matches_value(&self, haystack: &Value) -> Result<bool> {
match haystack {
Value::Str(text) => self.try_is_match(text),
Value::FixedChar(text) => self.try_is_match(text.trim_end_matches(' ')),
Value::Null => self.try_is_match(""),
other => self.try_is_match(&value_to_string(other)),
}
}
}
fn like_escape_character(escape: Option<&str>) -> Result<Option<char>> {
let Some(escape) = escape else {
return Ok(Some('\\'));
};
let mut characters = escape.chars();
let first = characters.next();
if characters.next().is_some() {
return Err(SQLError::Routine {
sqlstate: "22025".into(),
message: "invalid escape string".into(),
});
}
Ok(first)
}
fn compile_like_pattern(
pattern: &str,
case_insensitive: bool,
escape: Option<char>,
) -> Vec<LikePatternToken<char>> {
let mut output = Vec::with_capacity(pattern.chars().count());
let mut characters = pattern.chars();
while let Some(character) = characters.next() {
if escape == Some(character) {
let Some(literal) = characters.next() else {
output.push(LikePatternToken::DanglingEscape);
break;
};
push_like_literal(&mut output, literal, case_insensitive);
continue;
}
match character {
'%' => output.push(LikePatternToken::AnyMany),
'_' => output.push(LikePatternToken::AnyOne),
literal => push_like_literal(&mut output, literal, case_insensitive),
}
}
output
}
fn push_like_literal(
output: &mut Vec<LikePatternToken<char>>,
literal: char,
case_insensitive: bool,
) {
if case_insensitive {
output.extend(literal.to_lowercase().map(LikePatternToken::Literal));
} else {
output.push(LikePatternToken::Literal(literal));
}
}
fn wildcard_match<T: Copy + Eq>(haystack: &[T], pattern: &[LikePatternToken<T>]) -> Result<bool> {
let mut haystack_index = 0;
let mut pattern_index = 0;
let mut star: Option<(usize, usize)> = None;
while haystack_index < haystack.len() {
match pattern.get(pattern_index) {
Some(LikePatternToken::Literal(literal)) if *literal == haystack[haystack_index] => {
haystack_index += 1;
pattern_index += 1;
}
Some(LikePatternToken::AnyOne) => {
haystack_index += 1;
pattern_index += 1;
}
Some(LikePatternToken::AnyMany) => {
star = Some((pattern_index, haystack_index));
pattern_index += 1;
}
Some(LikePatternToken::DanglingEscape) => {
return Err(SQLError::Routine {
sqlstate: "22025".into(),
message: "LIKE pattern must not end with escape character".into(),
});
}
_ => {
if let Some((star_pattern, star_haystack)) = star {
pattern_index = star_pattern + 1;
haystack_index = star_haystack + 1;
star = Some((star_pattern, star_haystack + 1));
} else {
return Ok(false);
}
}
}
}
while matches!(pattern.get(pattern_index), Some(LikePatternToken::AnyMany)) {
pattern_index += 1;
}
Ok(pattern_index == pattern.len())
}
pub(super) fn trim_chars(args: &[Value], start: bool, end: bool) -> Result<Value> {
if args.is_empty() || args.len() > 2 {
return Err(SQLError::TypeMismatch("trim takes 1-2 args".into()));
}
if args.iter().any(|arg| matches!(arg, Value::Null)) {
return Ok(Value::Null);
}
let s = value_to_string(&args[0]);
let out = match args.get(1) {
None => match (start, end) {
(true, true) => s.trim(),
(true, false) => s.trim_start(),
(false, true) => s.trim_end(),
(false, false) => s.as_str(),
}
.to_string(),
Some(set) => {
let set: Vec<char> = value_to_string(set).chars().collect();
let matches_set = |c: char| set.contains(&c);
let mut out = s.as_str();
if start {
out = out.trim_start_matches(matches_set);
}
if end {
out = out.trim_end_matches(matches_set);
}
out.to_string()
}
};
Ok(Value::Str(out))
}
pub(super) fn compile_pg_regex(
pattern: &str,
flags: &str,
global_allowed: bool,
) -> Result<regex::Regex> {
#[derive(Clone, Copy)]
enum Syntax {
Advanced,
Basic,
Quoted,
}
let mut case_insensitive = false;
let mut multi_line = false;
let mut dot_matches_new_line = true;
let mut expanded = false;
let mut syntax = Syntax::Advanced;
for flag in flags.chars() {
match flag {
'g' if global_allowed => {}
'b' | 'e' => syntax = Syntax::Basic,
'c' => case_insensitive = false,
'i' => case_insensitive = true,
'm' | 'n' => {
multi_line = true;
dot_matches_new_line = false;
}
'p' => {
multi_line = false;
dot_matches_new_line = false;
}
'q' => syntax = Syntax::Quoted,
's' => {
multi_line = false;
dot_matches_new_line = true;
}
't' => expanded = false,
'w' => {
multi_line = true;
dot_matches_new_line = true;
}
'x' => expanded = true,
invalid => {
return Err(SQLError::Routine {
sqlstate: "22023".into(),
message: format!("invalid regular expression option: \"{invalid}\""),
});
}
}
}
if matches!(syntax, Syntax::Quoted) && (expanded || multi_line || !dot_matches_new_line) {
return Err(SQLError::Routine {
sqlstate: "2201B".into(),
message: "invalid regular expression: invalid argument to regex function".into(),
});
}
let pattern = if expanded {
expand_postgres_regex(pattern)
} else {
pattern.to_string()
};
let pattern = match syntax {
Syntax::Advanced => pattern,
Syntax::Basic => postgres_basic_regex(&pattern),
Syntax::Quoted => regex::escape(&pattern),
};
let pattern = postgres_character_class_regex(&pattern, !dot_matches_new_line);
let mut builder = regex::RegexBuilder::new(&pattern);
builder
.case_insensitive(case_insensitive)
.multi_line(multi_line)
.dot_matches_new_line(dot_matches_new_line);
builder.build().map_err(|error| SQLError::Routine {
sqlstate: "2201B".into(),
message: format!("invalid regular expression: {error}"),
})
}
fn postgres_character_class_regex(pattern: &str, exclude_newline: bool) -> String {
let characters = pattern.chars().collect::<Vec<_>>();
let mut output = String::with_capacity(pattern.len());
let mut position = 0usize;
let mut in_bracket = false;
let mut bracket_can_close = false;
while let Some(&character) = characters.get(position) {
position += 1;
if character == '\\' {
output.push(character);
if let Some(&escaped) = characters.get(position) {
position += 1;
output.push(escaped);
if in_bracket {
bracket_can_close = true;
}
}
continue;
}
if !in_bracket {
output.push(character);
if character == '[' {
in_bracket = true;
bracket_can_close = false;
if characters.get(position) == Some(&'^') {
position += 1;
output.push('^');
if characters.get(position) == Some(&']') {
position += 1;
output.push(']');
bracket_can_close = true;
}
if exclude_newline {
output.push_str("\\n");
if characters.get(position) == Some(&'-') {
position += 1;
output.push_str("\\-");
bracket_can_close = true;
}
}
}
}
continue;
}
if character == '[' && matches!(characters.get(position), Some('.' | ':' | '=')) {
let delimiter = characters[position];
output.push(character);
output.push(delimiter);
position += 1;
while let Some(&nested) = characters.get(position) {
position += 1;
output.push(nested);
if nested == delimiter && characters.get(position) == Some(&']') {
output.push(']');
position += 1;
break;
}
}
bracket_can_close = true;
continue;
}
if character == '[' {
output.push_str("\\[");
bracket_can_close = true;
continue;
}
output.push(character);
if character == ']' && bracket_can_close {
in_bracket = false;
} else if character != '^' || bracket_can_close {
bracket_can_close = true;
}
}
output
}
fn expand_postgres_regex(pattern: &str) -> String {
let mut output = String::with_capacity(pattern.len());
let mut characters = pattern.chars().peekable();
let mut in_bracket = false;
let mut bracket_can_close = false;
while let Some(character) = characters.next() {
if character == '\\' {
output.push(character);
if let Some(escaped) = characters.next() {
output.push(escaped);
if in_bracket {
bracket_can_close = true;
}
}
continue;
}
if in_bracket {
if character == '[' {
if let Some(delimiter @ ('.' | ':' | '=')) = characters.peek().copied() {
output.push(character);
output.push(delimiter);
characters.next();
while let Some(nested) = characters.next() {
output.push(nested);
if nested == delimiter && characters.peek() == Some(&']') {
output.push(']');
characters.next();
break;
}
}
bracket_can_close = true;
continue;
}
}
output.push(character);
if character == ']' && bracket_can_close {
in_bracket = false;
} else if character != '^' || bracket_can_close {
bracket_can_close = true;
}
continue;
}
match character {
'[' => {
in_bracket = true;
bracket_can_close = false;
output.push(character);
}
'#' => {
for comment in characters.by_ref() {
if comment == '\n' {
break;
}
}
}
whitespace if postgres_expanded_regex_whitespace(whitespace) => {}
other => output.push(other),
}
}
output
}
fn postgres_expanded_regex_whitespace(character: char) -> bool {
matches!(
character,
'\u{0009}'..='\u{000D}'
| '\u{0020}'
| '\u{1680}'
| '\u{2000}'..='\u{2006}'
| '\u{2008}'..='\u{200A}'
| '\u{2028}'..='\u{2029}'
| '\u{205F}'
| '\u{3000}'
)
}
fn postgres_basic_regex(pattern: &str) -> String {
let mut output = String::with_capacity(pattern.len());
let characters = pattern.chars().collect::<Vec<_>>();
let mut position = 0usize;
let mut in_bracket = false;
let mut bracket_can_close = false;
let mut at_subexpression_start = true;
while let Some(&character) = characters.get(position) {
position += 1;
if in_bracket {
if character == '\\' {
output.push_str(r"\\");
bracket_can_close = true;
continue;
}
output.push(character);
if character == ']' && bracket_can_close {
in_bracket = false;
at_subexpression_start = false;
} else if character != '^' || bracket_can_close {
bracket_can_close = true;
}
continue;
}
if character == '\\' {
match characters.get(position).copied() {
Some('(') => {
position += 1;
output.push('(');
at_subexpression_start = true;
}
Some(')') => {
position += 1;
output.push(')');
at_subexpression_start = false;
}
Some(bound @ ('{' | '}')) => {
position += 1;
output.push(bound);
}
Some(escaped) if escaped.is_ascii_alphabetic() => {
position += 1;
output.push(escaped);
at_subexpression_start = false;
}
Some(escaped) => {
position += 1;
output.push('\\');
output.push(escaped);
at_subexpression_start = false;
}
None => output.push('\\'),
}
continue;
}
match character {
'[' => {
in_bracket = true;
bracket_can_close = false;
output.push(character);
}
'^' if at_subexpression_start => output.push(character),
'^' => {
output.push_str(r"\^");
at_subexpression_start = false;
}
'$' => {
let closes_subexpression = matches!(
(characters.get(position), characters.get(position + 1)),
(Some('\\'), Some(')'))
);
if position == characters.len() || closes_subexpression {
output.push(character);
} else {
output.push_str(r"\$");
at_subexpression_start = false;
}
}
'*' if at_subexpression_start => {
output.push_str(r"\*");
at_subexpression_start = false;
}
literal @ ('+' | '?' | '(' | ')' | '{' | '}' | '|') => {
output.push('\\');
output.push(literal);
at_subexpression_start = false;
}
other => {
output.push(other);
at_subexpression_start = false;
}
}
}
output
}
#[expect(
clippy::too_many_lines,
reason = "builtin dispatch preserves arity, NULL, and error precedence"
)]
pub(super) fn is_quoted_keyword(word: &str) -> bool {
const KEYWORDS: &[&str] = &[
"all",
"analyse",
"analyze",
"and",
"any",
"array",
"as",
"asc",
"asymmetric",
"authorization",
"between",
"bigint",
"binary",
"bit",
"boolean",
"both",
"case",
"cast",
"char",
"character",
"check",
"coalesce",
"collate",
"collation",
"column",
"concurrently",
"constraint",
"create",
"cross",
"current_catalog",
"current_date",
"current_role",
"current_schema",
"current_time",
"current_timestamp",
"current_user",
"dec",
"decimal",
"default",
"deferrable",
"desc",
"distinct",
"do",
"else",
"end",
"except",
"exists",
"extract",
"false",
"fetch",
"float",
"for",
"foreign",
"freeze",
"from",
"full",
"grant",
"greatest",
"group",
"grouping",
"having",
"ilike",
"in",
"initially",
"inner",
"inout",
"int",
"integer",
"intersect",
"interval",
"into",
"is",
"isnull",
"join",
"json",
"json_array",
"json_arrayagg",
"json_exists",
"json_object",
"json_objectagg",
"json_query",
"json_scalar",
"json_serialize",
"json_table",
"json_value",
"lateral",
"leading",
"least",
"left",
"like",
"limit",
"localtime",
"localtimestamp",
"merge_action",
"national",
"natural",
"nchar",
"none",
"normalize",
"not",
"notnull",
"null",
"nullif",
"numeric",
"offset",
"on",
"only",
"or",
"order",
"out",
"outer",
"overlaps",
"overlay",
"placing",
"position",
"precision",
"primary",
"real",
"references",
"returning",
"right",
"row",
"select",
"session_user",
"setof",
"similar",
"smallint",
"some",
"substring",
"symmetric",
"system_user",
"table",
"tablesample",
"then",
"time",
"timestamp",
"to",
"trailing",
"treat",
"trim",
"true",
"union",
"unique",
"user",
"using",
"values",
"varchar",
"variadic",
"verbose",
"when",
"where",
"window",
"with",
"xmlattributes",
"xmlconcat",
"xmlelement",
"xmlexists",
"xmlforest",
"xmlnamespaces",
"xmlparse",
"xmlpi",
"xmlroot",
"xmlserialize",
"xmltable",
];
KEYWORDS.binary_search(&word).is_ok()
}
pub fn quote_ident(ident: &str) -> String {
let safe = !ident.is_empty()
&& ident.chars().enumerate().all(|(i, c)| {
c.is_ascii_lowercase() || c == '_' || (i > 0 && (c.is_ascii_digit() || c == '$'))
});
if safe && !is_quoted_keyword(ident) {
return ident.to_string();
}
format!("\"{}\"", ident.replace('"', "\"\""))
}
pub(super) fn quote_literal(text: &str) -> String {
let escaped = text.replace('\'', "''");
if escaped.contains('\\') {
format!("E'{}'", escaped.replace('\\', "\\\\"))
} else {
format!("'{escaped}'")
}
}
pub(super) fn similar_to_regex(pattern: &str, escape: Option<&str>) -> Result<String> {
let escape = like_escape_character(escape)?;
let mut out = String::with_capacity(pattern.len() + 8);
out.push_str("^(?:");
let mut after_escape = false;
let mut quote_count = 0;
let mut bracket_depth = 0usize;
let mut bracket_position = 0usize;
for character in pattern.chars() {
if after_escape {
if character == '"' && bracket_depth == 0 {
match quote_count {
0 => out.push_str("){1,1}?("),
1 => out.push_str("){1,1}(?:"),
_ => {
return Err(SQLError::Routine {
sqlstate: "2200C".into(),
message: "SQL regular expression may not contain more than two escape-double-quote separators".into(),
});
}
}
quote_count += 1;
} else {
push_similar_escaped(&mut out, character);
bracket_position = 3;
}
after_escape = false;
continue;
}
if escape == Some(character) {
after_escape = true;
continue;
}
if bracket_depth > 0 {
if character == '\\' && escape != Some('\\') {
out.push('\\');
}
out.push(character);
if character == ']' && bracket_position > 2 {
bracket_depth -= 1;
} else if character == '[' {
bracket_depth += 1;
bracket_position = 3;
} else if character == '^' {
bracket_position += 1;
} else {
bracket_position = 3;
}
continue;
}
match character {
'%' => out.push_str(".*"),
'_' => out.push('.'),
'[' => {
bracket_depth = 1;
bracket_position = 1;
out.push('[');
}
'(' => out.push_str("(?:"),
'\\' | '.' | '^' | '$' => {
out.push('\\');
out.push(character);
}
other => out.push(other),
}
}
out.push_str(")$");
Ok(out)
}
fn push_similar_escaped(output: &mut String, character: char) {
match character {
'b' => {
output.push_str(r"\x08");
return;
}
'B' => {
output.push_str(r"\\");
return;
}
_ => {}
}
if character.is_ascii_alphanumeric()
|| matches!(
character,
'\\' | '.'
| '^'
| '$'
| '|'
| '?'
| '*'
| '+'
| '('
| ')'
| '{'
| '}'
| '['
| ']'
| '-'
)
{
output.push('\\');
}
output.push(character);
}
#[cfg(test)]
mod regex_tests;