Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit1e7ba76

Browse files
committed
Indexes for LIKE and ~, !~ operations.
1 parent0308f91 commit1e7ba76

File tree

1 file changed

+174
-3
lines changed

1 file changed

+174
-3
lines changed

‎src/backend/parser/gram.y

Lines changed: 174 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
*
1111
*
1212
* IDENTIFICATION
13-
* $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 1.60 1997/10/30 16:39:27 thomas Exp $
13+
* $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 1.61 1997/10/31 00:50:39 momjian Exp $
1414
*
1515
* HISTORY
1616
* AUTHORDATEMAJOR EVENT
@@ -64,6 +64,7 @@ static Node *makeA_Expr(int oper, char *opname, Node *lexpr, Node *rexpr);
6464
static List *makeConstantList( A_Const *node);
6565
static char *FlattenStringList(List *list);
6666
static char *fmtId(char *rawid);
67+
static Node *makeIndexable(char *opname, Node *lexpr, Node *rexpr);
6768

6869
/* old versions of flex define this as a macro */
6970
#if defined(yywrap)
@@ -2755,9 +2756,9 @@ a_expr: attr opt_indirection
27552756
| '(' a_expr_or_null ')'
27562757
{$$ = $2; }
27572758
| a_expr Op a_expr
2758-
{$$ =makeA_Expr(OP,$2,$1,$3);}
2759+
{$$ =makeIndexable($2,$1,$3);}
27592760
| a_expr LIKE a_expr
2760-
{$$ =makeA_Expr(OP,"~~", $1, $3); }
2761+
{$$ =makeIndexable("~~", $1, $3); }
27612762
| a_expr NOT LIKE a_expr
27622763
{$$ = makeA_Expr(OP, "!~~", $1, $4); }
27632764
| Op a_expr
@@ -3527,6 +3528,176 @@ static Node *makeA_Expr(int oper, char *opname, Node *lexpr, Node *rexpr)
35273528
return (Node *)a;
35283529
}
35293530

3531+
static Node *makeIndexable(char *opname, Node *lexpr, Node *rexpr)
3532+
{
3533+
Node *result = NULL;
3534+
3535+
/* we do this so indexes can be used */
3536+
if (strcmp(opname,"~") == 0)
3537+
{
3538+
if (nodeTag(rexpr) == T_A_Const &&
3539+
((A_Const *)rexpr)->val.type == T_String &&
3540+
((A_Const *)rexpr)->val.val.str[0] == '^')
3541+
{
3542+
A_Const *n = (A_Const *)rexpr;
3543+
char *match_least = palloc(strlen(n->val.val.str)+2);
3544+
char *match_most = palloc(strlen(n->val.val.str)+2);
3545+
int pos, match_pos=0;
3546+
3547+
/* skip leading ^ */
3548+
for (pos = 1; n->val.val.str[pos]; pos++)
3549+
{
3550+
if (n->val.val.str[pos] == '.' ||
3551+
n->val.val.str[pos] == '?' ||
3552+
n->val.val.str[pos] == '*' ||
3553+
n->val.val.str[pos] == '[' ||
3554+
n->val.val.str[pos] == '$')
3555+
break;
3556+
if (n->val.val.str[pos] == '\\')
3557+
pos++;
3558+
match_least[match_pos] = n->val.val.str[pos];
3559+
match_most[match_pos++] = n->val.val.str[pos];
3560+
}
3561+
3562+
if (match_pos != 0)
3563+
{
3564+
A_Const *least = makeNode(A_Const);
3565+
A_Const *most = makeNode(A_Const);
3566+
3567+
/* make strings to be used in index use */
3568+
match_least[match_pos] = '\0';
3569+
match_most[match_pos] = '\377';
3570+
match_most[match_pos+1] = '\0';
3571+
least->val.type = T_String;
3572+
least->val.val.str = match_least;
3573+
most->val.type = T_String;
3574+
most->val.val.str = match_most;
3575+
result = makeA_Expr(AND, NULL,
3576+
makeA_Expr(OP, "~", lexpr, rexpr),
3577+
makeA_Expr(AND, NULL,
3578+
makeA_Expr(OP, ">=", lexpr, (Node *)least),
3579+
makeA_Expr(OP, "<=", lexpr, (Node *)most)));
3580+
}
3581+
}
3582+
}
3583+
else if (strcmp(opname,"~*") == 0)
3584+
{
3585+
if (nodeTag(rexpr) == T_A_Const &&
3586+
((A_Const *)rexpr)->val.type == T_String &&
3587+
((A_Const *)rexpr)->val.val.str[0] == '^')
3588+
{
3589+
A_Const *n = (A_Const *)rexpr;
3590+
char *match_lower_least = palloc(strlen(n->val.val.str)+2);
3591+
char *match_lower_most = palloc(strlen(n->val.val.str)+2);
3592+
char *match_upper_least = palloc(strlen(n->val.val.str)+2);
3593+
char *match_upper_most = palloc(strlen(n->val.val.str)+2);
3594+
int pos, match_pos=0;
3595+
3596+
/* skip leading ^ */
3597+
for (pos = 1; n->val.val.str[pos]; pos++)
3598+
{
3599+
if (n->val.val.str[pos] == '.' ||
3600+
n->val.val.str[pos] == '?' ||
3601+
n->val.val.str[pos] == '*' ||
3602+
n->val.val.str[pos] == '[' ||
3603+
n->val.val.str[pos] == '$')
3604+
break;
3605+
if (n->val.val.str[pos] == '\\')
3606+
pos++;
3607+
/* If we have punctuation, this works well */
3608+
match_lower_least[match_pos] = tolower(n->val.val.str[pos]);
3609+
match_lower_most[match_pos] = tolower(n->val.val.str[pos]);
3610+
match_upper_least[match_pos] = toupper(n->val.val.str[pos]);
3611+
match_upper_most[match_pos++] = toupper(n->val.val.str[pos]);
3612+
}
3613+
3614+
if (match_pos != 0)
3615+
{
3616+
A_Const *lower_least = makeNode(A_Const);
3617+
A_Const *lower_most = makeNode(A_Const);
3618+
A_Const *upper_least = makeNode(A_Const);
3619+
A_Const *upper_most = makeNode(A_Const);
3620+
3621+
/* make strings to be used in index use */
3622+
match_lower_least[match_pos] = '\0';
3623+
match_lower_most[match_pos] = '\377';
3624+
match_lower_most[match_pos+1] = '\0';
3625+
match_upper_least[match_pos] = '\0';
3626+
match_upper_most[match_pos] = '\377';
3627+
match_upper_most[match_pos+1] = '\0';
3628+
lower_least->val.type = T_String;
3629+
lower_least->val.val.str = match_lower_least;
3630+
lower_most->val.type = T_String;
3631+
lower_most->val.val.str = match_lower_most;
3632+
upper_least->val.type = T_String;
3633+
upper_least->val.val.str = match_upper_least;
3634+
upper_most->val.type = T_String;
3635+
upper_most->val.val.str = match_upper_most;
3636+
result = makeA_Expr(AND, NULL,
3637+
makeA_Expr(OP, "~*", lexpr, rexpr),
3638+
makeA_Expr(OR, NULL,
3639+
makeA_Expr(AND, NULL,
3640+
makeA_Expr(OP, ">=", lexpr, (Node *)lower_least),
3641+
makeA_Expr(OP, "<=", lexpr, (Node *)lower_most)),
3642+
makeA_Expr(AND, NULL,
3643+
makeA_Expr(OP, ">=", lexpr, (Node *)upper_least),
3644+
makeA_Expr(OP, "<=", lexpr, (Node *)upper_most))));
3645+
}
3646+
}
3647+
}
3648+
else if (strcmp(opname,"~~") == 0)
3649+
{
3650+
if (nodeTag(rexpr) == T_A_Const &&
3651+
((A_Const *)rexpr)->val.type == T_String)
3652+
{
3653+
A_Const *n = (A_Const *)rexpr;
3654+
char *match_least = palloc(strlen(n->val.val.str)+2);
3655+
char *match_most = palloc(strlen(n->val.val.str)+2);
3656+
int pos, match_pos=0;
3657+
3658+
for (pos = 0; n->val.val.str[pos]; pos++)
3659+
{
3660+
if ((n->val.val.str[pos] == '%' &&
3661+
n->val.val.str[pos+1] != '%') ||
3662+
(n->val.val.str[pos] == '_' &&
3663+
n->val.val.str[pos+1] != '_'))
3664+
break;
3665+
if (n->val.val.str[pos] == '%' ||
3666+
n->val.val.str[pos] == '_' ||
3667+
n->val.val.str[pos] == '\\')
3668+
pos++;
3669+
match_least[match_pos] = n->val.val.str[pos];
3670+
match_most[match_pos++] = n->val.val.str[pos];
3671+
}
3672+
3673+
if (match_pos != 0)
3674+
{
3675+
A_Const *least = makeNode(A_Const);
3676+
A_Const *most = makeNode(A_Const);
3677+
3678+
/* make strings to be used in index use */
3679+
match_least[match_pos] = '\0';
3680+
match_most[match_pos] = '\377';
3681+
match_most[match_pos+1] = '\0';
3682+
least->val.type = T_String;
3683+
least->val.val.str = match_least;
3684+
most->val.type = T_String;
3685+
most->val.val.str = match_most;
3686+
result = makeA_Expr(AND, NULL,
3687+
makeA_Expr(OP, "~~", lexpr, rexpr),
3688+
makeA_Expr(AND, NULL,
3689+
makeA_Expr(OP, ">=", lexpr, (Node *)least),
3690+
makeA_Expr(OP, "<=", lexpr, (Node *)most)));
3691+
}
3692+
}
3693+
}
3694+
3695+
if (result == NULL)
3696+
result = makeA_Expr(OP, opname, lexpr, rexpr);
3697+
return result;
3698+
}
3699+
3700+
35303701
/* xlateSqlType()
35313702
* Convert alternate type names to internal Postgres types.
35323703
* Do not convert "float", since that is handled elsewhere

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp