*** a/src/backend/executor/execQual.c --- b/src/backend/executor/execQual.c *************** *** 125,130 **** static Datum ExecEvalCase(CaseExprState *caseExpr, ExprContext *econtext, --- 125,133 ---- static Datum ExecEvalCaseTestExpr(ExprState *exprstate, ExprContext *econtext, bool *isNull, ExprDoneCond *isDone); + static Datum ExecEvalInExpr(InExprState *exprstate, + ExprContext *econtext, + bool *isNull, ExprDoneCond *isDone); static Datum ExecEvalArray(ArrayExprState *astate, ExprContext *econtext, bool *isNull, ExprDoneCond *isDone); *************** *** 2854,2859 **** ExecEvalCaseTestExpr(ExprState *exprstate, --- 2857,2933 ---- } /* ---------------------------------------------------------------- + * ExecEvalInExpr + * + * Evaluate an IN clause. + * ---------------------------------------------------------------- + */ + static Datum + ExecEvalInExpr(InExprState *inExpr, ExprContext *econtext, + bool *isNull, ExprDoneCond *isDone) + { + ListCell *clause; + Datum save_datum; + bool save_isNull; + bool anyNull = false; + + if (isDone) + *isDone = ExprSingleResult; + + /* + * First we evaluate the lefthand side of the IN clause, and save the + * value where the CaseTestExpr placeholders can find it. We must save and + * restore prior setting of econtext's caseValue fields, in case this node + * is itself within a larger CASE or IN. + */ + save_datum = econtext->caseValue_datum; + save_isNull = econtext->caseValue_isNull; + + econtext->caseValue_datum = ExecEvalExpr(inExpr->lexpr, + econtext, + &econtext->caseValue_isNull, + NULL); + + /* + * Evaluate each comparison expression in turn, until we find a match or + * run out of expressions. NULLs are handled the same as in a list of + * OR expressions. + */ + anyNull = false; + foreach(clause, inExpr->comparisons) + { + ExprState *expr = lfirst(clause); + Datum clause_value; + + clause_value = ExecEvalExpr(expr, econtext, isNull, NULL); + + if (*isNull) + anyNull = true; /* remember we got a null */ + else + { + /* return true as soon as one comparison returns true. */ + if (DatumGetBool(clause_value)) + { + econtext->caseValue_datum = save_datum; + econtext->caseValue_isNull = save_isNull; + *isNull = false; + return BoolGetDatum(true); + } + } + } + + econtext->caseValue_datum = save_datum; + econtext->caseValue_isNull = save_isNull; + + /* + * If no match, return false, or NULL if any of the comparisons returned + * NULL. + */ + *isNull = anyNull; + return BoolGetDatum(false); + } + + /* ---------------------------------------------------------------- * ExecEvalArray - ARRAY[] expressions * ---------------------------------------------------------------- */ *************** *** 4253,4258 **** ExecInitExpr(Expr *node, PlanState *parent) --- 4327,4342 ---- state = (ExprState *) makeNode(ExprState); state->evalfunc = ExecEvalCaseTestExpr; break; + case T_InExpr: + { + InExpr *inexpr = (InExpr *) node; + InExprState *istate = (InExprState *) makeNode(InExprState); + istate->lexpr = ExecInitExpr((Expr *) inexpr->lexpr, parent); + istate->comparisons = (List *) ExecInitExpr((Expr *) inexpr->comparisons, parent); + istate->xprstate.evalfunc = (ExprStateEvalFunc) ExecEvalInExpr; + state = (ExprState *) istate; + } + break; case T_Aggref: { Aggref *aggref = (Aggref *) node; *** a/src/backend/nodes/copyfuncs.c --- b/src/backend/nodes/copyfuncs.c *************** *** 1526,1531 **** _copyCaseTestExpr(CaseTestExpr *from) --- 1526,1546 ---- } /* + * _copyInExpr + */ + static InExpr * + _copyInExpr(InExpr *from) + { + InExpr *newnode = makeNode(InExpr); + + COPY_NODE_FIELD(lexpr); + COPY_NODE_FIELD(comparisons); + COPY_LOCATION_FIELD(location); + + return newnode; + } + + /* * _copyArrayExpr */ static ArrayExpr * *************** *** 4044,4049 **** copyObject(void *from) --- 4059,4067 ---- case T_CaseTestExpr: retval = _copyCaseTestExpr(from); break; + case T_InExpr: + retval = _copyInExpr(from); + break; case T_ArrayExpr: retval = _copyArrayExpr(from); break; *** a/src/backend/nodes/equalfuncs.c --- b/src/backend/nodes/equalfuncs.c *************** *** 571,576 **** _equalCaseTestExpr(CaseTestExpr *a, CaseTestExpr *b) --- 571,586 ---- } static bool + _equalInExpr(InExpr *a, InExpr *b) + { + COMPARE_NODE_FIELD(lexpr); + COMPARE_NODE_FIELD(comparisons); + COMPARE_LOCATION_FIELD(location); + + return true; + } + + static bool _equalArrayExpr(ArrayExpr *a, ArrayExpr *b) { COMPARE_SCALAR_FIELD(array_typeid); *************** *** 2610,2615 **** equal(void *a, void *b) --- 2620,2628 ---- case T_CaseTestExpr: retval = _equalCaseTestExpr(a, b); break; + case T_InExpr: + retval = _equalInExpr(a, b); + break; case T_ArrayExpr: retval = _equalArrayExpr(a, b); break; *** a/src/backend/nodes/nodeFuncs.c --- b/src/backend/nodes/nodeFuncs.c *************** *** 88,93 **** exprType(Node *expr) --- 88,96 ---- case T_BoolExpr: type = BOOLOID; break; + case T_InExpr: + type = BOOLOID; + break; case T_SubLink: { SubLink *sublink = (SubLink *) expr; *************** *** 680,685 **** exprCollation(Node *expr) --- 683,691 ---- case T_BoolExpr: coll = InvalidOid; /* result is always boolean */ break; + case T_InExpr: + coll = InvalidOid; /* result is always boolean */ + break; case T_SubLink: { SubLink *sublink = (SubLink *) expr; *************** *** 912,917 **** exprSetCollation(Node *expr, Oid collation) --- 918,926 ---- case T_BoolExpr: Assert(!OidIsValid(collation)); /* result is always boolean */ break; + case T_InExpr: + Assert(!OidIsValid(collation)); /* result is always boolean */ + break; case T_SubLink: #ifdef USE_ASSERT_CHECKING { *************** *** 1687,1692 **** expression_tree_walker(Node *node, --- 1696,1711 ---- return true; } break; + case T_InExpr: + { + InExpr *inexpr = (InExpr *) node; + + if (walker(inexpr->lexpr, context)) + return true; + if (walker(inexpr->comparisons, context)) + return true; + } + break; case T_ArrayExpr: return walker(((ArrayExpr *) node)->elements, context); case T_RowExpr: *************** *** 2289,2294 **** expression_tree_mutator(Node *node, --- 2308,2324 ---- return (Node *) newnode; } break; + case T_InExpr: + { + InExpr *inexpr = (InExpr *) node; + InExpr *newnode; + + FLATCOPY(newnode, inexpr, InExpr); + MUTATE(newnode->lexpr, inexpr->lexpr, Expr *); + MUTATE(newnode->comparisons, inexpr->comparisons, List *); + return (Node *) newnode; + } + break; case T_ArrayExpr: { ArrayExpr *arrayexpr = (ArrayExpr *) node; *************** *** 2758,2763 **** bool --- 2788,2803 ---- return true; } break; + case T_InExpr: + { + InExpr *inexpr = (InExpr *) node; + + if (walker(inexpr->lexpr, context)) + return true; + if (walker(inexpr->comparisons, context)) + return true; + } + break; case T_RowExpr: /* Assume colnames isn't interesting */ return walker(((RowExpr *) node)->args, context); *** a/src/backend/nodes/outfuncs.c --- b/src/backend/nodes/outfuncs.c *************** *** 1262,1267 **** _outCaseTestExpr(StringInfo str, CaseTestExpr *node) --- 1262,1277 ---- } static void + _outInExpr(StringInfo str, InExpr *node) + { + WRITE_NODE_TYPE("INEXPR"); + + WRITE_NODE_FIELD(lexpr); + WRITE_NODE_FIELD(comparisons); + WRITE_LOCATION_FIELD(location); + } + + static void _outArrayExpr(StringInfo str, ArrayExpr *node) { WRITE_NODE_TYPE("ARRAY"); *************** *** 2880,2885 **** _outNode(StringInfo str, void *obj) --- 2890,2898 ---- case T_CaseTestExpr: _outCaseTestExpr(str, obj); break; + case T_InExpr: + _outInExpr(str, obj); + break; case T_ArrayExpr: _outArrayExpr(str, obj); break; *** a/src/backend/nodes/readfuncs.c --- b/src/backend/nodes/readfuncs.c *************** *** 897,902 **** _readCaseTestExpr(void) --- 897,917 ---- } /* + * _readInExpr + */ + static InExpr * + _readInExpr(void) + { + READ_LOCALS(InExpr); + + READ_NODE_FIELD(lexpr); + READ_NODE_FIELD(comparisons); + READ_LOCATION_FIELD(location); + + READ_DONE(); + } + + /* * _readArrayExpr */ static ArrayExpr * *************** *** 1311,1316 **** parseNodeString(void) --- 1326,1333 ---- return_value = _readCollateExpr(); else if (MATCH("CASE", 4)) return_value = _readCaseExpr(); + else if (MATCH("INEXPR", 6)) + return_value = _readInExpr(); else if (MATCH("WHEN", 4)) return_value = _readCaseWhen(); else if (MATCH("CASETESTEXPR", 12)) *** a/src/backend/optimizer/util/clauses.c --- b/src/backend/optimizer/util/clauses.c *************** *** 2801,2806 **** eval_const_expressions_mutator(Node *node, --- 2801,2860 ---- else return copyObject(node); } + if (IsA(node, InExpr)) + { + InExpr *inexpr = (InExpr *) node; + Node *newlexpr; + + /* Simplify the left expression */ + newlexpr = eval_const_expressions_mutator((Node *) inexpr->lexpr, + context); + + /* + * If the left expression is not volatile, transform the expression + * into a list of ORs. For example: + * foo IN (bar, 123) + * is transformed to + * foo = bar OR foo = 123 + * + * The OR clauses give the planner more room for optimization, like + * choosing a bitmap-OR path. But we can't do the transformation if + * the lefthand expression is volatile, because it is evaluated + * multiple times in the OR form. + */ + if (!contain_volatile_functions(newlexpr)) + { + Node *orclause; + Node *save_case_val; + + save_case_val = context->case_val; + context->case_val = newlexpr; + orclause = (Node *) make_orclause(inexpr->comparisons); + orclause = eval_const_expressions_mutator(orclause, + context); + context->case_val = save_case_val; + return orclause; + } + else + { + /* can't transform to OR clauses. */ + InExpr *newinexpr; + ListCell *lc; + + newinexpr = makeNode(InExpr); + newinexpr->lexpr = (Expr *) newlexpr; + foreach(lc, inexpr->comparisons) + { + Node *cmp; + + cmp = eval_const_expressions_mutator((Node *) lfirst(lc), + context); + newinexpr->comparisons = lappend(newinexpr->comparisons, cmp); + } + + return (Node *) newinexpr; + } + } if (IsA(node, ArrayExpr)) { ArrayExpr *arrayexpr = (ArrayExpr *) node; *** a/src/backend/parser/gram.y --- b/src/backend/parser/gram.y *************** *** 10055,10061 **** a_expr: c_expr { $$ = $1; } else { /* generate scalar NOT IN expression */ ! $$ = (Node *) makeSimpleA_Expr(AEXPR_IN, "<>", $1, $4, @2); } } | a_expr subquery_Op sub_type select_with_parens %prec Op --- 10055,10063 ---- else { /* generate scalar NOT IN expression */ ! $$ = (Node *) makeA_Expr(AEXPR_NOT, NIL, NULL, ! (Node *) makeSimpleA_Expr(AEXPR_IN, "=", $1, $4, @2), ! @2); } } | a_expr subquery_Op sub_type select_with_parens %prec Op *** a/src/backend/parser/parse_expr.c --- b/src/backend/parser/parse_expr.c *************** *** 313,318 **** transformExpr(ParseState *pstate, Node *expr) --- 313,319 ---- case T_NullIfExpr: case T_ScalarArrayOpExpr: case T_BoolExpr: + case T_InExpr: case T_FieldSelect: case T_FieldStore: case T_RelabelType: *************** *** 1071,1187 **** transformAExprOf(ParseState *pstate, A_Expr *a) static Node * transformAExprIn(ParseState *pstate, A_Expr *a) { ! Node *result = NULL; Node *lexpr; List *rexprs; - List *rvars; - List *rnonvars; - bool useOr; bool haveRowExpr; ListCell *l; ! /* ! * If the operator is <>, combine with AND not OR. ! */ ! if (strcmp(strVal(linitial(a->name)), "<>") == 0) ! useOr = false; ! else ! useOr = true; /* - * We try to generate a ScalarArrayOpExpr from IN/NOT IN, but this is only - * possible if the inputs are all scalars (no RowExprs) and there is a - * suitable array type available. If not, we fall back to a boolean - * condition tree with multiple copies of the lefthand expression. Also, - * any IN-list items that contain Vars are handled as separate boolean - * conditions, because that gives the planner more scope for optimization - * on such clauses. - * * First step: transform all the inputs, and detect whether any are ! * RowExprs or contain Vars. */ lexpr = transformExpr(pstate, a->lexpr); haveRowExpr = (lexpr && IsA(lexpr, RowExpr)); ! rexprs = rvars = rnonvars = NIL; foreach(l, (List *) a->rexpr) { Node *rexpr = transformExpr(pstate, lfirst(l)); haveRowExpr |= (rexpr && IsA(rexpr, RowExpr)); rexprs = lappend(rexprs, rexpr); - if (contain_vars_of_level(rexpr, 0)) - rvars = lappend(rvars, rexpr); - else - rnonvars = lappend(rnonvars, rexpr); } /* ! * ScalarArrayOpExpr is only going to be useful if there's more than one ! * non-Var righthand item. Also, it won't work for RowExprs. */ ! if (!haveRowExpr && list_length(rnonvars) > 1) ! { ! List *allexprs; ! Oid scalar_type; ! Oid array_type; ! /* ! * Try to select a common type for the array elements. Note that ! * since the LHS' type is first in the list, it will be preferred when ! * there is doubt (eg, when all the RHS items are unknown literals). ! * ! * Note: use list_concat here not lcons, to avoid damaging rnonvars. ! */ ! allexprs = list_concat(list_make1(lexpr), rnonvars); ! scalar_type = select_common_type(pstate, allexprs, NULL, NULL); ! /* Do we have an array type to use? */ ! if (OidIsValid(scalar_type)) ! array_type = get_array_type(scalar_type); ! else ! array_type = InvalidOid; ! if (array_type != InvalidOid) ! { ! /* ! * OK: coerce all the right-hand non-Var inputs to the common type ! * and build an ArrayExpr for them. ! */ ! List *aexprs; ! ArrayExpr *newa; ! aexprs = NIL; ! foreach(l, rnonvars) ! { ! Node *rexpr = (Node *) lfirst(l); ! rexpr = coerce_to_common_type(pstate, rexpr, ! scalar_type, ! "IN"); ! aexprs = lappend(aexprs, rexpr); ! } ! newa = makeNode(ArrayExpr); ! newa->array_typeid = array_type; ! /* array_collid will be set by parse_collate.c */ ! newa->element_typeid = scalar_type; ! newa->elements = aexprs; ! newa->multidims = false; ! newa->location = -1; ! ! result = (Node *) make_scalar_array_op(pstate, ! a->name, ! useOr, ! lexpr, ! (Node *) newa, ! a->location); ! ! /* Consider only the Vars (if any) in the loop below */ ! rexprs = rvars; } } ! /* ! * Must do it the hard way, ie, with a boolean expression tree. ! */ foreach(l, rexprs) { Node *rexpr = (Node *) lfirst(l); --- 1072,1156 ---- static Node * transformAExprIn(ParseState *pstate, A_Expr *a) { ! InExpr *result; Node *lexpr; List *rexprs; bool haveRowExpr; ListCell *l; + List *newrexprs; + List *comparisons; + Node *placeholder; + List *fieldPlaceholders = NIL; ! Assert(strcmp(strVal(linitial(a->name)), "=") == 0); /* * First step: transform all the inputs, and detect whether any are ! * RowExprs. */ lexpr = transformExpr(pstate, a->lexpr); haveRowExpr = (lexpr && IsA(lexpr, RowExpr)); ! rexprs = NIL; foreach(l, (List *) a->rexpr) { Node *rexpr = transformExpr(pstate, lfirst(l)); haveRowExpr |= (rexpr && IsA(rexpr, RowExpr)); rexprs = lappend(rexprs, rexpr); } + lexpr = (Node *) transformExpr(pstate, a->lexpr); + /* ! * Run collation assignment on the test expression so that we know ! * what collation to mark the placeholder with. In principle we ! * could leave it to parse_collate.c to do that later, but propagating ! * the result to the CaseTestExpr would be unnecessarily complicated. */ ! assign_expr_collations(pstate, lexpr); ! /* generate placeholder for test expression */ ! if (IsA(lexpr, Const)) ! { ! placeholder = lexpr; ! } ! else ! { ! CaseTestExpr *c = makeNode(CaseTestExpr); ! c->typeId = exprType(lexpr); ! c->typeMod = exprTypmod(lexpr); ! c->collation = exprCollation(lexpr); ! placeholder = (Node *) c; ! } ! /* ! * If this is a row IN clause, generate placeholders for each field in ! * the test expression. ! */ ! if (IsA(lexpr, RowExpr)) ! { ! AttrNumber fieldnum = 1; ! fieldPlaceholders = NIL; ! foreach(l, ((RowExpr *) lexpr)->args) ! { ! Node *arg = lfirst(l); ! FieldSelect *f = makeNode(FieldSelect); ! ! f->arg = (Expr *) placeholder; ! f->fieldnum = fieldnum++; ! f->resulttype = exprType(arg); ! f->resulttypmod = exprTypmod(arg); ! f->resultcollid = exprCollation(arg); ! fieldPlaceholders = lappend(fieldPlaceholders, f); } } ! /* Construct comparison expressions */ ! newrexprs = NIL; ! comparisons = NIL; ! haveRowExpr = IsA(lexpr, RowExpr); foreach(l, rexprs) { Node *rexpr = (Node *) lfirst(l); *************** *** 1195,1223 **** transformAExprIn(ParseState *pstate, A_Expr *a) (errcode(ERRCODE_SYNTAX_ERROR), errmsg("arguments of row IN must all be row expressions"), parser_errposition(pstate, a->location))); cmp = make_row_comparison_op(pstate, a->name, ! (List *) copyObject(((RowExpr *) lexpr)->args), ((RowExpr *) rexpr)->args, a->location); } else cmp = (Node *) make_op(pstate, a->name, ! copyObject(lexpr), rexpr, a->location); cmp = coerce_to_boolean(pstate, cmp, "IN"); ! if (result == NULL) ! result = cmp; ! else ! result = (Node *) makeBoolExpr(useOr ? OR_EXPR : AND_EXPR, ! list_make2(result, cmp), ! a->location); } ! return result; } static Node * --- 1164,1200 ---- (errcode(ERRCODE_SYNTAX_ERROR), errmsg("arguments of row IN must all be row expressions"), parser_errposition(pstate, a->location))); + cmp = make_row_comparison_op(pstate, a->name, ! fieldPlaceholders, ((RowExpr *) rexpr)->args, a->location); } else + { + Node *arg; + + arg = (Node *) placeholder; cmp = (Node *) make_op(pstate, a->name, ! arg, rexpr, a->location); + } cmp = coerce_to_boolean(pstate, cmp, "IN"); ! ! comparisons = lappend(comparisons, cmp); } ! result = makeNode(InExpr); ! result->lexpr = (Expr *) lexpr; ! result->comparisons = comparisons; ! /* Make the result have the original input's parse location */ ! result->location = exprLocation((Node *) a); ! ! return (Node *) result; } static Node * *** a/src/backend/utils/adt/ruleutils.c --- b/src/backend/utils/adt/ruleutils.c *************** *** 5245,5250 **** get_rule_expr(Node *node, deparse_context *context, --- 5245,5335 ---- } break; + case T_InExpr: + { + InExpr *inexpr = (InExpr *) node; + ListCell *temp; + const char *sep; + + if (!PRETTY_PAREN(context)) + appendStringInfoChar(buf, '('); + + get_rule_expr((Node *) inexpr->lexpr, context, showimplicit); + appendStringInfoString(buf, " IN ("); + sep = ""; + foreach(temp, inexpr->comparisons) + { + Node *cmp = (Node *) lfirst(temp); + + appendStringInfoString(buf, sep); + + cmp = strip_implicit_coercions(cmp); + /* + * Scalar IN clause. The comparison expressions are of + * form: + * CaseTestExpr = arg + * Print the 'arg' + */ + if (IsA(cmp, OpExpr)) + { + OpExpr *eq = (OpExpr *) cmp; + + Assert(list_length(eq->args) == 2); + Assert(IsA(strip_implicit_coercions(linitial(eq->args)), + CaseTestExpr) || + IsA(strip_implicit_coercions(linitial(eq->args)), + Const)); + + get_rule_expr(lsecond(eq->args), context, false); + } + /* + * Row IN clause. The comparison expressions are of form: + * CaseTestExpr.field1 = arg1 AND CaseTestExpr.field2 = arg2 + * Print '(arg1, arg2)' + */ + else if (IsA(cmp, BoolExpr)) + { + BoolExpr *b = (BoolExpr *) cmp; + ListCell *temp2; + const char *sep2 = ""; + + if (b->boolop != AND_EXPR) + elog(ERROR, "unexpected boolean expression in IN clause: %d", + b->boolop); + appendStringInfoChar(buf, '('); + foreach (temp2, b->args) + { + Node *a = (Node *) lfirst(temp2); + OpExpr *eq; + + if (!IsA(a, OpExpr)) + elog(ERROR, "unexpected IN clause: %d", + (int) nodeTag(a)); + eq = (OpExpr *) a; + + Assert(list_length(eq->args) == 2); + Assert(IsA(strip_implicit_coercions(linitial(eq->args)), + FieldSelect)); + /* Assert the arg of FieldSelect is CaseTestExpr or Const */ + + appendStringInfoString(buf, sep2); + get_rule_expr(lsecond(eq->args), context, false); + sep2 = ", "; + } + appendStringInfoChar(buf, ')'); + } + else + elog(ERROR, "unexpected IN clause: %d", + (int) nodeTag(cmp)); + sep = ", "; + } + appendStringInfoChar(buf, ')'); + + if (!PRETTY_PAREN(context)) + appendStringInfoChar(buf, ')'); + } + break; + case T_ArrayExpr: { ArrayExpr *arrayexpr = (ArrayExpr *) node; *** a/src/include/nodes/execnodes.h --- b/src/include/nodes/execnodes.h *************** *** 828,833 **** typedef struct CaseWhenState --- 828,844 ---- } CaseWhenState; /* ---------------- + * InExprState node + * ---------------- + */ + typedef struct InExprState + { + ExprState xprstate; + ExprState *lexpr; /* lefthand expression */ + List *comparisons; /* comparison expressions */ + } InExprState; + + /* ---------------- * ArrayExprState node * * Note: ARRAY[] expressions always produce varlena arrays, never fixed-length *** a/src/include/nodes/nodes.h --- b/src/include/nodes/nodes.h *************** *** 170,175 **** typedef enum NodeTag --- 170,176 ---- T_JoinExpr, T_FromExpr, T_IntoClause, + T_InExpr, /* * TAGS FOR EXPRESSION STATE NODES (execnodes.h) *************** *** 203,208 **** typedef enum NodeTag --- 204,210 ---- T_NullTestState, T_CoerceToDomainState, T_DomainConstraintState, + T_InExprState, /* * TAGS FOR PLANNER NODES (relation.h) *** a/src/include/nodes/primnodes.h --- b/src/include/nodes/primnodes.h *************** *** 797,804 **** typedef struct CaseWhen * This is effectively like a Param, but can be implemented more simply * since we need only one replacement value at a time. * ! * We also use this in nested UPDATE expressions. ! * See transformAssignmentIndirection(). */ typedef struct CaseTestExpr { --- 797,804 ---- * This is effectively like a Param, but can be implemented more simply * since we need only one replacement value at a time. * ! * We also use this in nested UPDATE expressions and IN expressions. ! * See transformAssignmentIndirection() and transformInExpr(). */ typedef struct CaseTestExpr { *************** *** 808,813 **** typedef struct CaseTestExpr --- 808,830 ---- Oid collation; /* collation for the substituted value */ } CaseTestExpr; + + /* + * Parse analysis converts IN clause to a list of valid boolean expressions + * of the form + * CaseTestExpr '=' rexpr + * where the CaseTestExpr node is a placeholder that emits the correct + * value of the lefthand expression at runtime. This structure is used so + * that the lefthand expression need be evaluated only once. + */ + typedef struct InExpr + { + Expr xpr; + Expr *lexpr; /* lefthand expression */ + List *comparisons; /* comparison expressions */ + int location; /* token location, or -1 if unknown */ + } InExpr; + /* * ArrayExpr - an ARRAY[] expression * *** /dev/null --- b/src/test/regress/expected/inclause.out *************** *** 0 **** --- 1,156 ---- + -- + -- Test the IN clause + -- + CREATE TABLE IN_TBL ( + i integer, + j integer + ); + INSERT INTO IN_TBL VALUES (1, 2); + INSERT INTO IN_TBL VALUES (2, 3); + INSERT INTO IN_TBL VALUES (3, 4); + INSERT INTO IN_TBL VALUES (4, NULL); + CREATE INDEX i_in_tbl ON IN_TBL (i); + -- Simple scalar IN, including untyped literals + SELECT 1 IN (1, 2); + ?column? + ---------- + t + (1 row) + + SELECT 50 IN (1, 2, 3, 4); + ?column? + ---------- + f + (1 row) + + SELECT 'foo' IN ('foo', 'bar'); + ?column? + ---------- + t + (1 row) + + SELECT 'notfound' IN ('foo', 'bar'); + ?column? + ---------- + f + (1 row) + + SELECT 'foo' IN ('foo'); + ?column? + ---------- + t + (1 row) + + SELECT 'foo' IN ('bar'); + ?column? + ---------- + f + (1 row) + + SELECT '123' IN ('bar', 123); + ?column? + ---------- + t + (1 row) + + SELECT 'foo' IN ('bar', 123); + ERROR: invalid input syntax for integer: "foo" + LINE 1: SELECT 'foo' IN ('bar', 123); + ^ + -- Simple row IN + SELECT (1, 2) IN ((1, 2), (2, 3)); + ?column? + ---------- + t + (1 row) + + SELECT (1, 3) IN ((1, 2), (2, 3)); + ?column? + ---------- + f + (1 row) + + SELECT (1, 3) IN ((1, 2)); + ?column? + ---------- + f + (1 row) + + -- Scalar and row IN using table + SELECT * FROM in_tbl WHERE i IN (0, 1); + i | j + ---+--- + 1 | 2 + (1 row) + + SELECT * FROM in_tbl WHERE i IN (5, 6, 7, 8, 9, 10); + i | j + ---+--- + (0 rows) + + SELECT * FROM in_tbl WHERE (i, j) IN ((1, 2), (2, 4), (2, 3), (10, 11)); + i | j + ---+--- + 1 | 2 + 2 | 3 + (2 rows) + + SELECT * FROM in_tbl WHERE (i, j) IN ((1, 3)); + i | j + ---+--- + (0 rows) + + -- Scalar with NULLs + SELECT 1 IN (0, NULL, 2); + ?column? + ---------- + + (1 row) + + SELECT 1 IN (1, NULL, 2); + ?column? + ---------- + t + (1 row) + + SELECT 3 IN (1, 2, NULL); + ?column? + ---------- + + (1 row) + + SELECT NULL IN (1, 2, NULL); + ?column? + ---------- + + (1 row) + + -- + -- Check that the lefthand expression is only executed once. Before 9.1 + -- we used to transform IN to a list of OR expressions, which lead to + -- multiple evaluation. + -- + CREATE FUNCTION volatilefunc() RETURNS int4 AS $$ + begin + RAISE NOTICE 'volatilefunc executed'; + RETURN 123; + end; + $$ LANGUAGE plpgsql VOLATILE; + SELECT volatilefunc() IN (1, 2, 3, 123); + NOTICE: volatilefunc executed + ?column? + ---------- + t + (1 row) + + -- Subselect IN + SELECT 1 IN (SELECT i FROM in_tbl); + ?column? + ---------- + t + (1 row) + + -- + -- Clean up + -- + DROP TABLE IN_TBL; *** a/src/test/regress/expected/rules.out --- b/src/test/regress/expected/rules.out *************** *** 1293,1323 **** SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem pg_settings | SELECT a.name, a.setting, a.unit, a.category, a.short_desc, a.extra_desc, a.context, a.vartype, a.source, a.min_val, a.max_val, a.enumvals, a.boot_val, a.reset_val, a.sourcefile, a.sourceline FROM pg_show_all_settings() a(name, setting, unit, category, short_desc, extra_desc, context, vartype, source, min_val, max_val, enumvals, boot_val, reset_val, sourcefile, sourceline); pg_shadow | SELECT pg_authid.rolname AS usename, pg_authid.oid AS usesysid, pg_authid.rolcreatedb AS usecreatedb, pg_authid.rolsuper AS usesuper, pg_authid.rolcatupdate AS usecatupd, pg_authid.rolreplication AS userepl, pg_authid.rolpassword AS passwd, (pg_authid.rolvaliduntil)::abstime AS valuntil, s.setconfig AS useconfig FROM (pg_authid LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid)))) WHERE pg_authid.rolcanlogin; pg_stat_activity | SELECT s.datid, d.datname, s.procpid, s.usesysid, u.rolname AS usename, s.application_name, s.client_addr, s.client_hostname, s.client_port, s.backend_start, s.xact_start, s.query_start, s.waiting, s.current_query FROM pg_database d, pg_stat_get_activity(NULL::integer) s(datid, procpid, usesysid, application_name, current_query, waiting, xact_start, query_start, backend_start, client_addr, client_hostname, client_port), pg_authid u WHERE ((s.datid = d.oid) AND (s.usesysid = u.oid)); ! pg_stat_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname AS indexrelname, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])); ! pg_stat_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_numscans(c.oid) AS seq_scan, pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, (sum(pg_stat_get_numscans(i.indexrelid)))::bigint AS idx_scan, ((sum(pg_stat_get_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_tuples_fetched(c.oid)) AS idx_tup_fetch, pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid) AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del, pg_stat_get_tuples_hot_updated(c.oid) AS n_tup_hot_upd, pg_stat_get_live_tuples(c.oid) AS n_live_tup, pg_stat_get_dead_tuples(c.oid) AS n_dead_tup, pg_stat_get_last_vacuum_time(c.oid) AS last_vacuum, pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum, pg_stat_get_last_analyze_time(c.oid) AS last_analyze, pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze, pg_stat_get_vacuum_count(c.oid) AS vacuum_count, pg_stat_get_autovacuum_count(c.oid) AS autovacuum_count, pg_stat_get_analyze_count(c.oid) AS analyze_count, pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count FROM ((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])) GROUP BY c.oid, n.nspname, c.relname; pg_stat_bgwriter | SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed, pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req, pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint, pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean, pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean, pg_stat_get_buf_written_backend() AS buffers_backend, pg_stat_get_buf_fsync_backend() AS buffers_backend_fsync, pg_stat_get_buf_alloc() AS buffers_alloc, pg_stat_get_bgwriter_stat_reset_time() AS stats_reset; pg_stat_database | SELECT d.oid AS datid, d.datname, pg_stat_get_db_numbackends(d.oid) AS numbackends, pg_stat_get_db_xact_commit(d.oid) AS xact_commit, pg_stat_get_db_xact_rollback(d.oid) AS xact_rollback, (pg_stat_get_db_blocks_fetched(d.oid) - pg_stat_get_db_blocks_hit(d.oid)) AS blks_read, pg_stat_get_db_blocks_hit(d.oid) AS blks_hit, pg_stat_get_db_tuples_returned(d.oid) AS tup_returned, pg_stat_get_db_tuples_fetched(d.oid) AS tup_fetched, pg_stat_get_db_tuples_inserted(d.oid) AS tup_inserted, pg_stat_get_db_tuples_updated(d.oid) AS tup_updated, pg_stat_get_db_tuples_deleted(d.oid) AS tup_deleted, pg_stat_get_db_conflict_all(d.oid) AS conflicts, pg_stat_get_db_stat_reset_time(d.oid) AS stats_reset FROM pg_database d; pg_stat_database_conflicts | SELECT d.oid AS datid, d.datname, pg_stat_get_db_conflict_tablespace(d.oid) AS confl_tablespace, pg_stat_get_db_conflict_lock(d.oid) AS confl_lock, pg_stat_get_db_conflict_snapshot(d.oid) AS confl_snapshot, pg_stat_get_db_conflict_bufferpin(d.oid) AS confl_bufferpin, pg_stat_get_db_conflict_startup_deadlock(d.oid) AS confl_deadlock FROM pg_database d; pg_stat_replication | SELECT s.procpid, s.usesysid, u.rolname AS usename, s.application_name, s.client_addr, s.client_hostname, s.client_port, s.backend_start, w.state, w.sent_location, w.write_location, w.flush_location, w.replay_location, w.sync_priority, w.sync_state FROM pg_stat_get_activity(NULL::integer) s(datid, procpid, usesysid, application_name, current_query, waiting, xact_start, query_start, backend_start, client_addr, client_hostname, client_port), pg_authid u, pg_stat_get_wal_senders() w(procpid, state, sent_location, write_location, flush_location, replay_location, sync_priority, sync_state) WHERE ((s.usesysid = u.oid) AND (s.procpid = w.procpid)); ! pg_stat_sys_indexes | SELECT pg_stat_all_indexes.relid, pg_stat_all_indexes.indexrelid, pg_stat_all_indexes.schemaname, pg_stat_all_indexes.relname, pg_stat_all_indexes.indexrelname, pg_stat_all_indexes.idx_scan, pg_stat_all_indexes.idx_tup_read, pg_stat_all_indexes.idx_tup_fetch FROM pg_stat_all_indexes WHERE ((pg_stat_all_indexes.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_all_indexes.schemaname ~ '^pg_toast'::text)); ! pg_stat_sys_tables | SELECT pg_stat_all_tables.relid, pg_stat_all_tables.schemaname, pg_stat_all_tables.relname, pg_stat_all_tables.seq_scan, pg_stat_all_tables.seq_tup_read, pg_stat_all_tables.idx_scan, pg_stat_all_tables.idx_tup_fetch, pg_stat_all_tables.n_tup_ins, pg_stat_all_tables.n_tup_upd, pg_stat_all_tables.n_tup_del, pg_stat_all_tables.n_tup_hot_upd, pg_stat_all_tables.n_live_tup, pg_stat_all_tables.n_dead_tup, pg_stat_all_tables.last_vacuum, pg_stat_all_tables.last_autovacuum, pg_stat_all_tables.last_analyze, pg_stat_all_tables.last_autoanalyze, pg_stat_all_tables.vacuum_count, pg_stat_all_tables.autovacuum_count, pg_stat_all_tables.analyze_count, pg_stat_all_tables.autoanalyze_count FROM pg_stat_all_tables WHERE ((pg_stat_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_all_tables.schemaname ~ '^pg_toast'::text)); pg_stat_user_functions | SELECT p.oid AS funcid, n.nspname AS schemaname, p.proname AS funcname, pg_stat_get_function_calls(p.oid) AS calls, (pg_stat_get_function_time(p.oid) / 1000) AS total_time, (pg_stat_get_function_self_time(p.oid) / 1000) AS self_time FROM (pg_proc p LEFT JOIN pg_namespace n ON ((n.oid = p.pronamespace))) WHERE ((p.prolang <> (12)::oid) AND (pg_stat_get_function_calls(p.oid) IS NOT NULL)); ! pg_stat_user_indexes | SELECT pg_stat_all_indexes.relid, pg_stat_all_indexes.indexrelid, pg_stat_all_indexes.schemaname, pg_stat_all_indexes.relname, pg_stat_all_indexes.indexrelname, pg_stat_all_indexes.idx_scan, pg_stat_all_indexes.idx_tup_read, pg_stat_all_indexes.idx_tup_fetch FROM pg_stat_all_indexes WHERE ((pg_stat_all_indexes.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_all_indexes.schemaname !~ '^pg_toast'::text)); ! pg_stat_user_tables | SELECT pg_stat_all_tables.relid, pg_stat_all_tables.schemaname, pg_stat_all_tables.relname, pg_stat_all_tables.seq_scan, pg_stat_all_tables.seq_tup_read, pg_stat_all_tables.idx_scan, pg_stat_all_tables.idx_tup_fetch, pg_stat_all_tables.n_tup_ins, pg_stat_all_tables.n_tup_upd, pg_stat_all_tables.n_tup_del, pg_stat_all_tables.n_tup_hot_upd, pg_stat_all_tables.n_live_tup, pg_stat_all_tables.n_dead_tup, pg_stat_all_tables.last_vacuum, pg_stat_all_tables.last_autovacuum, pg_stat_all_tables.last_analyze, pg_stat_all_tables.last_autoanalyze, pg_stat_all_tables.vacuum_count, pg_stat_all_tables.autovacuum_count, pg_stat_all_tables.analyze_count, pg_stat_all_tables.autoanalyze_count FROM pg_stat_all_tables WHERE ((pg_stat_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_all_tables.schemaname !~ '^pg_toast'::text)); ! pg_stat_xact_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_xact_numscans(c.oid) AS seq_scan, pg_stat_get_xact_tuples_returned(c.oid) AS seq_tup_read, (sum(pg_stat_get_xact_numscans(i.indexrelid)))::bigint AS idx_scan, ((sum(pg_stat_get_xact_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_xact_tuples_fetched(c.oid)) AS idx_tup_fetch, pg_stat_get_xact_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_xact_tuples_updated(c.oid) AS n_tup_upd, pg_stat_get_xact_tuples_deleted(c.oid) AS n_tup_del, pg_stat_get_xact_tuples_hot_updated(c.oid) AS n_tup_hot_upd FROM ((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])) GROUP BY c.oid, n.nspname, c.relname; ! pg_stat_xact_sys_tables | SELECT pg_stat_xact_all_tables.relid, pg_stat_xact_all_tables.schemaname, pg_stat_xact_all_tables.relname, pg_stat_xact_all_tables.seq_scan, pg_stat_xact_all_tables.seq_tup_read, pg_stat_xact_all_tables.idx_scan, pg_stat_xact_all_tables.idx_tup_fetch, pg_stat_xact_all_tables.n_tup_ins, pg_stat_xact_all_tables.n_tup_upd, pg_stat_xact_all_tables.n_tup_del, pg_stat_xact_all_tables.n_tup_hot_upd FROM pg_stat_xact_all_tables WHERE ((pg_stat_xact_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_xact_all_tables.schemaname ~ '^pg_toast'::text)); pg_stat_xact_user_functions | SELECT p.oid AS funcid, n.nspname AS schemaname, p.proname AS funcname, pg_stat_get_xact_function_calls(p.oid) AS calls, (pg_stat_get_xact_function_time(p.oid) / 1000) AS total_time, (pg_stat_get_xact_function_self_time(p.oid) / 1000) AS self_time FROM (pg_proc p LEFT JOIN pg_namespace n ON ((n.oid = p.pronamespace))) WHERE ((p.prolang <> (12)::oid) AND (pg_stat_get_xact_function_calls(p.oid) IS NOT NULL)); ! pg_stat_xact_user_tables | SELECT pg_stat_xact_all_tables.relid, pg_stat_xact_all_tables.schemaname, pg_stat_xact_all_tables.relname, pg_stat_xact_all_tables.seq_scan, pg_stat_xact_all_tables.seq_tup_read, pg_stat_xact_all_tables.idx_scan, pg_stat_xact_all_tables.idx_tup_fetch, pg_stat_xact_all_tables.n_tup_ins, pg_stat_xact_all_tables.n_tup_upd, pg_stat_xact_all_tables.n_tup_del, pg_stat_xact_all_tables.n_tup_hot_upd FROM pg_stat_xact_all_tables WHERE ((pg_stat_xact_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_xact_all_tables.schemaname !~ '^pg_toast'::text)); ! pg_statio_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname AS indexrelname, (pg_stat_get_blocks_fetched(i.oid) - pg_stat_get_blocks_hit(i.oid)) AS idx_blks_read, pg_stat_get_blocks_hit(i.oid) AS idx_blks_hit FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])); pg_statio_all_sequences | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, (pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid)) AS blks_read, pg_stat_get_blocks_hit(c.oid) AS blks_hit FROM (pg_class c LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'S'::"char"); ! pg_statio_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, (pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid)) AS heap_blks_read, pg_stat_get_blocks_hit(c.oid) AS heap_blks_hit, (sum((pg_stat_get_blocks_fetched(i.indexrelid) - pg_stat_get_blocks_hit(i.indexrelid))))::bigint AS idx_blks_read, (sum(pg_stat_get_blocks_hit(i.indexrelid)))::bigint AS idx_blks_hit, (pg_stat_get_blocks_fetched(t.oid) - pg_stat_get_blocks_hit(t.oid)) AS toast_blks_read, pg_stat_get_blocks_hit(t.oid) AS toast_blks_hit, (pg_stat_get_blocks_fetched(x.oid) - pg_stat_get_blocks_hit(x.oid)) AS tidx_blks_read, pg_stat_get_blocks_hit(x.oid) AS tidx_blks_hit FROM ((((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_class t ON ((c.reltoastrelid = t.oid))) LEFT JOIN pg_class x ON ((t.reltoastidxid = x.oid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])) GROUP BY c.oid, n.nspname, c.relname, t.oid, x.oid; ! pg_statio_sys_indexes | SELECT pg_statio_all_indexes.relid, pg_statio_all_indexes.indexrelid, pg_statio_all_indexes.schemaname, pg_statio_all_indexes.relname, pg_statio_all_indexes.indexrelname, pg_statio_all_indexes.idx_blks_read, pg_statio_all_indexes.idx_blks_hit FROM pg_statio_all_indexes WHERE ((pg_statio_all_indexes.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_statio_all_indexes.schemaname ~ '^pg_toast'::text)); ! pg_statio_sys_sequences | SELECT pg_statio_all_sequences.relid, pg_statio_all_sequences.schemaname, pg_statio_all_sequences.relname, pg_statio_all_sequences.blks_read, pg_statio_all_sequences.blks_hit FROM pg_statio_all_sequences WHERE ((pg_statio_all_sequences.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_statio_all_sequences.schemaname ~ '^pg_toast'::text)); ! pg_statio_sys_tables | SELECT pg_statio_all_tables.relid, pg_statio_all_tables.schemaname, pg_statio_all_tables.relname, pg_statio_all_tables.heap_blks_read, pg_statio_all_tables.heap_blks_hit, pg_statio_all_tables.idx_blks_read, pg_statio_all_tables.idx_blks_hit, pg_statio_all_tables.toast_blks_read, pg_statio_all_tables.toast_blks_hit, pg_statio_all_tables.tidx_blks_read, pg_statio_all_tables.tidx_blks_hit FROM pg_statio_all_tables WHERE ((pg_statio_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_statio_all_tables.schemaname ~ '^pg_toast'::text)); ! pg_statio_user_indexes | SELECT pg_statio_all_indexes.relid, pg_statio_all_indexes.indexrelid, pg_statio_all_indexes.schemaname, pg_statio_all_indexes.relname, pg_statio_all_indexes.indexrelname, pg_statio_all_indexes.idx_blks_read, pg_statio_all_indexes.idx_blks_hit FROM pg_statio_all_indexes WHERE ((pg_statio_all_indexes.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_statio_all_indexes.schemaname !~ '^pg_toast'::text)); ! pg_statio_user_sequences | SELECT pg_statio_all_sequences.relid, pg_statio_all_sequences.schemaname, pg_statio_all_sequences.relname, pg_statio_all_sequences.blks_read, pg_statio_all_sequences.blks_hit FROM pg_statio_all_sequences WHERE ((pg_statio_all_sequences.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_statio_all_sequences.schemaname !~ '^pg_toast'::text)); ! pg_statio_user_tables | SELECT pg_statio_all_tables.relid, pg_statio_all_tables.schemaname, pg_statio_all_tables.relname, pg_statio_all_tables.heap_blks_read, pg_statio_all_tables.heap_blks_hit, pg_statio_all_tables.idx_blks_read, pg_statio_all_tables.idx_blks_hit, pg_statio_all_tables.toast_blks_read, pg_statio_all_tables.toast_blks_hit, pg_statio_all_tables.tidx_blks_read, pg_statio_all_tables.tidx_blks_hit FROM pg_statio_all_tables WHERE ((pg_statio_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_statio_all_tables.schemaname !~ '^pg_toast'::text)); ! pg_stats | SELECT n.nspname AS schemaname, c.relname AS tablename, a.attname, s.stainherit AS inherited, s.stanullfrac AS null_frac, s.stawidth AS avg_width, s.stadistinct AS n_distinct, CASE WHEN (s.stakind1 = ANY (ARRAY[1, 4])) THEN s.stavalues1 WHEN (s.stakind2 = ANY (ARRAY[1, 4])) THEN s.stavalues2 WHEN (s.stakind3 = ANY (ARRAY[1, 4])) THEN s.stavalues3 WHEN (s.stakind4 = ANY (ARRAY[1, 4])) THEN s.stavalues4 ELSE NULL::anyarray END AS most_common_vals, CASE WHEN (s.stakind1 = ANY (ARRAY[1, 4])) THEN s.stanumbers1 WHEN (s.stakind2 = ANY (ARRAY[1, 4])) THEN s.stanumbers2 WHEN (s.stakind3 = ANY (ARRAY[1, 4])) THEN s.stanumbers3 WHEN (s.stakind4 = ANY (ARRAY[1, 4])) THEN s.stanumbers4 ELSE NULL::real[] END AS most_common_freqs, CASE WHEN (s.stakind1 = 2) THEN s.stavalues1 WHEN (s.stakind2 = 2) THEN s.stavalues2 WHEN (s.stakind3 = 2) THEN s.stavalues3 WHEN (s.stakind4 = 2) THEN s.stavalues4 ELSE NULL::anyarray END AS histogram_bounds, CASE WHEN (s.stakind1 = 3) THEN s.stanumbers1[1] WHEN (s.stakind2 = 3) THEN s.stanumbers2[1] WHEN (s.stakind3 = 3) THEN s.stanumbers3[1] WHEN (s.stakind4 = 3) THEN s.stanumbers4[1] ELSE NULL::real END AS correlation FROM (((pg_statistic s JOIN pg_class c ON ((c.oid = s.starelid))) JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum = s.staattnum)))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE ((NOT a.attisdropped) AND has_column_privilege(c.oid, a.attnum, 'select'::text)); pg_tables | SELECT n.nspname AS schemaname, c.relname AS tablename, pg_get_userbyid(c.relowner) AS tableowner, t.spcname AS tablespace, c.relhasindex AS hasindexes, c.relhasrules AS hasrules, c.relhastriggers AS hastriggers FROM ((pg_class c LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace))) WHERE (c.relkind = 'r'::"char"); pg_timezone_abbrevs | SELECT pg_timezone_abbrevs.abbrev, pg_timezone_abbrevs.utc_offset, pg_timezone_abbrevs.is_dst FROM pg_timezone_abbrevs() pg_timezone_abbrevs(abbrev, utc_offset, is_dst); pg_timezone_names | SELECT pg_timezone_names.name, pg_timezone_names.abbrev, pg_timezone_names.utc_offset, pg_timezone_names.is_dst FROM pg_timezone_names() pg_timezone_names(name, abbrev, utc_offset, is_dst); --- 1293,1323 ---- pg_settings | SELECT a.name, a.setting, a.unit, a.category, a.short_desc, a.extra_desc, a.context, a.vartype, a.source, a.min_val, a.max_val, a.enumvals, a.boot_val, a.reset_val, a.sourcefile, a.sourceline FROM pg_show_all_settings() a(name, setting, unit, category, short_desc, extra_desc, context, vartype, source, min_val, max_val, enumvals, boot_val, reset_val, sourcefile, sourceline); pg_shadow | SELECT pg_authid.rolname AS usename, pg_authid.oid AS usesysid, pg_authid.rolcreatedb AS usecreatedb, pg_authid.rolsuper AS usesuper, pg_authid.rolcatupdate AS usecatupd, pg_authid.rolreplication AS userepl, pg_authid.rolpassword AS passwd, (pg_authid.rolvaliduntil)::abstime AS valuntil, s.setconfig AS useconfig FROM (pg_authid LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid)))) WHERE pg_authid.rolcanlogin; pg_stat_activity | SELECT s.datid, d.datname, s.procpid, s.usesysid, u.rolname AS usename, s.application_name, s.client_addr, s.client_hostname, s.client_port, s.backend_start, s.xact_start, s.query_start, s.waiting, s.current_query FROM pg_database d, pg_stat_get_activity(NULL::integer) s(datid, procpid, usesysid, application_name, current_query, waiting, xact_start, query_start, backend_start, client_addr, client_hostname, client_port), pg_authid u WHERE ((s.datid = d.oid) AND (s.usesysid = u.oid)); ! pg_stat_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname AS indexrelname, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind IN ('r'::"char", 't'::"char")); ! pg_stat_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_numscans(c.oid) AS seq_scan, pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, (sum(pg_stat_get_numscans(i.indexrelid)))::bigint AS idx_scan, ((sum(pg_stat_get_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_tuples_fetched(c.oid)) AS idx_tup_fetch, pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid) AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del, pg_stat_get_tuples_hot_updated(c.oid) AS n_tup_hot_upd, pg_stat_get_live_tuples(c.oid) AS n_live_tup, pg_stat_get_dead_tuples(c.oid) AS n_dead_tup, pg_stat_get_last_vacuum_time(c.oid) AS last_vacuum, pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum, pg_stat_get_last_analyze_time(c.oid) AS last_analyze, pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze, pg_stat_get_vacuum_count(c.oid) AS vacuum_count, pg_stat_get_autovacuum_count(c.oid) AS autovacuum_count, pg_stat_get_analyze_count(c.oid) AS analyze_count, pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count FROM ((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind IN ('r'::"char", 't'::"char")) GROUP BY c.oid, n.nspname, c.relname; pg_stat_bgwriter | SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed, pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req, pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint, pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean, pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean, pg_stat_get_buf_written_backend() AS buffers_backend, pg_stat_get_buf_fsync_backend() AS buffers_backend_fsync, pg_stat_get_buf_alloc() AS buffers_alloc, pg_stat_get_bgwriter_stat_reset_time() AS stats_reset; pg_stat_database | SELECT d.oid AS datid, d.datname, pg_stat_get_db_numbackends(d.oid) AS numbackends, pg_stat_get_db_xact_commit(d.oid) AS xact_commit, pg_stat_get_db_xact_rollback(d.oid) AS xact_rollback, (pg_stat_get_db_blocks_fetched(d.oid) - pg_stat_get_db_blocks_hit(d.oid)) AS blks_read, pg_stat_get_db_blocks_hit(d.oid) AS blks_hit, pg_stat_get_db_tuples_returned(d.oid) AS tup_returned, pg_stat_get_db_tuples_fetched(d.oid) AS tup_fetched, pg_stat_get_db_tuples_inserted(d.oid) AS tup_inserted, pg_stat_get_db_tuples_updated(d.oid) AS tup_updated, pg_stat_get_db_tuples_deleted(d.oid) AS tup_deleted, pg_stat_get_db_conflict_all(d.oid) AS conflicts, pg_stat_get_db_stat_reset_time(d.oid) AS stats_reset FROM pg_database d; pg_stat_database_conflicts | SELECT d.oid AS datid, d.datname, pg_stat_get_db_conflict_tablespace(d.oid) AS confl_tablespace, pg_stat_get_db_conflict_lock(d.oid) AS confl_lock, pg_stat_get_db_conflict_snapshot(d.oid) AS confl_snapshot, pg_stat_get_db_conflict_bufferpin(d.oid) AS confl_bufferpin, pg_stat_get_db_conflict_startup_deadlock(d.oid) AS confl_deadlock FROM pg_database d; pg_stat_replication | SELECT s.procpid, s.usesysid, u.rolname AS usename, s.application_name, s.client_addr, s.client_hostname, s.client_port, s.backend_start, w.state, w.sent_location, w.write_location, w.flush_location, w.replay_location, w.sync_priority, w.sync_state FROM pg_stat_get_activity(NULL::integer) s(datid, procpid, usesysid, application_name, current_query, waiting, xact_start, query_start, backend_start, client_addr, client_hostname, client_port), pg_authid u, pg_stat_get_wal_senders() w(procpid, state, sent_location, write_location, flush_location, replay_location, sync_priority, sync_state) WHERE ((s.usesysid = u.oid) AND (s.procpid = w.procpid)); ! pg_stat_sys_indexes | SELECT pg_stat_all_indexes.relid, pg_stat_all_indexes.indexrelid, pg_stat_all_indexes.schemaname, pg_stat_all_indexes.relname, pg_stat_all_indexes.indexrelname, pg_stat_all_indexes.idx_scan, pg_stat_all_indexes.idx_tup_read, pg_stat_all_indexes.idx_tup_fetch FROM pg_stat_all_indexes WHERE ((pg_stat_all_indexes.schemaname IN ('pg_catalog'::name, 'information_schema'::name)) OR (pg_stat_all_indexes.schemaname ~ '^pg_toast'::text)); ! pg_stat_sys_tables | SELECT pg_stat_all_tables.relid, pg_stat_all_tables.schemaname, pg_stat_all_tables.relname, pg_stat_all_tables.seq_scan, pg_stat_all_tables.seq_tup_read, pg_stat_all_tables.idx_scan, pg_stat_all_tables.idx_tup_fetch, pg_stat_all_tables.n_tup_ins, pg_stat_all_tables.n_tup_upd, pg_stat_all_tables.n_tup_del, pg_stat_all_tables.n_tup_hot_upd, pg_stat_all_tables.n_live_tup, pg_stat_all_tables.n_dead_tup, pg_stat_all_tables.last_vacuum, pg_stat_all_tables.last_autovacuum, pg_stat_all_tables.last_analyze, pg_stat_all_tables.last_autoanalyze, pg_stat_all_tables.vacuum_count, pg_stat_all_tables.autovacuum_count, pg_stat_all_tables.analyze_count, pg_stat_all_tables.autoanalyze_count FROM pg_stat_all_tables WHERE ((pg_stat_all_tables.schemaname IN ('pg_catalog'::name, 'information_schema'::name)) OR (pg_stat_all_tables.schemaname ~ '^pg_toast'::text)); pg_stat_user_functions | SELECT p.oid AS funcid, n.nspname AS schemaname, p.proname AS funcname, pg_stat_get_function_calls(p.oid) AS calls, (pg_stat_get_function_time(p.oid) / 1000) AS total_time, (pg_stat_get_function_self_time(p.oid) / 1000) AS self_time FROM (pg_proc p LEFT JOIN pg_namespace n ON ((n.oid = p.pronamespace))) WHERE ((p.prolang <> (12)::oid) AND (pg_stat_get_function_calls(p.oid) IS NOT NULL)); ! pg_stat_user_indexes | SELECT pg_stat_all_indexes.relid, pg_stat_all_indexes.indexrelid, pg_stat_all_indexes.schemaname, pg_stat_all_indexes.relname, pg_stat_all_indexes.indexrelname, pg_stat_all_indexes.idx_scan, pg_stat_all_indexes.idx_tup_read, pg_stat_all_indexes.idx_tup_fetch FROM pg_stat_all_indexes WHERE ((NOT (pg_stat_all_indexes.schemaname IN ('pg_catalog'::name, 'information_schema'::name))) AND (pg_stat_all_indexes.schemaname !~ '^pg_toast'::text)); ! pg_stat_user_tables | SELECT pg_stat_all_tables.relid, pg_stat_all_tables.schemaname, pg_stat_all_tables.relname, pg_stat_all_tables.seq_scan, pg_stat_all_tables.seq_tup_read, pg_stat_all_tables.idx_scan, pg_stat_all_tables.idx_tup_fetch, pg_stat_all_tables.n_tup_ins, pg_stat_all_tables.n_tup_upd, pg_stat_all_tables.n_tup_del, pg_stat_all_tables.n_tup_hot_upd, pg_stat_all_tables.n_live_tup, pg_stat_all_tables.n_dead_tup, pg_stat_all_tables.last_vacuum, pg_stat_all_tables.last_autovacuum, pg_stat_all_tables.last_analyze, pg_stat_all_tables.last_autoanalyze, pg_stat_all_tables.vacuum_count, pg_stat_all_tables.autovacuum_count, pg_stat_all_tables.analyze_count, pg_stat_all_tables.autoanalyze_count FROM pg_stat_all_tables WHERE ((NOT (pg_stat_all_tables.schemaname IN ('pg_catalog'::name, 'information_schema'::name))) AND (pg_stat_all_tables.schemaname !~ '^pg_toast'::text)); ! pg_stat_xact_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_xact_numscans(c.oid) AS seq_scan, pg_stat_get_xact_tuples_returned(c.oid) AS seq_tup_read, (sum(pg_stat_get_xact_numscans(i.indexrelid)))::bigint AS idx_scan, ((sum(pg_stat_get_xact_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_xact_tuples_fetched(c.oid)) AS idx_tup_fetch, pg_stat_get_xact_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_xact_tuples_updated(c.oid) AS n_tup_upd, pg_stat_get_xact_tuples_deleted(c.oid) AS n_tup_del, pg_stat_get_xact_tuples_hot_updated(c.oid) AS n_tup_hot_upd FROM ((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind IN ('r'::"char", 't'::"char")) GROUP BY c.oid, n.nspname, c.relname; ! pg_stat_xact_sys_tables | SELECT pg_stat_xact_all_tables.relid, pg_stat_xact_all_tables.schemaname, pg_stat_xact_all_tables.relname, pg_stat_xact_all_tables.seq_scan, pg_stat_xact_all_tables.seq_tup_read, pg_stat_xact_all_tables.idx_scan, pg_stat_xact_all_tables.idx_tup_fetch, pg_stat_xact_all_tables.n_tup_ins, pg_stat_xact_all_tables.n_tup_upd, pg_stat_xact_all_tables.n_tup_del, pg_stat_xact_all_tables.n_tup_hot_upd FROM pg_stat_xact_all_tables WHERE ((pg_stat_xact_all_tables.schemaname IN ('pg_catalog'::name, 'information_schema'::name)) OR (pg_stat_xact_all_tables.schemaname ~ '^pg_toast'::text)); pg_stat_xact_user_functions | SELECT p.oid AS funcid, n.nspname AS schemaname, p.proname AS funcname, pg_stat_get_xact_function_calls(p.oid) AS calls, (pg_stat_get_xact_function_time(p.oid) / 1000) AS total_time, (pg_stat_get_xact_function_self_time(p.oid) / 1000) AS self_time FROM (pg_proc p LEFT JOIN pg_namespace n ON ((n.oid = p.pronamespace))) WHERE ((p.prolang <> (12)::oid) AND (pg_stat_get_xact_function_calls(p.oid) IS NOT NULL)); ! pg_stat_xact_user_tables | SELECT pg_stat_xact_all_tables.relid, pg_stat_xact_all_tables.schemaname, pg_stat_xact_all_tables.relname, pg_stat_xact_all_tables.seq_scan, pg_stat_xact_all_tables.seq_tup_read, pg_stat_xact_all_tables.idx_scan, pg_stat_xact_all_tables.idx_tup_fetch, pg_stat_xact_all_tables.n_tup_ins, pg_stat_xact_all_tables.n_tup_upd, pg_stat_xact_all_tables.n_tup_del, pg_stat_xact_all_tables.n_tup_hot_upd FROM pg_stat_xact_all_tables WHERE ((NOT (pg_stat_xact_all_tables.schemaname IN ('pg_catalog'::name, 'information_schema'::name))) AND (pg_stat_xact_all_tables.schemaname !~ '^pg_toast'::text)); ! pg_statio_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname AS indexrelname, (pg_stat_get_blocks_fetched(i.oid) - pg_stat_get_blocks_hit(i.oid)) AS idx_blks_read, pg_stat_get_blocks_hit(i.oid) AS idx_blks_hit FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind IN ('r'::"char", 't'::"char")); pg_statio_all_sequences | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, (pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid)) AS blks_read, pg_stat_get_blocks_hit(c.oid) AS blks_hit FROM (pg_class c LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'S'::"char"); ! pg_statio_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, (pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid)) AS heap_blks_read, pg_stat_get_blocks_hit(c.oid) AS heap_blks_hit, (sum((pg_stat_get_blocks_fetched(i.indexrelid) - pg_stat_get_blocks_hit(i.indexrelid))))::bigint AS idx_blks_read, (sum(pg_stat_get_blocks_hit(i.indexrelid)))::bigint AS idx_blks_hit, (pg_stat_get_blocks_fetched(t.oid) - pg_stat_get_blocks_hit(t.oid)) AS toast_blks_read, pg_stat_get_blocks_hit(t.oid) AS toast_blks_hit, (pg_stat_get_blocks_fetched(x.oid) - pg_stat_get_blocks_hit(x.oid)) AS tidx_blks_read, pg_stat_get_blocks_hit(x.oid) AS tidx_blks_hit FROM ((((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_class t ON ((c.reltoastrelid = t.oid))) LEFT JOIN pg_class x ON ((t.reltoastidxid = x.oid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind IN ('r'::"char", 't'::"char")) GROUP BY c.oid, n.nspname, c.relname, t.oid, x.oid; ! pg_statio_sys_indexes | SELECT pg_statio_all_indexes.relid, pg_statio_all_indexes.indexrelid, pg_statio_all_indexes.schemaname, pg_statio_all_indexes.relname, pg_statio_all_indexes.indexrelname, pg_statio_all_indexes.idx_blks_read, pg_statio_all_indexes.idx_blks_hit FROM pg_statio_all_indexes WHERE ((pg_statio_all_indexes.schemaname IN ('pg_catalog'::name, 'information_schema'::name)) OR (pg_statio_all_indexes.schemaname ~ '^pg_toast'::text)); ! pg_statio_sys_sequences | SELECT pg_statio_all_sequences.relid, pg_statio_all_sequences.schemaname, pg_statio_all_sequences.relname, pg_statio_all_sequences.blks_read, pg_statio_all_sequences.blks_hit FROM pg_statio_all_sequences WHERE ((pg_statio_all_sequences.schemaname IN ('pg_catalog'::name, 'information_schema'::name)) OR (pg_statio_all_sequences.schemaname ~ '^pg_toast'::text)); ! pg_statio_sys_tables | SELECT pg_statio_all_tables.relid, pg_statio_all_tables.schemaname, pg_statio_all_tables.relname, pg_statio_all_tables.heap_blks_read, pg_statio_all_tables.heap_blks_hit, pg_statio_all_tables.idx_blks_read, pg_statio_all_tables.idx_blks_hit, pg_statio_all_tables.toast_blks_read, pg_statio_all_tables.toast_blks_hit, pg_statio_all_tables.tidx_blks_read, pg_statio_all_tables.tidx_blks_hit FROM pg_statio_all_tables WHERE ((pg_statio_all_tables.schemaname IN ('pg_catalog'::name, 'information_schema'::name)) OR (pg_statio_all_tables.schemaname ~ '^pg_toast'::text)); ! pg_statio_user_indexes | SELECT pg_statio_all_indexes.relid, pg_statio_all_indexes.indexrelid, pg_statio_all_indexes.schemaname, pg_statio_all_indexes.relname, pg_statio_all_indexes.indexrelname, pg_statio_all_indexes.idx_blks_read, pg_statio_all_indexes.idx_blks_hit FROM pg_statio_all_indexes WHERE ((NOT (pg_statio_all_indexes.schemaname IN ('pg_catalog'::name, 'information_schema'::name))) AND (pg_statio_all_indexes.schemaname !~ '^pg_toast'::text)); ! pg_statio_user_sequences | SELECT pg_statio_all_sequences.relid, pg_statio_all_sequences.schemaname, pg_statio_all_sequences.relname, pg_statio_all_sequences.blks_read, pg_statio_all_sequences.blks_hit FROM pg_statio_all_sequences WHERE ((NOT (pg_statio_all_sequences.schemaname IN ('pg_catalog'::name, 'information_schema'::name))) AND (pg_statio_all_sequences.schemaname !~ '^pg_toast'::text)); ! pg_statio_user_tables | SELECT pg_statio_all_tables.relid, pg_statio_all_tables.schemaname, pg_statio_all_tables.relname, pg_statio_all_tables.heap_blks_read, pg_statio_all_tables.heap_blks_hit, pg_statio_all_tables.idx_blks_read, pg_statio_all_tables.idx_blks_hit, pg_statio_all_tables.toast_blks_read, pg_statio_all_tables.toast_blks_hit, pg_statio_all_tables.tidx_blks_read, pg_statio_all_tables.tidx_blks_hit FROM pg_statio_all_tables WHERE ((NOT (pg_statio_all_tables.schemaname IN ('pg_catalog'::name, 'information_schema'::name))) AND (pg_statio_all_tables.schemaname !~ '^pg_toast'::text)); ! pg_stats | SELECT n.nspname AS schemaname, c.relname AS tablename, a.attname, s.stainherit AS inherited, s.stanullfrac AS null_frac, s.stawidth AS avg_width, s.stadistinct AS n_distinct, CASE WHEN (s.stakind1 IN (1, 4)) THEN s.stavalues1 WHEN (s.stakind2 IN (1, 4)) THEN s.stavalues2 WHEN (s.stakind3 IN (1, 4)) THEN s.stavalues3 WHEN (s.stakind4 IN (1, 4)) THEN s.stavalues4 ELSE NULL::anyarray END AS most_common_vals, CASE WHEN (s.stakind1 IN (1, 4)) THEN s.stanumbers1 WHEN (s.stakind2 IN (1, 4)) THEN s.stanumbers2 WHEN (s.stakind3 IN (1, 4)) THEN s.stanumbers3 WHEN (s.stakind4 IN (1, 4)) THEN s.stanumbers4 ELSE NULL::real[] END AS most_common_freqs, CASE WHEN (s.stakind1 = 2) THEN s.stavalues1 WHEN (s.stakind2 = 2) THEN s.stavalues2 WHEN (s.stakind3 = 2) THEN s.stavalues3 WHEN (s.stakind4 = 2) THEN s.stavalues4 ELSE NULL::anyarray END AS histogram_bounds, CASE WHEN (s.stakind1 = 3) THEN s.stanumbers1[1] WHEN (s.stakind2 = 3) THEN s.stanumbers2[1] WHEN (s.stakind3 = 3) THEN s.stanumbers3[1] WHEN (s.stakind4 = 3) THEN s.stanumbers4[1] ELSE NULL::real END AS correlation FROM (((pg_statistic s JOIN pg_class c ON ((c.oid = s.starelid))) JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum = s.staattnum)))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE ((NOT a.attisdropped) AND has_column_privilege(c.oid, a.attnum, 'select'::text)); pg_tables | SELECT n.nspname AS schemaname, c.relname AS tablename, pg_get_userbyid(c.relowner) AS tableowner, t.spcname AS tablespace, c.relhasindex AS hasindexes, c.relhasrules AS hasrules, c.relhastriggers AS hastriggers FROM ((pg_class c LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace))) WHERE (c.relkind = 'r'::"char"); pg_timezone_abbrevs | SELECT pg_timezone_abbrevs.abbrev, pg_timezone_abbrevs.utc_offset, pg_timezone_abbrevs.is_dst FROM pg_timezone_abbrevs() pg_timezone_abbrevs(abbrev, utc_offset, is_dst); pg_timezone_names | SELECT pg_timezone_names.name, pg_timezone_names.abbrev, pg_timezone_names.utc_offset, pg_timezone_names.is_dst FROM pg_timezone_names() pg_timezone_names(name, abbrev, utc_offset, is_dst); *** a/src/test/regress/parallel_schedule --- b/src/test/regress/parallel_schedule *************** *** 79,85 **** test: select_into select_distinct select_distinct_on select_implicit select_havi # ---------- # Another group of parallel tests # ---------- ! test: privileges security_label collate test: misc # rules cannot run concurrently with any test that creates a view --- 79,85 ---- # ---------- # Another group of parallel tests # ---------- ! test: privileges security_label collate inclause test: misc # rules cannot run concurrently with any test that creates a view *** a/src/test/regress/serial_schedule --- b/src/test/regress/serial_schedule *************** *** 90,95 **** test: prepared_xacts --- 90,96 ---- test: privileges test: security_label test: collate + test: inclause test: misc test: rules test: select_views *** /dev/null --- b/src/test/regress/sql/inclause.sql *************** *** 0 **** --- 1,65 ---- + -- + -- Test the IN clause + -- + + CREATE TABLE IN_TBL ( + i integer, + j integer + ); + + INSERT INTO IN_TBL VALUES (1, 2); + INSERT INTO IN_TBL VALUES (2, 3); + INSERT INTO IN_TBL VALUES (3, 4); + INSERT INTO IN_TBL VALUES (4, NULL); + + CREATE INDEX i_in_tbl ON IN_TBL (i); + + -- Simple scalar IN, including untyped literals + SELECT 1 IN (1, 2); + SELECT 50 IN (1, 2, 3, 4); + SELECT 'foo' IN ('foo', 'bar'); + SELECT 'notfound' IN ('foo', 'bar'); + SELECT 'foo' IN ('foo'); + SELECT 'foo' IN ('bar'); + SELECT '123' IN ('bar', 123); + SELECT 'foo' IN ('bar', 123); + + -- Simple row IN + SELECT (1, 2) IN ((1, 2), (2, 3)); + SELECT (1, 3) IN ((1, 2), (2, 3)); + SELECT (1, 3) IN ((1, 2)); + + -- Scalar and row IN using table + SELECT * FROM in_tbl WHERE i IN (0, 1); + SELECT * FROM in_tbl WHERE i IN (5, 6, 7, 8, 9, 10); + SELECT * FROM in_tbl WHERE (i, j) IN ((1, 2), (2, 4), (2, 3), (10, 11)); + SELECT * FROM in_tbl WHERE (i, j) IN ((1, 3)); + + -- Scalar with NULLs + SELECT 1 IN (0, NULL, 2); + SELECT 1 IN (1, NULL, 2); + SELECT 3 IN (1, 2, NULL); + SELECT NULL IN (1, 2, NULL); + + -- + -- Check that the lefthand expression is only executed once. Before 9.1 + -- we used to transform IN to a list of OR expressions, which lead to + -- multiple evaluation. + -- + CREATE FUNCTION volatilefunc() RETURNS int4 AS $$ + begin + RAISE NOTICE 'volatilefunc executed'; + RETURN 123; + end; + $$ LANGUAGE plpgsql VOLATILE; + + SELECT volatilefunc() IN (1, 2, 3, 123); + + -- Subselect IN + SELECT 1 IN (SELECT i FROM in_tbl); + + -- + -- Clean up + -- + + DROP TABLE IN_TBL;