Here's v2 of the patches. Changes from v1:
And here there's v3 of planning and execution: common executor steps for
all types of cached expression.
--
Marina Polyakova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
From 5e89221251670526eb2b5750868ac73eee48f10b Mon Sep 17 00:00:00 2001
From: Marina Polyakova <m.polyak...@postgrespro.ru>
Date: Mon, 15 May 2017 15:31:21 +0300
Subject: [PATCH 2/3] Precalculate stable functions, planning and execution v3
Now in Postgresql only immutable functions are precalculated; stable functions
are calculated for every row so in fact they don't differ from volatile
functions.
This patch includes:
- replacement nonvolatile functions and operators by appropriate cached
expressions
- planning and execution cached expressions
- regression tests
---
src/backend/executor/execExpr.c | 37 +
src/backend/executor/execExprInterp.c | 37 +
src/backend/optimizer/path/allpaths.c | 9 +-
src/backend/optimizer/path/clausesel.c | 13 +
src/backend/optimizer/plan/planagg.c | 1 +
src/backend/optimizer/plan/planner.c | 28 +
src/backend/optimizer/util/clauses.c | 55 +
src/backend/utils/adt/ruleutils.c | 5 +
src/include/executor/execExpr.h | 19 +
src/include/optimizer/planner.h | 3 +
src/include/optimizer/tlist.h | 8 +-
src/pl/plpgsql/src/pl_exec.c | 10 +
.../expected/precalculate_stable_functions.out | 2625 ++++++++++++++++++++
src/test/regress/serial_schedule | 1 +
.../regress/sql/precalculate_stable_functions.sql | 949 +++++++
15 files changed, 3797 insertions(+), 3 deletions(-)
create mode 100644 src/test/regress/expected/precalculate_stable_functions.out
create mode 100644 src/test/regress/sql/precalculate_stable_functions.sql
diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
index 5a34a46..3c2068d 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -865,6 +865,43 @@ ExecInitExprRec(Expr *node, PlanState *parent, ExprState *state,
break;
}
+ case T_CachedExpr:
+ {
+ int adjust_jump;
+
+ /*
+ * Allocate and fill scratch memory used by all steps of
+ * CachedExpr evaluation.
+ */
+ scratch.d.cachedexpr.isExecuted = (bool *) palloc(sizeof(bool));
+ scratch.d.cachedexpr.resnull = (bool *) palloc(sizeof(bool));
+ scratch.d.cachedexpr.resvalue = (Datum *) palloc(sizeof(Datum));
+
+ *scratch.d.cachedexpr.isExecuted = false;
+ *scratch.d.cachedexpr.resnull = false;
+ *scratch.d.cachedexpr.resvalue = (Datum) 0;
+ scratch.d.cachedexpr.jumpdone = -1;
+
+ /* add EEOP_CACHEDEXPR_IF_CACHED step */
+ scratch.opcode = EEOP_CACHEDEXPR_IF_CACHED;
+ ExprEvalPushStep(state, &scratch);
+ adjust_jump = state->steps_len - 1;
+
+ /* add subexpression steps */
+ ExecInitExprRec((Expr *) get_subexpr((CachedExpr *) node),
+ parent, state, resv, resnull);
+
+ /* add EEOP_CACHEDEXPR_SUBEXPR_END step */
+ scratch.opcode = EEOP_CACHEDEXPR_SUBEXPR_END;
+ ExprEvalPushStep(state, &scratch);
+
+ /* adjust jump target */
+ state->steps[adjust_jump].d.cachedexpr.jumpdone =
+ state->steps_len;
+
+ break;
+ }
+
case T_ScalarArrayOpExpr:
{
ScalarArrayOpExpr *opexpr = (ScalarArrayOpExpr *) node;
diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index fed0052..ac7b7f8 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -279,6 +279,7 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull)
TupleTableSlot *innerslot;
TupleTableSlot *outerslot;
TupleTableSlot *scanslot;
+ MemoryContext oldContext; /* for EEOP_CACHEDEXPR_* */
/*
* This array has to be in the same order as enum ExprEvalOp.
@@ -309,6 +310,8 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull)
&&CASE_EEOP_FUNCEXPR_STRICT,
&&CASE_EEOP_FUNCEXPR_FUSAGE,
&&CASE_EEOP_FUNCEXPR_STRICT_FUSAGE,
+ &&CASE_EEOP_CACHEDEXPR_IF_CACHED,
+ &&CASE_EEOP_CACHEDEXPR_SUBEXPR_END,
&&CASE_EEOP_BOOL_AND_STEP_FIRST,
&&CASE_EEOP_BOOL_AND_STEP,
&&CASE_EEOP_BOOL_AND_STEP_LAST,
@@ -721,6 +724,40 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull)
EEO_NEXT();
}
+ EEO_CASE(EEOP_CACHEDEXPR_IF_CACHED)
+ {
+ if (*op->d.cachedexpr.isExecuted)
+ {
+ /* use saved result and skip subexpression evaluation */
+ *op->resnull = *op->d.cachedexpr.resnull;
+ if (!(*op->resnull))
+ *op->resvalue = *op->d.cachedexpr.resvalue;
+
+ EEO_JUMP(op->d.cachedexpr.jumpdone);
+ }
+
+ /*
+ * Switch per-query memory context for subexpression evaluation.
+ * It is necessary to save result between all tuples.
+ */
+ oldContext = MemoryContextSwitchTo(econtext->ecxt_per_query_memory);
+
+ EEO_NEXT();
+ }
+
+ EEO_CASE(EEOP_CACHEDEXPR_SUBEXPR_END)
+ {
+ /* save result and switch memory context back */
+ *op->d.cachedexpr.resnull = *op->resnull;
+ if (!(*op->resnull))
+ *op->d.cachedexpr.resvalue = *op->resvalue;
+ *op->d.cachedexpr.isExecuted = true;
+
+ MemoryContextSwitchTo(oldContext);
+
+ EEO_NEXT();
+ }
+
/*
* If any of its clauses is FALSE, an AND's result is FALSE regardless
* of the states of the rest of the clauses, so we can stop evaluating
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index b93b4fc..a322255 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -378,7 +378,11 @@ set_rel_size(PlannerInfo *root, RelOptInfo *rel,
set_subquery_pathlist(root, rel, rti, rte);
break;
case RTE_FUNCTION:
- set_function_size_estimates(root, rel);
+ {
+ rel->baserestrictinfo = replace_qual_cached_expressions(
+ rel->baserestrictinfo);
+ set_function_size_estimates(root, rel);
+ }
break;
case RTE_TABLEFUNC:
set_tablefunc_size_estimates(root, rel);
@@ -517,6 +521,9 @@ set_plain_rel_size(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte)
*/
check_index_predicates(root, rel);
+ rel->baserestrictinfo = replace_qual_cached_expressions(
+ rel->baserestrictinfo);
+
/* Mark rel with estimated output rows, width, etc */
set_baserel_size_estimates(root, rel);
}
diff --git a/src/backend/optimizer/path/clausesel.c b/src/backend/optimizer/path/clausesel.c
index 758ddea..fc799f1 100644
--- a/src/backend/optimizer/path/clausesel.c
+++ b/src/backend/optimizer/path/clausesel.c
@@ -15,6 +15,7 @@
#include "postgres.h"
#include "nodes/makefuncs.h"
+#include "nodes/nodeFuncs.h"
#include "optimizer/clauses.h"
#include "optimizer/cost.h"
#include "optimizer/pathnode.h"
@@ -825,6 +826,18 @@ clause_selectivity(PlannerInfo *root,
jointype,
sjinfo);
}
+ else if (IsA(clause, CachedExpr))
+ {
+ /*
+ * Not sure this case is needed, but it can't hurt.
+ * Calculate selectivity of subexpression.
+ */
+ s1 = clause_selectivity(root,
+ get_subexpr((CachedExpr *) clause),
+ varRelid,
+ jointype,
+ sjinfo);
+ }
else
{
/*
diff --git a/src/backend/optimizer/plan/planagg.c b/src/backend/optimizer/plan/planagg.c
index 5565736..7a28764 100644
--- a/src/backend/optimizer/plan/planagg.c
+++ b/src/backend/optimizer/plan/planagg.c
@@ -38,6 +38,7 @@
#include "optimizer/pathnode.h"
#include "optimizer/paths.h"
#include "optimizer/planmain.h"
+#include "optimizer/planner.h"
#include "optimizer/subselect.h"
#include "optimizer/tlist.h"
#include "parser/parsetree.h"
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 552b73d..7c68d6d 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -6088,6 +6088,34 @@ get_partitioned_child_rels(PlannerInfo *root, Index rti)
return result;
}
+/*
+ * replace_pathtarget_cached_expressions
+ * Replace cached expresisons in a PathTarget tlist.
+ *
+ * As a notational convenience, returns the same PathTarget pointer passed in.
+ */
+PathTarget *
+replace_pathtarget_cached_expressions(PathTarget *target)
+{
+ target->exprs = (List *) replace_cached_expressions_mutator(
+ (Node *) target->exprs);
+
+ return target;
+}
+
+/*
+ * replace_qual_cached_expressions
+ * Replace cacehd expressions in a WHERE clause. The input can be either an
+ * implicitly-ANDed list of boolean expressions, or a list of RestrictInfo
+ * nodes.
+ */
+List *
+replace_qual_cached_expressions(List *quals)
+{
+ /* No setup needed for tree walk, so away we go */
+ return (List *) replace_cached_expressions_mutator((Node *) quals);
+}
+
static Node *
replace_cached_expressions_mutator(Node *node)
{
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index a1dafc8..0c0284a 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -2758,6 +2758,61 @@ eval_const_expressions_mutator(Node *node,
newexpr->location = expr->location;
return (Node *) newexpr;
}
+ case T_CachedExpr:
+ {
+ CachedExpr *cachedexpr = (CachedExpr *) node;
+ Node *new_subexpr = eval_const_expressions_mutator(
+ get_subexpr(cachedexpr), context);
+ CachedExpr *new_cachedexpr;
+
+ /*
+ * If unsafe transformations are used cached expression should
+ * be always simplified.
+ */
+ if (context->estimate)
+ Assert(IsA(new_subexpr, Const));
+
+ if (IsA(new_subexpr, Const))
+ {
+ /* successfully simplified it */
+ return new_subexpr;
+ }
+ else
+ {
+ /*
+ * The expression cannot be simplified any further, so build
+ * and return a replacement CachedExpr node using the
+ * possibly-simplified arguments of subexpression.
+ */
+ new_cachedexpr = makeNode(CachedExpr);
+ new_cachedexpr->subexprtype = cachedexpr->subexprtype;
+ switch (new_cachedexpr->subexprtype)
+ {
+ case CACHED_FUNCEXPR:
+ new_cachedexpr->subexpr.funcexpr = (FuncExpr *)
+ new_subexpr;
+ break;
+ case CACHED_OPEXPR:
+ new_cachedexpr->subexpr.opexpr = (OpExpr *)
+ new_subexpr;
+ break;
+ case CACHED_DISTINCTEXPR:
+ new_cachedexpr->subexpr.distinctexpr =
+ (DistinctExpr *) new_subexpr;
+ break;
+ case CACHED_NULLIFEXPR:
+ new_cachedexpr->subexpr.nullifexpr = (NullIfExpr *)
+ new_subexpr;
+ break;
+ case CACHED_SCALARARRAYOPEXPR:
+ new_cachedexpr->subexpr.saopexpr =
+ (ScalarArrayOpExpr *) new_subexpr;
+ break;
+ }
+
+ return (Node *) new_cachedexpr;
+ }
+ }
case T_BoolExpr:
{
BoolExpr *expr = (BoolExpr *) node;
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 43b1475..838389d 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -7720,6 +7720,11 @@ get_rule_expr(Node *node, deparse_context *context,
}
break;
+ case T_CachedExpr:
+ get_rule_expr(get_subexpr((CachedExpr *) node), context,
+ showimplicit);
+ break;
+
case T_ScalarArrayOpExpr:
{
ScalarArrayOpExpr *expr = (ScalarArrayOpExpr *) node;
diff --git a/src/include/executor/execExpr.h b/src/include/executor/execExpr.h
index 86fdb33..650c8af 100644
--- a/src/include/executor/execExpr.h
+++ b/src/include/executor/execExpr.h
@@ -86,6 +86,16 @@ typedef enum ExprEvalOp
EEOP_FUNCEXPR_STRICT_FUSAGE,
/*
+ * Evaluate CachedExpr. EEOP_CACHEDEXPR_IF_CACHED is used before
+ * subexpression evaluation (if subexpression was evaluated use cached value
+ * and jump to next state or get prepared to subexpression evaluation
+ * otherwise). EEOP_CACHEDEXPR_SUBEXPR_END is used after subexpression
+ * evaluation for caching its result.
+ */
+ EEOP_CACHEDEXPR_IF_CACHED,
+ EEOP_CACHEDEXPR_SUBEXPR_END,
+
+ /*
* Evaluate boolean AND expression, one step per subexpression. FIRST/LAST
* subexpressions are special-cased for performance. Since AND always has
* at least two subexpressions, FIRST and LAST never apply to the same
@@ -298,6 +308,15 @@ typedef struct ExprEvalStep
int nargs; /* number of arguments */
} func;
+ /* for EEOP_CACHEDEXPR_* */
+ struct
+ {
+ bool *isExecuted;
+ bool *resnull;
+ Datum *resvalue;
+ int jumpdone; /* jump here if result determined */
+ } cachedexpr;
+
/* for EEOP_BOOL_*_STEP */
struct
{
diff --git a/src/include/optimizer/planner.h b/src/include/optimizer/planner.h
index f3aaa23..bbadcdd 100644
--- a/src/include/optimizer/planner.h
+++ b/src/include/optimizer/planner.h
@@ -59,4 +59,7 @@ extern bool plan_cluster_use_sort(Oid tableOid, Oid indexOid);
extern List *get_partitioned_child_rels(PlannerInfo *root, Index rti);
+extern PathTarget *replace_pathtarget_cached_expressions(PathTarget *target);
+extern List *replace_qual_cached_expressions(List *quals);
+
#endif /* PLANNER_H */
diff --git a/src/include/optimizer/tlist.h b/src/include/optimizer/tlist.h
index ccb93d8..7488bd2 100644
--- a/src/include/optimizer/tlist.h
+++ b/src/include/optimizer/tlist.h
@@ -65,8 +65,12 @@ extern void split_pathtarget_at_srfs(PlannerInfo *root,
PathTarget *target, PathTarget *input_target,
List **targets, List **targets_contain_srfs);
-/* Convenience macro to get a PathTarget with valid cost/width fields */
+/*
+ * Convenience macro to get a PathTarget with valid cost/width fields and
+ * cached expressions.
+ */
#define create_pathtarget(root, tlist) \
- set_pathtarget_cost_width(root, make_pathtarget_from_tlist(tlist))
+ set_pathtarget_cost_width(root, replace_pathtarget_cached_expressions( \
+ make_pathtarget_from_tlist(tlist)))
#endif /* TLIST_H */
diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
index 7a40c99..2e27052 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -6535,6 +6535,16 @@ exec_simple_check_node(Node *node)
return TRUE;
}
+ case T_CachedExpr:
+ {
+ /*
+ * If CachedExpr will not be initialized by ExecInitCachedExpr
+ * possibly it will use cached value when it shouldn't (for
+ * example, snapshot has changed), so return false.
+ */
+ return FALSE;
+ }
+
case T_ScalarArrayOpExpr:
{
ScalarArrayOpExpr *expr = (ScalarArrayOpExpr *) node;
diff --git a/src/test/regress/expected/precalculate_stable_functions.out b/src/test/regress/expected/precalculate_stable_functions.out
new file mode 100644
index 0000000..093e6f8
--- /dev/null
+++ b/src/test/regress/expected/precalculate_stable_functions.out
@@ -0,0 +1,2625 @@
+--
+-- PRECALCULATE STABLE FUNCTIONS
+--
+-- Create types and tables for testing
+CREATE TYPE my_integer AS (value integer);
+CREATE TABLE two (i integer);
+INSERT INTO two VALUES (1), (2);
+-- Create volatile functions for testing
+CREATE OR REPLACE FUNCTION public.x_vlt (
+)
+RETURNS integer VOLATILE AS
+$body$
+BEGIN
+ RAISE NOTICE 'v';
+ RETURN 1;
+END;
+$body$
+LANGUAGE 'plpgsql';
+CREATE OR REPLACE FUNCTION public.equal_integers_vlt (
+ integer,
+ integer
+)
+RETURNS boolean VOLATILE AS
+$body$
+BEGIN
+ RAISE NOTICE 'equal integers volatile';
+ RETURN $1 = $2;
+END;
+$body$
+LANGUAGE 'plpgsql';
+CREATE OR REPLACE FUNCTION public.x_vlt_my_integer (
+)
+RETURNS my_integer VOLATILE AS
+$body$
+BEGIN
+ RAISE NOTICE 'v my_integer';
+ RETURN '(1)'::my_integer;
+END;
+$body$
+LANGUAGE 'plpgsql';
+CREATE OR REPLACE FUNCTION public.equal_my_integer_vlt (
+ my_integer,
+ my_integer
+)
+RETURNS boolean VOLATILE AS
+$body$
+BEGIN
+ RAISE NOTICE 'equal my_integer volatile';
+ RETURN $1.value = $2.value;
+END;
+$body$
+LANGUAGE 'plpgsql';
+CREATE OR REPLACE FUNCTION public.x_vlt_array_int (
+)
+RETURNS int[] VOLATILE AS
+$body$
+BEGIN
+ RAISE NOTICE 'v array_int';
+ RETURN '{2, 3}'::int[];
+END;
+$body$
+LANGUAGE 'plpgsql';
+-- Create stable functions for testing
+CREATE OR REPLACE FUNCTION public.x_stl (
+)
+RETURNS integer STABLE AS
+$body$
+BEGIN
+ RAISE NOTICE 's';
+ RETURN 1;
+END;
+$body$
+LANGUAGE 'plpgsql';
+CREATE OR REPLACE FUNCTION public.x_stl2 (
+ integer
+)
+RETURNS integer STABLE AS
+$body$
+BEGIN
+ RAISE NOTICE 's2';
+ RETURN $1;
+END;
+$body$
+LANGUAGE 'plpgsql';
+CREATE OR REPLACE FUNCTION public.x_stl2_strict (
+ integer
+)
+RETURNS integer STABLE STRICT AS
+$body$
+BEGIN
+ RAISE NOTICE 's2 strict';
+ RETURN $1;
+END;
+$body$
+LANGUAGE 'plpgsql';
+CREATE OR REPLACE FUNCTION public.equal_integers_stl (
+ integer,
+ integer
+)
+RETURNS boolean STABLE AS
+$body$
+BEGIN
+ RAISE NOTICE 'equal integers stable';
+ RETURN $1 = $2;
+END;
+$body$
+LANGUAGE 'plpgsql';
+CREATE OR REPLACE FUNCTION public.x_stl2_boolean (
+ boolean
+)
+RETURNS boolean STABLE AS
+$body$
+BEGIN
+ RAISE NOTICE 's2 boolean';
+ RETURN $1;
+END;
+$body$
+LANGUAGE 'plpgsql';
+CREATE OR REPLACE FUNCTION public.equal_booleans_stl_strict (
+ boolean,
+ boolean
+)
+RETURNS boolean STABLE STRICT AS
+$body$
+BEGIN
+ RAISE NOTICE 'equal booleans stable strict';
+ RETURN $1 = $2;
+END;
+$body$
+LANGUAGE 'plpgsql';
+CREATE OR REPLACE FUNCTION public.x_stl_my_integer (
+)
+RETURNS my_integer STABLE AS
+$body$
+BEGIN
+ RAISE NOTICE 's my_integer';
+ RETURN '(1)'::my_integer;
+END;
+$body$
+LANGUAGE 'plpgsql';
+CREATE OR REPLACE FUNCTION public.equal_my_integer_stl (
+ my_integer,
+ my_integer
+)
+RETURNS boolean STABLE AS
+$body$
+BEGIN
+ RAISE NOTICE 'equal my_integer stable';
+ RETURN $1.value = $2.value;
+END;
+$body$
+LANGUAGE 'plpgsql';
+CREATE OR REPLACE FUNCTION public.x_stl_array_int (
+)
+RETURNS int[] STABLE AS
+$body$
+BEGIN
+ RAISE NOTICE 's array_int';
+ RETURN '{2, 3}'::int[];
+END;
+$body$
+LANGUAGE 'plpgsql';
+CREATE OR REPLACE FUNCTION public.stable_max(
+)
+RETURNS integer STABLE AS
+$body$
+BEGIN
+ RETURN (SELECT max(i) from two);
+END
+$body$
+LANGUAGE 'plpgsql';
+CREATE OR REPLACE FUNCTION public.simple(
+)
+RETURNS integer STABLE AS
+$body$
+BEGIN
+ RETURN stable_max();
+END
+$body$
+LANGUAGE 'plpgsql';
+-- Create immutable functions for testing
+CREATE OR REPLACE FUNCTION public.x_imm2 (
+ integer
+)
+RETURNS integer IMMUTABLE AS
+$body$
+BEGIN
+ RAISE NOTICE 'i2';
+ RETURN $1;
+END;
+$body$
+LANGUAGE 'plpgsql';
+CREATE OR REPLACE FUNCTION public.x_imm2_strict (
+ integer
+)
+RETURNS integer IMMUTABLE STRICT AS
+$body$
+BEGIN
+ RAISE NOTICE 'i2 strict';
+ RETURN $1;
+END;
+$body$
+LANGUAGE 'plpgsql';
+CREATE OR REPLACE FUNCTION public.equal_integers_imm (
+ integer,
+ integer
+)
+RETURNS boolean IMMUTABLE AS
+$body$
+BEGIN
+ RAISE NOTICE 'equal integers immutable';
+ RETURN $1 = $2;
+END;
+$body$
+LANGUAGE 'plpgsql';
+CREATE OR REPLACE FUNCTION public.equal_my_integer_imm (
+ my_integer,
+ my_integer
+)
+RETURNS boolean IMMUTABLE AS
+$body$
+BEGIN
+ RAISE NOTICE 'equal my_integer immutable';
+ RETURN $1.value = $2.value;
+END;
+$body$
+LANGUAGE 'plpgsql';
+-- Create operators for testing
+CREATE operator === (
+ PROCEDURE = equal_integers_vlt,
+ LEFTARG = integer,
+ RIGHTARG = integer
+);
+CREATE operator ==== (
+ PROCEDURE = equal_integers_stl,
+ LEFTARG = integer,
+ RIGHTARG = integer
+);
+CREATE operator ===== (
+ PROCEDURE = equal_integers_imm,
+ LEFTARG = integer,
+ RIGHTARG = integer
+);
+CREATE operator ====== (
+ PROCEDURE = equal_booleans_stl_strict,
+ LEFTARG = boolean,
+ RIGHTARG = boolean
+);
+CREATE operator ==== (
+ PROCEDURE = equal_my_integer_stl,
+ LEFTARG = my_integer,
+ RIGHTARG = my_integer
+);
+-- Simple functions testing
+SELECT x_vlt() FROM generate_series(1, 3) x; -- should not be precalculated
+NOTICE: v
+NOTICE: v
+NOTICE: v
+ x_vlt
+-------
+ 1
+ 1
+ 1
+(3 rows)
+
+SELECT x_stl() FROM generate_series(1, 3) x;
+NOTICE: s
+ x_stl
+-------
+ 1
+ 1
+ 1
+(3 rows)
+
+-- WHERE clause testing
+SELECT x_vlt() FROM generate_series(1, 4) x WHERE x_vlt() < x; -- should not be precalculated
+NOTICE: v
+NOTICE: v
+NOTICE: v
+NOTICE: v
+NOTICE: v
+NOTICE: v
+NOTICE: v
+ x_vlt
+-------
+ 1
+ 1
+ 1
+(3 rows)
+
+SELECT x_stl() FROM generate_series(1, 4) x WHERE x_stl() < x;
+NOTICE: s
+NOTICE: s
+NOTICE: s
+ x_stl
+-------
+ 1
+ 1
+ 1
+(3 rows)
+
+-- Functions with constant arguments and nested functions testing
+SELECT x_stl2(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
+NOTICE: v
+NOTICE: s2
+NOTICE: v
+NOTICE: s2
+NOTICE: v
+NOTICE: s2
+NOTICE: v
+NOTICE: s2
+ x_stl2
+--------
+ 1
+ 1
+ 1
+ 1
+(4 rows)
+
+SELECT x_imm2(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
+NOTICE: v
+NOTICE: i2
+NOTICE: v
+NOTICE: i2
+NOTICE: v
+NOTICE: i2
+NOTICE: v
+NOTICE: i2
+ x_imm2
+--------
+ 1
+ 1
+ 1
+ 1
+(4 rows)
+
+SELECT x_stl2(x_stl2(1)) FROM generate_series(1, 4) x;
+NOTICE: s2
+NOTICE: s2
+ x_stl2
+--------
+ 1
+ 1
+ 1
+ 1
+(4 rows)
+
+SELECT x_imm2(x_stl2(1)) FROM generate_series(1, 4) x;
+NOTICE: s2
+NOTICE: i2
+ x_imm2
+--------
+ 1
+ 1
+ 1
+ 1
+(4 rows)
+
+-- Strict functions testing
+SELECT x_stl2_strict(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
+NOTICE: v
+NOTICE: s2 strict
+NOTICE: v
+NOTICE: s2 strict
+NOTICE: v
+NOTICE: s2 strict
+NOTICE: v
+NOTICE: s2 strict
+ x_stl2_strict
+---------------
+ 1
+ 1
+ 1
+ 1
+(4 rows)
+
+SELECT x_imm2_strict(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
+NOTICE: v
+NOTICE: i2 strict
+NOTICE: v
+NOTICE: i2 strict
+NOTICE: v
+NOTICE: i2 strict
+NOTICE: v
+NOTICE: i2 strict
+ x_imm2_strict
+---------------
+ 1
+ 1
+ 1
+ 1
+(4 rows)
+
+SELECT x_stl2_strict(x_stl2_strict(1)) FROM generate_series(1, 4) x;
+NOTICE: s2 strict
+NOTICE: s2 strict
+ x_stl2_strict
+---------------
+ 1
+ 1
+ 1
+ 1
+(4 rows)
+
+SELECT x_imm2_strict(x_stl2_strict(1)) FROM generate_series(1, 4) x;
+NOTICE: s2 strict
+NOTICE: i2 strict
+ x_imm2_strict
+---------------
+ 1
+ 1
+ 1
+ 1
+(4 rows)
+
+-- Strict functions with null arguments testing
+SELECT x_stl2_strict(x_stl2(NULL)) FROM generate_series(1, 4) x;
+NOTICE: s2
+ x_stl2_strict
+---------------
+
+
+
+
+(4 rows)
+
+SELECT x_imm2_strict(x_stl2(NULL)) FROM generate_series(1, 4) x;
+NOTICE: s2
+ x_imm2_strict
+---------------
+
+
+
+
+(4 rows)
+
+-- Operators testing
+SELECT 1 === 2 FROM generate_series(1, 4) x; -- should not be precalculated
+NOTICE: equal integers volatile
+NOTICE: equal integers volatile
+NOTICE: equal integers volatile
+NOTICE: equal integers volatile
+ ?column?
+----------
+ f
+ f
+ f
+ f
+(4 rows)
+
+SELECT 1 ==== 2 FROM generate_series(1, 4) x;
+NOTICE: equal integers stable
+ ?column?
+----------
+ f
+ f
+ f
+ f
+(4 rows)
+
+-- Nested and strict operators testing
+-- (also partly mixed functions and operators testing)
+SELECT (x_vlt() ==== 2) ====== (x_vlt() ===== 3) FROM generate_series(1, 4) x; -- should not be precalculated
+NOTICE: v
+NOTICE: equal integers stable
+NOTICE: v
+NOTICE: equal integers immutable
+NOTICE: equal booleans stable strict
+NOTICE: v
+NOTICE: equal integers stable
+NOTICE: v
+NOTICE: equal integers immutable
+NOTICE: equal booleans stable strict
+NOTICE: v
+NOTICE: equal integers stable
+NOTICE: v
+NOTICE: equal integers immutable
+NOTICE: equal booleans stable strict
+NOTICE: v
+NOTICE: equal integers stable
+NOTICE: v
+NOTICE: equal integers immutable
+NOTICE: equal booleans stable strict
+ ?column?
+----------
+ t
+ t
+ t
+ t
+(4 rows)
+
+SELECT (x_stl() ==== 2) ====== (x_stl() ===== 3) FROM generate_series(1, 4) x;
+NOTICE: s
+NOTICE: equal integers stable
+NOTICE: s
+NOTICE: equal integers immutable
+NOTICE: equal booleans stable strict
+ ?column?
+----------
+ t
+ t
+ t
+ t
+(4 rows)
+
+SELECT (1 ==== 2) ====== x_stl2_boolean(NULL) FROM generate_series(1, 4) x;
+NOTICE: equal integers stable
+NOTICE: s2 boolean
+ ?column?
+----------
+
+
+
+
+(4 rows)
+
+-- IS DISTINCT FROM expression testing
+-- create operator here because we will drop and reuse it several times
+CREATE OPERATOR = (
+ PROCEDURE = equal_my_integer_vlt,
+ LEFTARG = my_integer,
+ RIGHTARG = my_integer
+);
+-- should not be precalculated
+SELECT '(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer
+FROM generate_series(1, 4) x;
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+ ?column?
+----------
+ t
+ t
+ t
+ t
+(4 rows)
+
+DROP OPERATOR = (my_integer, my_integer);
+CREATE OPERATOR = (
+ PROCEDURE = equal_my_integer_stl,
+ LEFTARG = my_integer,
+ RIGHTARG = my_integer
+);
+SELECT '(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer
+FROM generate_series(1, 4) x;
+NOTICE: equal my_integer stable
+ ?column?
+----------
+ t
+ t
+ t
+ t
+(4 rows)
+
+-- IS DISTINCT FROM expressions with null arguments testing
+SELECT x_stl2_boolean(1 IS DISTINCT FROM x_stl2(NULL))
+FROM generate_series(1, 4) x;
+NOTICE: s2
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+ t
+ t
+ t
+ t
+(4 rows)
+
+SELECT x_stl2_boolean(x_stl2(NULL) IS DISTINCT FROM x_stl2(NULL))
+FROM generate_series(1, 4) x;
+NOTICE: s2
+NOTICE: s2
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+ f
+ f
+ f
+ f
+(4 rows)
+
+-- Nested IS DISTINCT FROM expression testing
+DROP OPERATOR = (my_integer, my_integer);
+CREATE OPERATOR = (
+ PROCEDURE = equal_my_integer_vlt,
+ LEFTARG = my_integer,
+ RIGHTARG = my_integer
+);
+-- should not be precalculated
+SELECT x_stl2_boolean(
+ ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) IS DISTINCT FROM
+ TRUE
+)
+FROM generate_series(1, 4) x;
+NOTICE: equal my_integer volatile
+NOTICE: s2 boolean
+NOTICE: equal my_integer volatile
+NOTICE: s2 boolean
+NOTICE: equal my_integer volatile
+NOTICE: s2 boolean
+NOTICE: equal my_integer volatile
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+ f
+ f
+ f
+ f
+(4 rows)
+
+DROP OPERATOR = (my_integer, my_integer);
+CREATE OPERATOR = (
+ PROCEDURE = equal_my_integer_stl,
+ LEFTARG = my_integer,
+ RIGHTARG = my_integer
+);
+SELECT x_stl2_boolean(
+ ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) IS DISTINCT FROM
+ TRUE
+)
+FROM generate_series(1, 4) x;
+NOTICE: equal my_integer stable
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+ f
+ f
+ f
+ f
+(4 rows)
+
+-- NULLIF expressions testing
+DROP OPERATOR = (my_integer, my_integer);
+CREATE OPERATOR = (
+ PROCEDURE = equal_my_integer_vlt,
+ LEFTARG = my_integer,
+ RIGHTARG = my_integer
+);
+-- should not be precalculated
+SELECT NULLIF('(1)'::my_integer, '(2)'::my_integer)
+FROM generate_series(1, 4) x;
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+ nullif
+--------
+ (1)
+ (1)
+ (1)
+ (1)
+(4 rows)
+
+DROP OPERATOR = (my_integer, my_integer);
+CREATE OPERATOR = (
+ PROCEDURE = equal_my_integer_stl,
+ LEFTARG = my_integer,
+ RIGHTARG = my_integer
+);
+SELECT NULLIF('(1)'::my_integer, '(2)'::my_integer)
+FROM generate_series(1, 4) x;
+NOTICE: equal my_integer stable
+ nullif
+--------
+ (1)
+ (1)
+ (1)
+ (1)
+(4 rows)
+
+-- NULLIF expressions with null arguments testing
+SELECT x_stl2(NULLIF(1, x_stl2(NULL))) FROM generate_series(1, 4) x;
+NOTICE: s2
+NOTICE: s2
+ x_stl2
+--------
+ 1
+ 1
+ 1
+ 1
+(4 rows)
+
+SELECT x_stl2(NULLIF(x_stl2(NULL), x_stl2(NULL))) FROM generate_series(1, 4) x;
+NOTICE: s2
+NOTICE: s2
+NOTICE: s2
+ x_stl2
+--------
+
+
+
+
+(4 rows)
+
+-- Nested NULLIF expression testing
+-- should not be precalculated
+SELECT NULLIF(NULLIF(x_vlt_my_integer(), '(2)'::my_integer), '(2)'::my_integer)
+FROM generate_series(1, 4) x;
+NOTICE: v my_integer
+NOTICE: equal my_integer stable
+NOTICE: equal my_integer stable
+NOTICE: v my_integer
+NOTICE: equal my_integer stable
+NOTICE: equal my_integer stable
+NOTICE: v my_integer
+NOTICE: equal my_integer stable
+NOTICE: equal my_integer stable
+NOTICE: v my_integer
+NOTICE: equal my_integer stable
+NOTICE: equal my_integer stable
+ nullif
+--------
+ (1)
+ (1)
+ (1)
+ (1)
+(4 rows)
+
+SELECT NULLIF(NULLIF(x_stl_my_integer(), '(2)'::my_integer), '(2)'::my_integer)
+FROM generate_series(1, 4) x;
+NOTICE: s my_integer
+NOTICE: equal my_integer stable
+NOTICE: equal my_integer stable
+ nullif
+--------
+ (1)
+ (1)
+ (1)
+ (1)
+(4 rows)
+
+DROP OPERATOR = (my_integer, my_integer);
+CREATE OPERATOR = (
+ PROCEDURE = equal_my_integer_imm,
+ LEFTARG = my_integer,
+ RIGHTARG = my_integer
+);
+-- should not be precalculated
+SELECT NULLIF(NULLIF(x_vlt_my_integer(), '(2)'::my_integer), '(2)'::my_integer)
+FROM generate_series(1, 4) x;
+NOTICE: v my_integer
+NOTICE: equal my_integer immutable
+NOTICE: equal my_integer immutable
+NOTICE: v my_integer
+NOTICE: equal my_integer immutable
+NOTICE: equal my_integer immutable
+NOTICE: v my_integer
+NOTICE: equal my_integer immutable
+NOTICE: equal my_integer immutable
+NOTICE: v my_integer
+NOTICE: equal my_integer immutable
+NOTICE: equal my_integer immutable
+ nullif
+--------
+ (1)
+ (1)
+ (1)
+ (1)
+(4 rows)
+
+SELECT NULLIF(NULLIF(x_stl_my_integer(), '(2)'::my_integer), '(2)'::my_integer)
+FROM generate_series(1, 4) x;
+NOTICE: s my_integer
+NOTICE: equal my_integer immutable
+NOTICE: equal my_integer immutable
+ nullif
+--------
+ (1)
+ (1)
+ (1)
+ (1)
+(4 rows)
+
+-- "scalar op ANY/ALL (array)" / "scalar IN (2 or more values)" expressions
+-- testing
+SELECT 1 === ANY('{2, 3}') FROM generate_series(1, 4) x; -- should not be precalculated
+NOTICE: equal integers volatile
+NOTICE: equal integers volatile
+NOTICE: equal integers volatile
+NOTICE: equal integers volatile
+NOTICE: equal integers volatile
+NOTICE: equal integers volatile
+NOTICE: equal integers volatile
+NOTICE: equal integers volatile
+ ?column?
+----------
+ f
+ f
+ f
+ f
+(4 rows)
+
+SELECT 1 === ALL('{2, 3}') FROM generate_series(1, 4) x; -- should not be precalculated
+NOTICE: equal integers volatile
+NOTICE: equal integers volatile
+NOTICE: equal integers volatile
+NOTICE: equal integers volatile
+ ?column?
+----------
+ f
+ f
+ f
+ f
+(4 rows)
+
+DROP OPERATOR = (my_integer, my_integer);
+CREATE OPERATOR = (
+ PROCEDURE = equal_my_integer_vlt,
+ LEFTARG = my_integer,
+ RIGHTARG = my_integer
+);
+-- should not be precalculated
+SELECT '(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer)
+FROM generate_series(1, 4) x;
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+ ?column?
+----------
+ f
+ f
+ f
+ f
+(4 rows)
+
+SELECT 1 ==== ANY('{2, 3}') FROM generate_series(1, 4) x;
+NOTICE: equal integers stable
+NOTICE: equal integers stable
+ ?column?
+----------
+ f
+ f
+ f
+ f
+(4 rows)
+
+SELECT 1 ==== ALL('{2, 3}') FROM generate_series(1, 4) x;
+NOTICE: equal integers stable
+ ?column?
+----------
+ f
+ f
+ f
+ f
+(4 rows)
+
+DROP OPERATOR = (my_integer, my_integer);
+CREATE OPERATOR = (
+ PROCEDURE = equal_my_integer_stl,
+ LEFTARG = my_integer,
+ RIGHTARG = my_integer
+);
+SELECT '(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer)
+FROM generate_series(1, 4) x;
+NOTICE: equal my_integer stable
+NOTICE: equal my_integer stable
+ ?column?
+----------
+ f
+ f
+ f
+ f
+(4 rows)
+
+-- "scalar op ANY/ALL (array)" / "scalar IN (2 or more values)" expressions with
+-- null arguments testing
+SELECT 1 ==== ANY('{2, NULL}') FROM generate_series(1, 4) x;
+NOTICE: equal integers stable
+NOTICE: equal integers stable
+ ?column?
+----------
+
+
+
+
+(4 rows)
+
+SELECT x_stl2_boolean(1 ==== ANY(NULL)) FROM generate_series(1, 4) x;
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+
+
+
+
+(4 rows)
+
+SELECT NULL ==== ANY('{2, 3}'::int[]) FROM generate_series(1, 4) x;
+NOTICE: equal integers stable
+NOTICE: equal integers stable
+ ?column?
+----------
+
+
+
+
+(4 rows)
+
+SELECT NULL ==== ANY('{2, NULL}'::int[]) FROM generate_series(1, 4) x;
+NOTICE: equal integers stable
+NOTICE: equal integers stable
+ ?column?
+----------
+
+
+
+
+(4 rows)
+
+SELECT x_stl2_boolean(NULL::int ==== ANY(NULL)) FROM generate_series(1, 4) x;
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+
+
+
+
+(4 rows)
+
+SELECT 1 ==== ALL('{2, NULL}') FROM generate_series(1, 4) x;
+NOTICE: equal integers stable
+ ?column?
+----------
+ f
+ f
+ f
+ f
+(4 rows)
+
+SELECT x_stl2_boolean(1 ==== ALL(NULL)) FROM generate_series(1, 4) x;
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+
+
+
+
+(4 rows)
+
+SELECT NULL ==== ALL('{2, 3}'::int[]) FROM generate_series(1, 4) x;
+NOTICE: equal integers stable
+NOTICE: equal integers stable
+ ?column?
+----------
+
+
+
+
+(4 rows)
+
+SELECT NULL ==== ALL('{2, NULL}'::int[]) FROM generate_series(1, 4) x;
+NOTICE: equal integers stable
+NOTICE: equal integers stable
+ ?column?
+----------
+
+
+
+
+(4 rows)
+
+SELECT x_stl2_boolean(NULL::int ==== ALL(NULL)) FROM generate_series(1, 4) x;
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+
+
+
+
+(4 rows)
+
+SELECT x_stl2_boolean(1 IN (2, NULL)) FROM generate_series(1, 4) x;
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+
+
+
+
+(4 rows)
+
+SELECT x_stl2_boolean(NULL IN (2, 3)) FROM generate_series(1, 4) x;
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+
+
+
+
+(4 rows)
+
+SELECT x_stl2_boolean(NULL IN (2, NULL)) FROM generate_series(1, 4) x;
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+
+
+
+
+(4 rows)
+
+-- Nesting "scalar op ANY/ALL (array)" / "scalar IN (2 or more values)"
+-- expressions testing (also partly mixed functions and "scalar op ANY/ALL
+-- (array)" / "scalar IN (2 or more values)" expressions testing)
+-- should not be precalculated
+SELECT x_stl2_boolean((x_vlt() ==== ANY('{2, 3}')) = ANY('{TRUE}'))
+FROM generate_series(1, 4) x;
+NOTICE: v
+NOTICE: equal integers stable
+NOTICE: equal integers stable
+NOTICE: s2 boolean
+NOTICE: v
+NOTICE: equal integers stable
+NOTICE: equal integers stable
+NOTICE: s2 boolean
+NOTICE: v
+NOTICE: equal integers stable
+NOTICE: equal integers stable
+NOTICE: s2 boolean
+NOTICE: v
+NOTICE: equal integers stable
+NOTICE: equal integers stable
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+ f
+ f
+ f
+ f
+(4 rows)
+
+-- should not be precalculated
+SELECT x_stl2_boolean((x_vlt() ==== ANY('{2, 3}')) = ALL('{TRUE}'))
+FROM generate_series(1, 4) x;
+NOTICE: v
+NOTICE: equal integers stable
+NOTICE: equal integers stable
+NOTICE: s2 boolean
+NOTICE: v
+NOTICE: equal integers stable
+NOTICE: equal integers stable
+NOTICE: s2 boolean
+NOTICE: v
+NOTICE: equal integers stable
+NOTICE: equal integers stable
+NOTICE: s2 boolean
+NOTICE: v
+NOTICE: equal integers stable
+NOTICE: equal integers stable
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+ f
+ f
+ f
+ f
+(4 rows)
+
+-- should not be precalculated
+SELECT x_stl2_boolean((x_vlt() ==== ANY('{2, 3}')) IN (TRUE, FALSE))
+FROM generate_series(1, 4) x;
+NOTICE: v
+NOTICE: equal integers stable
+NOTICE: equal integers stable
+NOTICE: s2 boolean
+NOTICE: v
+NOTICE: equal integers stable
+NOTICE: equal integers stable
+NOTICE: s2 boolean
+NOTICE: v
+NOTICE: equal integers stable
+NOTICE: equal integers stable
+NOTICE: s2 boolean
+NOTICE: v
+NOTICE: equal integers stable
+NOTICE: equal integers stable
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+ t
+ t
+ t
+ t
+(4 rows)
+
+SELECT x_stl2_boolean((x_stl() ==== ANY('{2, 3}')) = ANY('{TRUE}'))
+FROM generate_series(1, 4) x;
+NOTICE: s
+NOTICE: equal integers stable
+NOTICE: equal integers stable
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+ f
+ f
+ f
+ f
+(4 rows)
+
+SELECT x_stl2_boolean((x_stl() ==== ANY('{2, 3}')) = ALL('{TRUE}'))
+FROM generate_series(1, 4) x;
+NOTICE: s
+NOTICE: equal integers stable
+NOTICE: equal integers stable
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+ f
+ f
+ f
+ f
+(4 rows)
+
+SELECT x_stl2_boolean((x_stl() ==== ANY('{2, 3}')) IN (TRUE, FALSE))
+FROM generate_series(1, 4) x;
+NOTICE: s
+NOTICE: equal integers stable
+NOTICE: equal integers stable
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+ t
+ t
+ t
+ t
+(4 rows)
+
+-- should not be precalculated
+SELECT x_stl2_boolean((x_vlt() ===== ANY('{2, 3}')) = ANY('{TRUE}'))
+FROM generate_series(1, 4) x;
+NOTICE: v
+NOTICE: equal integers immutable
+NOTICE: equal integers immutable
+NOTICE: s2 boolean
+NOTICE: v
+NOTICE: equal integers immutable
+NOTICE: equal integers immutable
+NOTICE: s2 boolean
+NOTICE: v
+NOTICE: equal integers immutable
+NOTICE: equal integers immutable
+NOTICE: s2 boolean
+NOTICE: v
+NOTICE: equal integers immutable
+NOTICE: equal integers immutable
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+ f
+ f
+ f
+ f
+(4 rows)
+
+-- should not be precalculated
+SELECT x_stl2_boolean((x_vlt() ===== ANY('{2, 3}')) = ALL('{TRUE}'))
+FROM generate_series(1, 4) x;
+NOTICE: v
+NOTICE: equal integers immutable
+NOTICE: equal integers immutable
+NOTICE: s2 boolean
+NOTICE: v
+NOTICE: equal integers immutable
+NOTICE: equal integers immutable
+NOTICE: s2 boolean
+NOTICE: v
+NOTICE: equal integers immutable
+NOTICE: equal integers immutable
+NOTICE: s2 boolean
+NOTICE: v
+NOTICE: equal integers immutable
+NOTICE: equal integers immutable
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+ f
+ f
+ f
+ f
+(4 rows)
+
+-- should not be precalculated
+SELECT x_stl2_boolean((x_vlt() ===== ANY('{2, 3}')) IN (TRUE, FALSE))
+FROM generate_series(1, 4) x;
+NOTICE: v
+NOTICE: equal integers immutable
+NOTICE: equal integers immutable
+NOTICE: s2 boolean
+NOTICE: v
+NOTICE: equal integers immutable
+NOTICE: equal integers immutable
+NOTICE: s2 boolean
+NOTICE: v
+NOTICE: equal integers immutable
+NOTICE: equal integers immutable
+NOTICE: s2 boolean
+NOTICE: v
+NOTICE: equal integers immutable
+NOTICE: equal integers immutable
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+ t
+ t
+ t
+ t
+(4 rows)
+
+SELECT x_stl2_boolean((x_stl() ===== ANY('{2, 3}')) = ANY('{TRUE}'))
+FROM generate_series(1, 4) x;
+NOTICE: s
+NOTICE: equal integers immutable
+NOTICE: equal integers immutable
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+ f
+ f
+ f
+ f
+(4 rows)
+
+SELECT x_stl2_boolean((x_stl() ===== ANY('{2, 3}')) = ALL('{TRUE}'))
+FROM generate_series(1, 4) x;
+NOTICE: s
+NOTICE: equal integers immutable
+NOTICE: equal integers immutable
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+ f
+ f
+ f
+ f
+(4 rows)
+
+SELECT x_stl2_boolean((x_stl() ===== ANY('{2, 3}')) IN (TRUE, FALSE))
+FROM generate_series(1, 4) x;
+NOTICE: s
+NOTICE: equal integers immutable
+NOTICE: equal integers immutable
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+ t
+ t
+ t
+ t
+(4 rows)
+
+-- Mixed functions and operators testing
+-- (most of it was earlier in Nested and strict operators testing)
+SELECT x_stl2_boolean(1 ==== 2) FROM generate_series(1, 4) x;
+NOTICE: equal integers stable
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+ f
+ f
+ f
+ f
+(4 rows)
+
+-- Mixed functions and IS DISTINCT FROM expressions testing
+DROP OPERATOR = (my_integer, my_integer);
+CREATE OPERATOR = (
+ PROCEDURE = equal_my_integer_vlt,
+ LEFTARG = my_integer,
+ RIGHTARG = my_integer
+);
+-- should not be precalculated
+SELECT equal_booleans_stl_strict(
+ (x_stl_my_integer() IS DISTINCT FROM '(1)'::my_integer),
+ (x_stl_my_integer() IS DISTINCT FROM '(2)'::my_integer)
+)
+FROM generate_series(1, 4) x;
+NOTICE: s my_integer
+NOTICE: equal my_integer volatile
+NOTICE: s my_integer
+NOTICE: equal my_integer volatile
+NOTICE: equal booleans stable strict
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal booleans stable strict
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal booleans stable strict
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal booleans stable strict
+ equal_booleans_stl_strict
+---------------------------
+ f
+ f
+ f
+ f
+(4 rows)
+
+SELECT equal_booleans_stl_strict(
+ (x_stl() IS DISTINCT FROM 1),
+ (x_stl() IS DISTINCT FROM 2)
+)
+FROM generate_series(1, 4) x;
+NOTICE: s
+NOTICE: s
+NOTICE: equal booleans stable strict
+ equal_booleans_stl_strict
+---------------------------
+ f
+ f
+ f
+ f
+(4 rows)
+
+-- Mixed functions and NULLIF expressions testing
+-- should not be precalculated
+SELECT equal_my_integer_stl(
+ NULLIF(x_stl_my_integer(), '(1)'::my_integer),
+ NULLIF(x_stl_my_integer(), '(2)'::my_integer)
+)
+FROM generate_series(1, 4) x;
+NOTICE: s my_integer
+NOTICE: equal my_integer volatile
+NOTICE: s my_integer
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer stable
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer stable
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer stable
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer stable
+ equal_my_integer_stl
+----------------------
+
+
+
+
+(4 rows)
+
+SELECT equal_integers_stl(NULLIF(x_stl(), 1), NULLIF(x_stl(), 2))
+FROM generate_series(1, 4) x;
+NOTICE: s
+NOTICE: s
+NOTICE: equal integers stable
+ equal_integers_stl
+--------------------
+
+
+
+
+(4 rows)
+
+-- Mixed functions and "scalar op ANY/ALL (array)" / "scalar IN (2 or more
+-- values)" expressions testing (partly in nesting "scalar op ANY/ALL (array)" /
+-- "scalar IN (2 or more values)" expressions testing)
+SELECT 1 ==== ANY(x_vlt_array_int()) FROM generate_series(1, 4) x; -- should not be precalculated
+NOTICE: v array_int
+NOTICE: equal integers stable
+NOTICE: equal integers stable
+NOTICE: v array_int
+NOTICE: equal integers stable
+NOTICE: equal integers stable
+NOTICE: v array_int
+NOTICE: equal integers stable
+NOTICE: equal integers stable
+NOTICE: v array_int
+NOTICE: equal integers stable
+NOTICE: equal integers stable
+ ?column?
+----------
+ f
+ f
+ f
+ f
+(4 rows)
+
+SELECT 1 ==== ALL(x_vlt_array_int()) FROM generate_series(1, 4) x; -- should not be precalculated
+NOTICE: v array_int
+NOTICE: equal integers stable
+NOTICE: v array_int
+NOTICE: equal integers stable
+NOTICE: v array_int
+NOTICE: equal integers stable
+NOTICE: v array_int
+NOTICE: equal integers stable
+ ?column?
+----------
+ f
+ f
+ f
+ f
+(4 rows)
+
+SELECT 1 ==== ANY(x_stl_array_int()) FROM generate_series(1, 4) x;
+NOTICE: s array_int
+NOTICE: equal integers stable
+NOTICE: equal integers stable
+ ?column?
+----------
+ f
+ f
+ f
+ f
+(4 rows)
+
+SELECT 1 ==== ALL(x_stl_array_int()) FROM generate_series(1, 4) x;
+NOTICE: s array_int
+NOTICE: equal integers stable
+ ?column?
+----------
+ f
+ f
+ f
+ f
+(4 rows)
+
+-- Mixed operators and IS DISTINCT FROM expressions testing
+-- should not be precalculated
+SELECT (
+ ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) ======
+ ('(2)'::my_integer IS DISTINCT FROM '(2)'::my_integer)
+)
+FROM generate_series(1, 4) x;
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal booleans stable strict
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal booleans stable strict
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal booleans stable strict
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal booleans stable strict
+ ?column?
+----------
+ f
+ f
+ f
+ f
+(4 rows)
+
+-- should not be precalculated
+SELECT x_stl2_boolean((1 === 2) IS DISTINCT FROM TRUE)
+FROM generate_series(1, 4) x;
+NOTICE: equal integers volatile
+NOTICE: s2 boolean
+NOTICE: equal integers volatile
+NOTICE: s2 boolean
+NOTICE: equal integers volatile
+NOTICE: s2 boolean
+NOTICE: equal integers volatile
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+ t
+ t
+ t
+ t
+(4 rows)
+
+DROP OPERATOR = (my_integer, my_integer);
+CREATE OPERATOR = (
+ PROCEDURE = equal_my_integer_stl,
+ LEFTARG = my_integer,
+ RIGHTARG = my_integer
+);
+SELECT (
+ ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) ======
+ ('(2)'::my_integer IS DISTINCT FROM '(2)'::my_integer)
+)
+FROM generate_series(1, 4) x;
+NOTICE: equal my_integer stable
+NOTICE: equal my_integer stable
+NOTICE: equal booleans stable strict
+ ?column?
+----------
+ f
+ f
+ f
+ f
+(4 rows)
+
+SELECT x_stl2_boolean((1 ==== 2) IS DISTINCT FROM TRUE)
+FROM generate_series(1, 4) x;
+NOTICE: equal integers stable
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+ t
+ t
+ t
+ t
+(4 rows)
+
+-- Mixed operators and NULLIF expressions testing
+DROP OPERATOR = (my_integer, my_integer);
+CREATE OPERATOR = (
+ PROCEDURE = equal_my_integer_vlt,
+ LEFTARG = my_integer,
+ RIGHTARG = my_integer
+);
+-- should not be precalculated
+SELECT (
+ NULLIF('(1)'::my_integer, '(2)'::my_integer) ====
+ NULLIF('(2)'::my_integer, '(2)'::my_integer)
+)
+FROM generate_series(1, 4) x;
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer stable
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer stable
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer stable
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer stable
+ ?column?
+----------
+
+
+
+
+(4 rows)
+
+-- should not be precalculated
+SELECT x_stl2_boolean(NULLIF(1 === 2, TRUE)) FROM generate_series(1, 4) x;
+NOTICE: equal integers volatile
+NOTICE: s2 boolean
+NOTICE: equal integers volatile
+NOTICE: s2 boolean
+NOTICE: equal integers volatile
+NOTICE: s2 boolean
+NOTICE: equal integers volatile
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+ f
+ f
+ f
+ f
+(4 rows)
+
+DROP OPERATOR = (my_integer, my_integer);
+CREATE OPERATOR = (
+ PROCEDURE = equal_my_integer_stl,
+ LEFTARG = my_integer,
+ RIGHTARG = my_integer
+);
+SELECT (
+ NULLIF('(1)'::my_integer, '(2)'::my_integer) ====
+ NULLIF('(2)'::my_integer, '(2)'::my_integer)
+)
+FROM generate_series(1, 4) x;
+NOTICE: equal my_integer stable
+NOTICE: equal my_integer stable
+NOTICE: equal my_integer stable
+ ?column?
+----------
+
+
+
+
+(4 rows)
+
+SELECT x_stl2_boolean(NULLIF(1 ==== 2, TRUE)) FROM generate_series(1, 4) x;
+NOTICE: equal integers stable
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+ f
+ f
+ f
+ f
+(4 rows)
+
+-- Mixed operators and "scalar op ANY/ALL (array)" / "scalar IN (2 or more
+-- values)" expressions testing
+-- should not be precalculated
+SELECT (1 === ANY('{2, 3}')) ====== (1 === ALL('{2, 3}'))
+FROM generate_series(1, 4) x;
+NOTICE: equal integers volatile
+NOTICE: equal integers volatile
+NOTICE: equal integers volatile
+NOTICE: equal booleans stable strict
+NOTICE: equal integers volatile
+NOTICE: equal integers volatile
+NOTICE: equal integers volatile
+NOTICE: equal booleans stable strict
+NOTICE: equal integers volatile
+NOTICE: equal integers volatile
+NOTICE: equal integers volatile
+NOTICE: equal booleans stable strict
+NOTICE: equal integers volatile
+NOTICE: equal integers volatile
+NOTICE: equal integers volatile
+NOTICE: equal booleans stable strict
+ ?column?
+----------
+ t
+ t
+ t
+ t
+(4 rows)
+
+DROP OPERATOR = (my_integer, my_integer);
+CREATE OPERATOR = (
+ PROCEDURE = equal_my_integer_vlt,
+ LEFTARG = my_integer,
+ RIGHTARG = my_integer
+);
+-- should not be precalculated
+SELECT ('(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer)) ====== TRUE
+FROM generate_series(1, 4) x;
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal booleans stable strict
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal booleans stable strict
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal booleans stable strict
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal booleans stable strict
+ ?column?
+----------
+ f
+ f
+ f
+ f
+(4 rows)
+
+-- should not be precalculated
+SELECT x_stl2_boolean((1 === 2) = ANY('{TRUE}'))
+FROM generate_series(1, 4) x;
+NOTICE: equal integers volatile
+NOTICE: s2 boolean
+NOTICE: equal integers volatile
+NOTICE: s2 boolean
+NOTICE: equal integers volatile
+NOTICE: s2 boolean
+NOTICE: equal integers volatile
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+ f
+ f
+ f
+ f
+(4 rows)
+
+-- should not be precalculated
+SELECT x_stl2_boolean((1 === 2) = ALL('{TRUE}'))
+FROM generate_series(1, 4) x;
+NOTICE: equal integers volatile
+NOTICE: s2 boolean
+NOTICE: equal integers volatile
+NOTICE: s2 boolean
+NOTICE: equal integers volatile
+NOTICE: s2 boolean
+NOTICE: equal integers volatile
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+ f
+ f
+ f
+ f
+(4 rows)
+
+-- should not be precalculated
+SELECT x_stl2_boolean((1 === 2) IN (TRUE, FALSE))
+FROM generate_series(1, 4) x;
+NOTICE: equal integers volatile
+NOTICE: s2 boolean
+NOTICE: equal integers volatile
+NOTICE: s2 boolean
+NOTICE: equal integers volatile
+NOTICE: s2 boolean
+NOTICE: equal integers volatile
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+ t
+ t
+ t
+ t
+(4 rows)
+
+SELECT (1 ==== ANY('{2, 3}')) ====== (1 ==== ALL('{2, 3}'))
+FROM generate_series(1, 4) x;
+NOTICE: equal integers stable
+NOTICE: equal integers stable
+NOTICE: equal integers stable
+NOTICE: equal booleans stable strict
+ ?column?
+----------
+ t
+ t
+ t
+ t
+(4 rows)
+
+DROP OPERATOR = (my_integer, my_integer);
+CREATE OPERATOR = (
+ PROCEDURE = equal_my_integer_stl,
+ LEFTARG = my_integer,
+ RIGHTARG = my_integer
+);
+SELECT ('(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer)) ====== TRUE
+FROM generate_series(1, 4) x;
+NOTICE: equal my_integer stable
+NOTICE: equal my_integer stable
+NOTICE: equal booleans stable strict
+ ?column?
+----------
+ f
+ f
+ f
+ f
+(4 rows)
+
+SELECT x_stl2_boolean((1 ==== 2) = ANY('{TRUE}'))
+FROM generate_series(1, 4) x;
+NOTICE: equal integers stable
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+ f
+ f
+ f
+ f
+(4 rows)
+
+SELECT x_stl2_boolean((1 ==== 2) = ALL('{TRUE}'))
+FROM generate_series(1, 4) x;
+NOTICE: equal integers stable
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+ f
+ f
+ f
+ f
+(4 rows)
+
+SELECT x_stl2_boolean((1 ==== 2) IN (TRUE, FALSE))
+FROM generate_series(1, 4) x;
+NOTICE: equal integers stable
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+ t
+ t
+ t
+ t
+(4 rows)
+
+-- Mixed IS DISTINCT FROM and NULLIF expressions testing
+DROP OPERATOR = (my_integer, my_integer);
+CREATE OPERATOR = (
+ PROCEDURE = equal_my_integer_vlt,
+ LEFTARG = my_integer,
+ RIGHTARG = my_integer
+);
+-- should not be precalculated
+SELECT (
+ NULLIF('(1)'::my_integer, '(2)'::my_integer) IS DISTINCT FROM
+ NULLIF('(2)'::my_integer, '(2)'::my_integer)
+)
+FROM generate_series(1, 4) x;
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+ ?column?
+----------
+ t
+ t
+ t
+ t
+(4 rows)
+
+-- should not be precalculated
+SELECT NULLIF(
+ ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer),
+ ('(2)'::my_integer IS DISTINCT FROM '(2)'::my_integer)
+)
+FROM generate_series(1, 4) x;
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+ nullif
+--------
+ t
+ t
+ t
+ t
+(4 rows)
+
+DROP OPERATOR = (my_integer, my_integer);
+CREATE OPERATOR = (
+ PROCEDURE = equal_my_integer_stl,
+ LEFTARG = my_integer,
+ RIGHTARG = my_integer
+);
+SELECT (
+ NULLIF('(1)'::my_integer, '(2)'::my_integer) IS DISTINCT FROM
+ NULLIF('(2)'::my_integer, '(2)'::my_integer)
+)
+FROM generate_series(1, 4) x;
+NOTICE: equal my_integer stable
+NOTICE: equal my_integer stable
+ ?column?
+----------
+ t
+ t
+ t
+ t
+(4 rows)
+
+SELECT NULLIF(
+ ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer),
+ ('(2)'::my_integer IS DISTINCT FROM '(2)'::my_integer)
+)
+FROM generate_series(1, 4) x;
+NOTICE: equal my_integer stable
+NOTICE: equal my_integer stable
+ nullif
+--------
+ t
+ t
+ t
+ t
+(4 rows)
+
+-- Mixed IS DISTINCT FROM and "scalar op ANY/ALL (array)" / "scalar IN (2 or
+-- more values)" expressions testing
+-- should not be precalculated
+SELECT x_stl2_boolean(
+ (1 === ANY('{2, 3}')) IS DISTINCT FROM
+ (1 === ALL('{2, 3}'))
+)
+FROM generate_series(1, 4) x;
+NOTICE: equal integers volatile
+NOTICE: equal integers volatile
+NOTICE: equal integers volatile
+NOTICE: s2 boolean
+NOTICE: equal integers volatile
+NOTICE: equal integers volatile
+NOTICE: equal integers volatile
+NOTICE: s2 boolean
+NOTICE: equal integers volatile
+NOTICE: equal integers volatile
+NOTICE: equal integers volatile
+NOTICE: s2 boolean
+NOTICE: equal integers volatile
+NOTICE: equal integers volatile
+NOTICE: equal integers volatile
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+ f
+ f
+ f
+ f
+(4 rows)
+
+DROP OPERATOR = (my_integer, my_integer);
+CREATE OPERATOR = (
+ PROCEDURE = equal_my_integer_vlt,
+ LEFTARG = my_integer,
+ RIGHTARG = my_integer
+);
+-- should not be precalculated
+SELECT x_stl2_boolean(
+ ('(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer)) IS DISTINCT FROM
+ TRUE
+)
+FROM generate_series(1, 4) x;
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: s2 boolean
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: s2 boolean
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: s2 boolean
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+ t
+ t
+ t
+ t
+(4 rows)
+
+-- should not be precalculated
+SELECT x_stl2_boolean(
+ ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) = ANY('{TRUE}')
+)
+FROM generate_series(1, 4) x;
+NOTICE: equal my_integer volatile
+NOTICE: s2 boolean
+NOTICE: equal my_integer volatile
+NOTICE: s2 boolean
+NOTICE: equal my_integer volatile
+NOTICE: s2 boolean
+NOTICE: equal my_integer volatile
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+ t
+ t
+ t
+ t
+(4 rows)
+
+-- should not be precalculated
+SELECT x_stl2_boolean(
+ ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) = ALL('{TRUE}')
+)
+FROM generate_series(1, 4) x;
+NOTICE: equal my_integer volatile
+NOTICE: s2 boolean
+NOTICE: equal my_integer volatile
+NOTICE: s2 boolean
+NOTICE: equal my_integer volatile
+NOTICE: s2 boolean
+NOTICE: equal my_integer volatile
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+ t
+ t
+ t
+ t
+(4 rows)
+
+-- should not be precalculated
+SELECT x_stl2_boolean(
+ ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) IN (TRUE, FALSE)
+)
+FROM generate_series(1, 4) x;
+NOTICE: equal my_integer volatile
+NOTICE: s2 boolean
+NOTICE: equal my_integer volatile
+NOTICE: s2 boolean
+NOTICE: equal my_integer volatile
+NOTICE: s2 boolean
+NOTICE: equal my_integer volatile
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+ t
+ t
+ t
+ t
+(4 rows)
+
+SELECT x_stl2_boolean(
+ (1 ==== ANY('{2, 3}')) IS DISTINCT FROM
+ (1 ==== ALL('{2, 3}'))
+)
+FROM generate_series(1, 4) x;
+NOTICE: equal integers stable
+NOTICE: equal integers stable
+NOTICE: equal integers stable
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+ f
+ f
+ f
+ f
+(4 rows)
+
+DROP OPERATOR = (my_integer, my_integer);
+CREATE OPERATOR = (
+ PROCEDURE = equal_my_integer_stl,
+ LEFTARG = my_integer,
+ RIGHTARG = my_integer
+);
+SELECT x_stl2_boolean(
+ ('(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer)) IS DISTINCT FROM
+ TRUE
+)
+FROM generate_series(1, 4) x;
+NOTICE: equal my_integer stable
+NOTICE: equal my_integer stable
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+ t
+ t
+ t
+ t
+(4 rows)
+
+SELECT x_stl2_boolean(
+ ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) = ANY('{TRUE}')
+)
+FROM generate_series(1, 4) x;
+NOTICE: equal my_integer stable
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+ t
+ t
+ t
+ t
+(4 rows)
+
+SELECT x_stl2_boolean(
+ ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) = ALL('{TRUE}')
+)
+FROM generate_series(1, 4) x;
+NOTICE: equal my_integer stable
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+ t
+ t
+ t
+ t
+(4 rows)
+
+SELECT x_stl2_boolean(
+ ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) IN (TRUE, FALSE)
+)
+FROM generate_series(1, 4) x;
+NOTICE: equal my_integer stable
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+ t
+ t
+ t
+ t
+(4 rows)
+
+-- Mixed NULLIF and "scalar op ANY/ALL (array)" / "scalar IN (2 or more values)"
+-- expressions testing
+-- should not be precalculated
+SELECT x_stl2_boolean(NULLIF(1 === ANY('{2, 3}'), 1 === ALL('{2, 3}')))
+FROM generate_series(1, 4) x;
+NOTICE: equal integers volatile
+NOTICE: equal integers volatile
+NOTICE: equal integers volatile
+NOTICE: s2 boolean
+NOTICE: equal integers volatile
+NOTICE: equal integers volatile
+NOTICE: equal integers volatile
+NOTICE: s2 boolean
+NOTICE: equal integers volatile
+NOTICE: equal integers volatile
+NOTICE: equal integers volatile
+NOTICE: s2 boolean
+NOTICE: equal integers volatile
+NOTICE: equal integers volatile
+NOTICE: equal integers volatile
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+
+
+
+
+(4 rows)
+
+DROP OPERATOR = (my_integer, my_integer);
+CREATE OPERATOR = (
+ PROCEDURE = equal_my_integer_vlt,
+ LEFTARG = my_integer,
+ RIGHTARG = my_integer
+);
+-- should not be precalculated
+SELECT x_stl2_boolean(NULLIF(
+ '(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer),
+ TRUE
+))
+FROM generate_series(1, 4) x;
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: s2 boolean
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: s2 boolean
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: s2 boolean
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+ f
+ f
+ f
+ f
+(4 rows)
+
+-- should not be precalculated
+SELECT (
+ NULLIF('(1)'::my_integer, '(2)'::my_integer) ====
+ ANY('{(3)}'::my_integer[])
+)
+FROM generate_series(1, 4) x;
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer stable
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer stable
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer stable
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer stable
+ ?column?
+----------
+ f
+ f
+ f
+ f
+(4 rows)
+
+-- should not be precalculated
+SELECT (
+ NULLIF('(1)'::my_integer, '(2)'::my_integer) ====
+ ALL('{(3)}'::my_integer[])
+)
+FROM generate_series(1, 4) x;
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer stable
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer stable
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer stable
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer stable
+ ?column?
+----------
+ f
+ f
+ f
+ f
+(4 rows)
+
+-- should not be precalculated
+SELECT (
+ NULLIF('(1)'::my_integer, '(2)'::my_integer) IN
+ ('(3)'::my_integer, '(2)'::my_integer)
+)
+FROM generate_series(1, 4) x;
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+NOTICE: equal my_integer volatile
+ ?column?
+----------
+ f
+ f
+ f
+ f
+(4 rows)
+
+SELECT x_stl2_boolean(NULLIF(1 ==== ANY('{2, 3}'), 1 ==== ALL('{2, 3}')))
+FROM generate_series(1, 4) x;
+NOTICE: equal integers stable
+NOTICE: equal integers stable
+NOTICE: equal integers stable
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+
+
+
+
+(4 rows)
+
+DROP OPERATOR = (my_integer, my_integer);
+CREATE OPERATOR = (
+ PROCEDURE = equal_my_integer_stl,
+ LEFTARG = my_integer,
+ RIGHTARG = my_integer
+);
+SELECT x_stl2_boolean(NULLIF(
+ '(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer),
+ TRUE
+))
+FROM generate_series(1, 4) x;
+NOTICE: equal my_integer stable
+NOTICE: equal my_integer stable
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+ f
+ f
+ f
+ f
+(4 rows)
+
+SELECT (
+ NULLIF('(1)'::my_integer, '(2)'::my_integer) ====
+ ANY('{(3)}'::my_integer[])
+)
+FROM generate_series(1, 4) x;
+NOTICE: equal my_integer stable
+NOTICE: equal my_integer stable
+ ?column?
+----------
+ f
+ f
+ f
+ f
+(4 rows)
+
+SELECT (
+ NULLIF('(1)'::my_integer, '(2)'::my_integer) ====
+ ALL('{(3)}'::my_integer[])
+)
+FROM generate_series(1, 4) x;
+NOTICE: equal my_integer stable
+NOTICE: equal my_integer stable
+ ?column?
+----------
+ f
+ f
+ f
+ f
+(4 rows)
+
+SELECT (
+ NULLIF('(1)'::my_integer, '(2)'::my_integer) IN
+ ('(3)'::my_integer, '(2)'::my_integer)
+)
+FROM generate_series(1, 4) x;
+NOTICE: equal my_integer stable
+NOTICE: equal my_integer stable
+NOTICE: equal my_integer stable
+ ?column?
+----------
+ f
+ f
+ f
+ f
+(4 rows)
+
+-- Tracking functions testing
+SET track_functions TO 'all';
+SELECT x_vlt() FROM generate_series(1, 3) x; -- should not be precalculated
+NOTICE: v
+NOTICE: v
+NOTICE: v
+ x_vlt
+-------
+ 1
+ 1
+ 1
+(3 rows)
+
+SELECT x_stl() FROM generate_series(1, 3) x;
+NOTICE: s
+ x_stl
+-------
+ 1
+ 1
+ 1
+(3 rows)
+
+SELECT x_vlt() FROM generate_series(1, 4) x WHERE x_vlt() < x; -- should not be precalculated
+NOTICE: v
+NOTICE: v
+NOTICE: v
+NOTICE: v
+NOTICE: v
+NOTICE: v
+NOTICE: v
+ x_vlt
+-------
+ 1
+ 1
+ 1
+(3 rows)
+
+SELECT x_stl() FROM generate_series(1, 4) x WHERE x_stl() < x;
+NOTICE: s
+NOTICE: s
+NOTICE: s
+ x_stl
+-------
+ 1
+ 1
+ 1
+(3 rows)
+
+SELECT x_stl2(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
+NOTICE: v
+NOTICE: s2
+NOTICE: v
+NOTICE: s2
+NOTICE: v
+NOTICE: s2
+NOTICE: v
+NOTICE: s2
+ x_stl2
+--------
+ 1
+ 1
+ 1
+ 1
+(4 rows)
+
+SELECT x_imm2(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
+NOTICE: v
+NOTICE: i2
+NOTICE: v
+NOTICE: i2
+NOTICE: v
+NOTICE: i2
+NOTICE: v
+NOTICE: i2
+ x_imm2
+--------
+ 1
+ 1
+ 1
+ 1
+(4 rows)
+
+SELECT x_stl2(x_stl2(1)) FROM generate_series(1, 4) x;
+NOTICE: s2
+NOTICE: s2
+ x_stl2
+--------
+ 1
+ 1
+ 1
+ 1
+(4 rows)
+
+SELECT x_imm2(x_stl2(1)) FROM generate_series(1, 4) x;
+NOTICE: s2
+NOTICE: i2
+ x_imm2
+--------
+ 1
+ 1
+ 1
+ 1
+(4 rows)
+
+SELECT x_stl2_strict(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
+NOTICE: v
+NOTICE: s2 strict
+NOTICE: v
+NOTICE: s2 strict
+NOTICE: v
+NOTICE: s2 strict
+NOTICE: v
+NOTICE: s2 strict
+ x_stl2_strict
+---------------
+ 1
+ 1
+ 1
+ 1
+(4 rows)
+
+SELECT x_imm2_strict(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
+NOTICE: v
+NOTICE: i2 strict
+NOTICE: v
+NOTICE: i2 strict
+NOTICE: v
+NOTICE: i2 strict
+NOTICE: v
+NOTICE: i2 strict
+ x_imm2_strict
+---------------
+ 1
+ 1
+ 1
+ 1
+(4 rows)
+
+SELECT x_stl2_strict(x_stl2_strict(1)) FROM generate_series(1, 4) x;
+NOTICE: s2 strict
+NOTICE: s2 strict
+ x_stl2_strict
+---------------
+ 1
+ 1
+ 1
+ 1
+(4 rows)
+
+SELECT x_imm2_strict(x_stl2_strict(1)) FROM generate_series(1, 4) x;
+NOTICE: s2 strict
+NOTICE: i2 strict
+ x_imm2_strict
+---------------
+ 1
+ 1
+ 1
+ 1
+(4 rows)
+
+SELECT x_stl2_strict(x_stl2(NULL)) FROM generate_series(1, 4) x;
+NOTICE: s2
+ x_stl2_strict
+---------------
+
+
+
+
+(4 rows)
+
+SELECT x_imm2_strict(x_stl2(NULL)) FROM generate_series(1, 4) x;
+NOTICE: s2
+ x_imm2_strict
+---------------
+
+
+
+
+(4 rows)
+
+SELECT 1 === 2 FROM generate_series(1, 4) x; -- should not be precalculated
+NOTICE: equal integers volatile
+NOTICE: equal integers volatile
+NOTICE: equal integers volatile
+NOTICE: equal integers volatile
+ ?column?
+----------
+ f
+ f
+ f
+ f
+(4 rows)
+
+SELECT 1 ==== 2 FROM generate_series(1, 4) x;
+NOTICE: equal integers stable
+ ?column?
+----------
+ f
+ f
+ f
+ f
+(4 rows)
+
+SELECT 1 ===== 2 FROM generate_series(1, 4) x;
+NOTICE: equal integers immutable
+ ?column?
+----------
+ f
+ f
+ f
+ f
+(4 rows)
+
+SELECT (x_vlt() ==== 2) ====== (x_vlt() ===== 3) FROM generate_series(1, 4) x; -- should not be precalculated
+NOTICE: v
+NOTICE: equal integers stable
+NOTICE: v
+NOTICE: equal integers immutable
+NOTICE: equal booleans stable strict
+NOTICE: v
+NOTICE: equal integers stable
+NOTICE: v
+NOTICE: equal integers immutable
+NOTICE: equal booleans stable strict
+NOTICE: v
+NOTICE: equal integers stable
+NOTICE: v
+NOTICE: equal integers immutable
+NOTICE: equal booleans stable strict
+NOTICE: v
+NOTICE: equal integers stable
+NOTICE: v
+NOTICE: equal integers immutable
+NOTICE: equal booleans stable strict
+ ?column?
+----------
+ t
+ t
+ t
+ t
+(4 rows)
+
+SELECT (1 ==== 2) ====== (3 ==== 3) FROM generate_series(1, 4) x;
+NOTICE: equal integers stable
+NOTICE: equal integers stable
+NOTICE: equal booleans stable strict
+ ?column?
+----------
+ f
+ f
+ f
+ f
+(4 rows)
+
+SELECT x_stl2_boolean(NULL) ====== (3 ==== 3) FROM generate_series(1, 4) x;
+NOTICE: s2 boolean
+NOTICE: equal integers stable
+ ?column?
+----------
+
+
+
+
+(4 rows)
+
+SELECT x_vlt() ==== 2 FROM generate_series(1, 4) x; -- should not be precalculated
+NOTICE: v
+NOTICE: equal integers stable
+NOTICE: v
+NOTICE: equal integers stable
+NOTICE: v
+NOTICE: equal integers stable
+NOTICE: v
+NOTICE: equal integers stable
+ ?column?
+----------
+ f
+ f
+ f
+ f
+(4 rows)
+
+SELECT x_vlt() ===== 2 FROM generate_series(1, 4) x; -- should not be precalculated
+NOTICE: v
+NOTICE: equal integers immutable
+NOTICE: v
+NOTICE: equal integers immutable
+NOTICE: v
+NOTICE: equal integers immutable
+NOTICE: v
+NOTICE: equal integers immutable
+ ?column?
+----------
+ f
+ f
+ f
+ f
+(4 rows)
+
+SELECT x_stl() ==== x_stl() FROM generate_series(1, 4) x;
+NOTICE: s
+NOTICE: s
+NOTICE: equal integers stable
+ ?column?
+----------
+ t
+ t
+ t
+ t
+(4 rows)
+
+SELECT x_stl2_boolean(1 ==== 2) FROM generate_series(1, 4) x;
+NOTICE: equal integers stable
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+ f
+ f
+ f
+ f
+(4 rows)
+
+SET track_functions TO DEFAULT;
+-- PL/pgSQL Simple expressions
+-- Make sure precalculated stable functions can't be simple expressions: these
+-- expressions are only initialized once per transaction and then executed
+-- multiple times.
+BEGIN;
+SELECT simple();
+ simple
+--------
+ 2
+(1 row)
+
+INSERT INTO two VALUES (3);
+SELECT simple();
+ simple
+--------
+ 3
+(1 row)
+
+ROLLBACK;
+-- Drop tables for testing
+DROP TABLE two;
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index 04206c3..f2710b9 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -179,3 +179,4 @@ test: with
test: xml
test: event_trigger
test: stats
+test: precalculate_stable_functions
diff --git a/src/test/regress/sql/precalculate_stable_functions.sql b/src/test/regress/sql/precalculate_stable_functions.sql
new file mode 100644
index 0000000..a59791d
--- /dev/null
+++ b/src/test/regress/sql/precalculate_stable_functions.sql
@@ -0,0 +1,949 @@
+--
+-- PRECALCULATE STABLE FUNCTIONS
+--
+
+-- Create types and tables for testing
+
+CREATE TYPE my_integer AS (value integer);
+
+CREATE TABLE two (i integer);
+INSERT INTO two VALUES (1), (2);
+
+-- Create volatile functions for testing
+
+CREATE OR REPLACE FUNCTION public.x_vlt (
+)
+RETURNS integer VOLATILE AS
+$body$
+BEGIN
+ RAISE NOTICE 'v';
+ RETURN 1;
+END;
+$body$
+LANGUAGE 'plpgsql';
+
+CREATE OR REPLACE FUNCTION public.equal_integers_vlt (
+ integer,
+ integer
+)
+RETURNS boolean VOLATILE AS
+$body$
+BEGIN
+ RAISE NOTICE 'equal integers volatile';
+ RETURN $1 = $2;
+END;
+$body$
+LANGUAGE 'plpgsql';
+
+CREATE OR REPLACE FUNCTION public.x_vlt_my_integer (
+)
+RETURNS my_integer VOLATILE AS
+$body$
+BEGIN
+ RAISE NOTICE 'v my_integer';
+ RETURN '(1)'::my_integer;
+END;
+$body$
+LANGUAGE 'plpgsql';
+
+CREATE OR REPLACE FUNCTION public.equal_my_integer_vlt (
+ my_integer,
+ my_integer
+)
+RETURNS boolean VOLATILE AS
+$body$
+BEGIN
+ RAISE NOTICE 'equal my_integer volatile';
+ RETURN $1.value = $2.value;
+END;
+$body$
+LANGUAGE 'plpgsql';
+
+CREATE OR REPLACE FUNCTION public.x_vlt_array_int (
+)
+RETURNS int[] VOLATILE AS
+$body$
+BEGIN
+ RAISE NOTICE 'v array_int';
+ RETURN '{2, 3}'::int[];
+END;
+$body$
+LANGUAGE 'plpgsql';
+
+-- Create stable functions for testing
+
+CREATE OR REPLACE FUNCTION public.x_stl (
+)
+RETURNS integer STABLE AS
+$body$
+BEGIN
+ RAISE NOTICE 's';
+ RETURN 1;
+END;
+$body$
+LANGUAGE 'plpgsql';
+
+CREATE OR REPLACE FUNCTION public.x_stl2 (
+ integer
+)
+RETURNS integer STABLE AS
+$body$
+BEGIN
+ RAISE NOTICE 's2';
+ RETURN $1;
+END;
+$body$
+LANGUAGE 'plpgsql';
+
+CREATE OR REPLACE FUNCTION public.x_stl2_strict (
+ integer
+)
+RETURNS integer STABLE STRICT AS
+$body$
+BEGIN
+ RAISE NOTICE 's2 strict';
+ RETURN $1;
+END;
+$body$
+LANGUAGE 'plpgsql';
+
+CREATE OR REPLACE FUNCTION public.equal_integers_stl (
+ integer,
+ integer
+)
+RETURNS boolean STABLE AS
+$body$
+BEGIN
+ RAISE NOTICE 'equal integers stable';
+ RETURN $1 = $2;
+END;
+$body$
+LANGUAGE 'plpgsql';
+
+CREATE OR REPLACE FUNCTION public.x_stl2_boolean (
+ boolean
+)
+RETURNS boolean STABLE AS
+$body$
+BEGIN
+ RAISE NOTICE 's2 boolean';
+ RETURN $1;
+END;
+$body$
+LANGUAGE 'plpgsql';
+
+CREATE OR REPLACE FUNCTION public.equal_booleans_stl_strict (
+ boolean,
+ boolean
+)
+RETURNS boolean STABLE STRICT AS
+$body$
+BEGIN
+ RAISE NOTICE 'equal booleans stable strict';
+ RETURN $1 = $2;
+END;
+$body$
+LANGUAGE 'plpgsql';
+
+CREATE OR REPLACE FUNCTION public.x_stl_my_integer (
+)
+RETURNS my_integer STABLE AS
+$body$
+BEGIN
+ RAISE NOTICE 's my_integer';
+ RETURN '(1)'::my_integer;
+END;
+$body$
+LANGUAGE 'plpgsql';
+
+CREATE OR REPLACE FUNCTION public.equal_my_integer_stl (
+ my_integer,
+ my_integer
+)
+RETURNS boolean STABLE AS
+$body$
+BEGIN
+ RAISE NOTICE 'equal my_integer stable';
+ RETURN $1.value = $2.value;
+END;
+$body$
+LANGUAGE 'plpgsql';
+
+CREATE OR REPLACE FUNCTION public.x_stl_array_int (
+)
+RETURNS int[] STABLE AS
+$body$
+BEGIN
+ RAISE NOTICE 's array_int';
+ RETURN '{2, 3}'::int[];
+END;
+$body$
+LANGUAGE 'plpgsql';
+
+CREATE OR REPLACE FUNCTION public.stable_max(
+)
+RETURNS integer STABLE AS
+$body$
+BEGIN
+ RETURN (SELECT max(i) from two);
+END
+$body$
+LANGUAGE 'plpgsql';
+
+CREATE OR REPLACE FUNCTION public.simple(
+)
+RETURNS integer STABLE AS
+$body$
+BEGIN
+ RETURN stable_max();
+END
+$body$
+LANGUAGE 'plpgsql';
+
+-- Create immutable functions for testing
+
+CREATE OR REPLACE FUNCTION public.x_imm2 (
+ integer
+)
+RETURNS integer IMMUTABLE AS
+$body$
+BEGIN
+ RAISE NOTICE 'i2';
+ RETURN $1;
+END;
+$body$
+LANGUAGE 'plpgsql';
+
+CREATE OR REPLACE FUNCTION public.x_imm2_strict (
+ integer
+)
+RETURNS integer IMMUTABLE STRICT AS
+$body$
+BEGIN
+ RAISE NOTICE 'i2 strict';
+ RETURN $1;
+END;
+$body$
+LANGUAGE 'plpgsql';
+
+CREATE OR REPLACE FUNCTION public.equal_integers_imm (
+ integer,
+ integer
+)
+RETURNS boolean IMMUTABLE AS
+$body$
+BEGIN
+ RAISE NOTICE 'equal integers immutable';
+ RETURN $1 = $2;
+END;
+$body$
+LANGUAGE 'plpgsql';
+
+CREATE OR REPLACE FUNCTION public.equal_my_integer_imm (
+ my_integer,
+ my_integer
+)
+RETURNS boolean IMMUTABLE AS
+$body$
+BEGIN
+ RAISE NOTICE 'equal my_integer immutable';
+ RETURN $1.value = $2.value;
+END;
+$body$
+LANGUAGE 'plpgsql';
+
+-- Create operators for testing
+
+CREATE operator === (
+ PROCEDURE = equal_integers_vlt,
+ LEFTARG = integer,
+ RIGHTARG = integer
+);
+
+CREATE operator ==== (
+ PROCEDURE = equal_integers_stl,
+ LEFTARG = integer,
+ RIGHTARG = integer
+);
+
+CREATE operator ===== (
+ PROCEDURE = equal_integers_imm,
+ LEFTARG = integer,
+ RIGHTARG = integer
+);
+
+CREATE operator ====== (
+ PROCEDURE = equal_booleans_stl_strict,
+ LEFTARG = boolean,
+ RIGHTARG = boolean
+);
+
+CREATE operator ==== (
+ PROCEDURE = equal_my_integer_stl,
+ LEFTARG = my_integer,
+ RIGHTARG = my_integer
+);
+
+-- Simple functions testing
+
+SELECT x_vlt() FROM generate_series(1, 3) x; -- should not be precalculated
+SELECT x_stl() FROM generate_series(1, 3) x;
+
+-- WHERE clause testing
+
+SELECT x_vlt() FROM generate_series(1, 4) x WHERE x_vlt() < x; -- should not be precalculated
+SELECT x_stl() FROM generate_series(1, 4) x WHERE x_stl() < x;
+
+-- Functions with constant arguments and nested functions testing
+
+SELECT x_stl2(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
+SELECT x_imm2(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
+
+SELECT x_stl2(x_stl2(1)) FROM generate_series(1, 4) x;
+SELECT x_imm2(x_stl2(1)) FROM generate_series(1, 4) x;
+
+-- Strict functions testing
+
+SELECT x_stl2_strict(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
+SELECT x_imm2_strict(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
+
+SELECT x_stl2_strict(x_stl2_strict(1)) FROM generate_series(1, 4) x;
+SELECT x_imm2_strict(x_stl2_strict(1)) FROM generate_series(1, 4) x;
+
+-- Strict functions with null arguments testing
+
+SELECT x_stl2_strict(x_stl2(NULL)) FROM generate_series(1, 4) x;
+SELECT x_imm2_strict(x_stl2(NULL)) FROM generate_series(1, 4) x;
+
+-- Operators testing
+
+SELECT 1 === 2 FROM generate_series(1, 4) x; -- should not be precalculated
+SELECT 1 ==== 2 FROM generate_series(1, 4) x;
+
+-- Nested and strict operators testing
+-- (also partly mixed functions and operators testing)
+
+SELECT (x_vlt() ==== 2) ====== (x_vlt() ===== 3) FROM generate_series(1, 4) x; -- should not be precalculated
+SELECT (x_stl() ==== 2) ====== (x_stl() ===== 3) FROM generate_series(1, 4) x;
+SELECT (1 ==== 2) ====== x_stl2_boolean(NULL) FROM generate_series(1, 4) x;
+
+-- IS DISTINCT FROM expression testing
+
+-- create operator here because we will drop and reuse it several times
+CREATE OPERATOR = (
+ PROCEDURE = equal_my_integer_vlt,
+ LEFTARG = my_integer,
+ RIGHTARG = my_integer
+);
+
+-- should not be precalculated
+SELECT '(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer
+FROM generate_series(1, 4) x;
+
+DROP OPERATOR = (my_integer, my_integer);
+CREATE OPERATOR = (
+ PROCEDURE = equal_my_integer_stl,
+ LEFTARG = my_integer,
+ RIGHTARG = my_integer
+);
+
+SELECT '(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer
+FROM generate_series(1, 4) x;
+
+-- IS DISTINCT FROM expressions with null arguments testing
+
+SELECT x_stl2_boolean(1 IS DISTINCT FROM x_stl2(NULL))
+FROM generate_series(1, 4) x;
+
+SELECT x_stl2_boolean(x_stl2(NULL) IS DISTINCT FROM x_stl2(NULL))
+FROM generate_series(1, 4) x;
+
+-- Nested IS DISTINCT FROM expression testing
+
+DROP OPERATOR = (my_integer, my_integer);
+CREATE OPERATOR = (
+ PROCEDURE = equal_my_integer_vlt,
+ LEFTARG = my_integer,
+ RIGHTARG = my_integer
+);
+
+-- should not be precalculated
+SELECT x_stl2_boolean(
+ ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) IS DISTINCT FROM
+ TRUE
+)
+FROM generate_series(1, 4) x;
+
+DROP OPERATOR = (my_integer, my_integer);
+CREATE OPERATOR = (
+ PROCEDURE = equal_my_integer_stl,
+ LEFTARG = my_integer,
+ RIGHTARG = my_integer
+);
+
+SELECT x_stl2_boolean(
+ ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) IS DISTINCT FROM
+ TRUE
+)
+FROM generate_series(1, 4) x;
+
+-- NULLIF expressions testing
+
+DROP OPERATOR = (my_integer, my_integer);
+CREATE OPERATOR = (
+ PROCEDURE = equal_my_integer_vlt,
+ LEFTARG = my_integer,
+ RIGHTARG = my_integer
+);
+
+-- should not be precalculated
+SELECT NULLIF('(1)'::my_integer, '(2)'::my_integer)
+FROM generate_series(1, 4) x;
+
+DROP OPERATOR = (my_integer, my_integer);
+CREATE OPERATOR = (
+ PROCEDURE = equal_my_integer_stl,
+ LEFTARG = my_integer,
+ RIGHTARG = my_integer
+);
+
+SELECT NULLIF('(1)'::my_integer, '(2)'::my_integer)
+FROM generate_series(1, 4) x;
+
+-- NULLIF expressions with null arguments testing
+
+SELECT x_stl2(NULLIF(1, x_stl2(NULL))) FROM generate_series(1, 4) x;
+
+SELECT x_stl2(NULLIF(x_stl2(NULL), x_stl2(NULL))) FROM generate_series(1, 4) x;
+
+-- Nested NULLIF expression testing
+
+-- should not be precalculated
+SELECT NULLIF(NULLIF(x_vlt_my_integer(), '(2)'::my_integer), '(2)'::my_integer)
+FROM generate_series(1, 4) x;
+
+SELECT NULLIF(NULLIF(x_stl_my_integer(), '(2)'::my_integer), '(2)'::my_integer)
+FROM generate_series(1, 4) x;
+
+DROP OPERATOR = (my_integer, my_integer);
+CREATE OPERATOR = (
+ PROCEDURE = equal_my_integer_imm,
+ LEFTARG = my_integer,
+ RIGHTARG = my_integer
+);
+
+-- should not be precalculated
+SELECT NULLIF(NULLIF(x_vlt_my_integer(), '(2)'::my_integer), '(2)'::my_integer)
+FROM generate_series(1, 4) x;
+
+SELECT NULLIF(NULLIF(x_stl_my_integer(), '(2)'::my_integer), '(2)'::my_integer)
+FROM generate_series(1, 4) x;
+
+-- "scalar op ANY/ALL (array)" / "scalar IN (2 or more values)" expressions
+-- testing
+
+SELECT 1 === ANY('{2, 3}') FROM generate_series(1, 4) x; -- should not be precalculated
+SELECT 1 === ALL('{2, 3}') FROM generate_series(1, 4) x; -- should not be precalculated
+
+DROP OPERATOR = (my_integer, my_integer);
+CREATE OPERATOR = (
+ PROCEDURE = equal_my_integer_vlt,
+ LEFTARG = my_integer,
+ RIGHTARG = my_integer
+);
+
+-- should not be precalculated
+SELECT '(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer)
+FROM generate_series(1, 4) x;
+
+SELECT 1 ==== ANY('{2, 3}') FROM generate_series(1, 4) x;
+SELECT 1 ==== ALL('{2, 3}') FROM generate_series(1, 4) x;
+
+DROP OPERATOR = (my_integer, my_integer);
+CREATE OPERATOR = (
+ PROCEDURE = equal_my_integer_stl,
+ LEFTARG = my_integer,
+ RIGHTARG = my_integer
+);
+
+SELECT '(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer)
+FROM generate_series(1, 4) x;
+
+-- "scalar op ANY/ALL (array)" / "scalar IN (2 or more values)" expressions with
+-- null arguments testing
+
+SELECT 1 ==== ANY('{2, NULL}') FROM generate_series(1, 4) x;
+SELECT x_stl2_boolean(1 ==== ANY(NULL)) FROM generate_series(1, 4) x;
+SELECT NULL ==== ANY('{2, 3}'::int[]) FROM generate_series(1, 4) x;
+SELECT NULL ==== ANY('{2, NULL}'::int[]) FROM generate_series(1, 4) x;
+SELECT x_stl2_boolean(NULL::int ==== ANY(NULL)) FROM generate_series(1, 4) x;
+
+SELECT 1 ==== ALL('{2, NULL}') FROM generate_series(1, 4) x;
+SELECT x_stl2_boolean(1 ==== ALL(NULL)) FROM generate_series(1, 4) x;
+SELECT NULL ==== ALL('{2, 3}'::int[]) FROM generate_series(1, 4) x;
+SELECT NULL ==== ALL('{2, NULL}'::int[]) FROM generate_series(1, 4) x;
+SELECT x_stl2_boolean(NULL::int ==== ALL(NULL)) FROM generate_series(1, 4) x;
+
+SELECT x_stl2_boolean(1 IN (2, NULL)) FROM generate_series(1, 4) x;
+SELECT x_stl2_boolean(NULL IN (2, 3)) FROM generate_series(1, 4) x;
+SELECT x_stl2_boolean(NULL IN (2, NULL)) FROM generate_series(1, 4) x;
+
+-- Nesting "scalar op ANY/ALL (array)" / "scalar IN (2 or more values)"
+-- expressions testing (also partly mixed functions and "scalar op ANY/ALL
+-- (array)" / "scalar IN (2 or more values)" expressions testing)
+
+-- should not be precalculated
+SELECT x_stl2_boolean((x_vlt() ==== ANY('{2, 3}')) = ANY('{TRUE}'))
+FROM generate_series(1, 4) x;
+
+-- should not be precalculated
+SELECT x_stl2_boolean((x_vlt() ==== ANY('{2, 3}')) = ALL('{TRUE}'))
+FROM generate_series(1, 4) x;
+
+-- should not be precalculated
+SELECT x_stl2_boolean((x_vlt() ==== ANY('{2, 3}')) IN (TRUE, FALSE))
+FROM generate_series(1, 4) x;
+
+SELECT x_stl2_boolean((x_stl() ==== ANY('{2, 3}')) = ANY('{TRUE}'))
+FROM generate_series(1, 4) x;
+
+SELECT x_stl2_boolean((x_stl() ==== ANY('{2, 3}')) = ALL('{TRUE}'))
+FROM generate_series(1, 4) x;
+
+SELECT x_stl2_boolean((x_stl() ==== ANY('{2, 3}')) IN (TRUE, FALSE))
+FROM generate_series(1, 4) x;
+
+-- should not be precalculated
+SELECT x_stl2_boolean((x_vlt() ===== ANY('{2, 3}')) = ANY('{TRUE}'))
+FROM generate_series(1, 4) x;
+
+-- should not be precalculated
+SELECT x_stl2_boolean((x_vlt() ===== ANY('{2, 3}')) = ALL('{TRUE}'))
+FROM generate_series(1, 4) x;
+
+-- should not be precalculated
+SELECT x_stl2_boolean((x_vlt() ===== ANY('{2, 3}')) IN (TRUE, FALSE))
+FROM generate_series(1, 4) x;
+
+SELECT x_stl2_boolean((x_stl() ===== ANY('{2, 3}')) = ANY('{TRUE}'))
+FROM generate_series(1, 4) x;
+
+SELECT x_stl2_boolean((x_stl() ===== ANY('{2, 3}')) = ALL('{TRUE}'))
+FROM generate_series(1, 4) x;
+
+SELECT x_stl2_boolean((x_stl() ===== ANY('{2, 3}')) IN (TRUE, FALSE))
+FROM generate_series(1, 4) x;
+
+-- Mixed functions and operators testing
+-- (most of it was earlier in Nested and strict operators testing)
+
+SELECT x_stl2_boolean(1 ==== 2) FROM generate_series(1, 4) x;
+
+-- Mixed functions and IS DISTINCT FROM expressions testing
+
+DROP OPERATOR = (my_integer, my_integer);
+CREATE OPERATOR = (
+ PROCEDURE = equal_my_integer_vlt,
+ LEFTARG = my_integer,
+ RIGHTARG = my_integer
+);
+
+-- should not be precalculated
+SELECT equal_booleans_stl_strict(
+ (x_stl_my_integer() IS DISTINCT FROM '(1)'::my_integer),
+ (x_stl_my_integer() IS DISTINCT FROM '(2)'::my_integer)
+)
+FROM generate_series(1, 4) x;
+
+SELECT equal_booleans_stl_strict(
+ (x_stl() IS DISTINCT FROM 1),
+ (x_stl() IS DISTINCT FROM 2)
+)
+FROM generate_series(1, 4) x;
+
+-- Mixed functions and NULLIF expressions testing
+
+-- should not be precalculated
+SELECT equal_my_integer_stl(
+ NULLIF(x_stl_my_integer(), '(1)'::my_integer),
+ NULLIF(x_stl_my_integer(), '(2)'::my_integer)
+)
+FROM generate_series(1, 4) x;
+
+SELECT equal_integers_stl(NULLIF(x_stl(), 1), NULLIF(x_stl(), 2))
+FROM generate_series(1, 4) x;
+
+-- Mixed functions and "scalar op ANY/ALL (array)" / "scalar IN (2 or more
+-- values)" expressions testing (partly in nesting "scalar op ANY/ALL (array)" /
+-- "scalar IN (2 or more values)" expressions testing)
+
+SELECT 1 ==== ANY(x_vlt_array_int()) FROM generate_series(1, 4) x; -- should not be precalculated
+SELECT 1 ==== ALL(x_vlt_array_int()) FROM generate_series(1, 4) x; -- should not be precalculated
+
+SELECT 1 ==== ANY(x_stl_array_int()) FROM generate_series(1, 4) x;
+SELECT 1 ==== ALL(x_stl_array_int()) FROM generate_series(1, 4) x;
+
+-- Mixed operators and IS DISTINCT FROM expressions testing
+
+-- should not be precalculated
+SELECT (
+ ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) ======
+ ('(2)'::my_integer IS DISTINCT FROM '(2)'::my_integer)
+)
+FROM generate_series(1, 4) x;
+
+-- should not be precalculated
+SELECT x_stl2_boolean((1 === 2) IS DISTINCT FROM TRUE)
+FROM generate_series(1, 4) x;
+
+DROP OPERATOR = (my_integer, my_integer);
+CREATE OPERATOR = (
+ PROCEDURE = equal_my_integer_stl,
+ LEFTARG = my_integer,
+ RIGHTARG = my_integer
+);
+
+SELECT (
+ ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) ======
+ ('(2)'::my_integer IS DISTINCT FROM '(2)'::my_integer)
+)
+FROM generate_series(1, 4) x;
+
+SELECT x_stl2_boolean((1 ==== 2) IS DISTINCT FROM TRUE)
+FROM generate_series(1, 4) x;
+
+-- Mixed operators and NULLIF expressions testing
+
+DROP OPERATOR = (my_integer, my_integer);
+CREATE OPERATOR = (
+ PROCEDURE = equal_my_integer_vlt,
+ LEFTARG = my_integer,
+ RIGHTARG = my_integer
+);
+
+-- should not be precalculated
+SELECT (
+ NULLIF('(1)'::my_integer, '(2)'::my_integer) ====
+ NULLIF('(2)'::my_integer, '(2)'::my_integer)
+)
+FROM generate_series(1, 4) x;
+
+-- should not be precalculated
+SELECT x_stl2_boolean(NULLIF(1 === 2, TRUE)) FROM generate_series(1, 4) x;
+
+DROP OPERATOR = (my_integer, my_integer);
+CREATE OPERATOR = (
+ PROCEDURE = equal_my_integer_stl,
+ LEFTARG = my_integer,
+ RIGHTARG = my_integer
+);
+
+SELECT (
+ NULLIF('(1)'::my_integer, '(2)'::my_integer) ====
+ NULLIF('(2)'::my_integer, '(2)'::my_integer)
+)
+FROM generate_series(1, 4) x;
+
+SELECT x_stl2_boolean(NULLIF(1 ==== 2, TRUE)) FROM generate_series(1, 4) x;
+
+-- Mixed operators and "scalar op ANY/ALL (array)" / "scalar IN (2 or more
+-- values)" expressions testing
+
+-- should not be precalculated
+SELECT (1 === ANY('{2, 3}')) ====== (1 === ALL('{2, 3}'))
+FROM generate_series(1, 4) x;
+
+DROP OPERATOR = (my_integer, my_integer);
+CREATE OPERATOR = (
+ PROCEDURE = equal_my_integer_vlt,
+ LEFTARG = my_integer,
+ RIGHTARG = my_integer
+);
+
+-- should not be precalculated
+SELECT ('(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer)) ====== TRUE
+FROM generate_series(1, 4) x;
+
+-- should not be precalculated
+SELECT x_stl2_boolean((1 === 2) = ANY('{TRUE}'))
+FROM generate_series(1, 4) x;
+
+-- should not be precalculated
+SELECT x_stl2_boolean((1 === 2) = ALL('{TRUE}'))
+FROM generate_series(1, 4) x;
+
+-- should not be precalculated
+SELECT x_stl2_boolean((1 === 2) IN (TRUE, FALSE))
+FROM generate_series(1, 4) x;
+
+SELECT (1 ==== ANY('{2, 3}')) ====== (1 ==== ALL('{2, 3}'))
+FROM generate_series(1, 4) x;
+
+DROP OPERATOR = (my_integer, my_integer);
+CREATE OPERATOR = (
+ PROCEDURE = equal_my_integer_stl,
+ LEFTARG = my_integer,
+ RIGHTARG = my_integer
+);
+
+SELECT ('(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer)) ====== TRUE
+FROM generate_series(1, 4) x;
+
+SELECT x_stl2_boolean((1 ==== 2) = ANY('{TRUE}'))
+FROM generate_series(1, 4) x;
+
+SELECT x_stl2_boolean((1 ==== 2) = ALL('{TRUE}'))
+FROM generate_series(1, 4) x;
+
+SELECT x_stl2_boolean((1 ==== 2) IN (TRUE, FALSE))
+FROM generate_series(1, 4) x;
+
+-- Mixed IS DISTINCT FROM and NULLIF expressions testing
+
+DROP OPERATOR = (my_integer, my_integer);
+CREATE OPERATOR = (
+ PROCEDURE = equal_my_integer_vlt,
+ LEFTARG = my_integer,
+ RIGHTARG = my_integer
+);
+
+-- should not be precalculated
+SELECT (
+ NULLIF('(1)'::my_integer, '(2)'::my_integer) IS DISTINCT FROM
+ NULLIF('(2)'::my_integer, '(2)'::my_integer)
+)
+FROM generate_series(1, 4) x;
+
+-- should not be precalculated
+SELECT NULLIF(
+ ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer),
+ ('(2)'::my_integer IS DISTINCT FROM '(2)'::my_integer)
+)
+FROM generate_series(1, 4) x;
+
+DROP OPERATOR = (my_integer, my_integer);
+CREATE OPERATOR = (
+ PROCEDURE = equal_my_integer_stl,
+ LEFTARG = my_integer,
+ RIGHTARG = my_integer
+);
+
+SELECT (
+ NULLIF('(1)'::my_integer, '(2)'::my_integer) IS DISTINCT FROM
+ NULLIF('(2)'::my_integer, '(2)'::my_integer)
+)
+FROM generate_series(1, 4) x;
+
+SELECT NULLIF(
+ ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer),
+ ('(2)'::my_integer IS DISTINCT FROM '(2)'::my_integer)
+)
+FROM generate_series(1, 4) x;
+
+-- Mixed IS DISTINCT FROM and "scalar op ANY/ALL (array)" / "scalar IN (2 or
+-- more values)" expressions testing
+
+-- should not be precalculated
+SELECT x_stl2_boolean(
+ (1 === ANY('{2, 3}')) IS DISTINCT FROM
+ (1 === ALL('{2, 3}'))
+)
+FROM generate_series(1, 4) x;
+
+DROP OPERATOR = (my_integer, my_integer);
+CREATE OPERATOR = (
+ PROCEDURE = equal_my_integer_vlt,
+ LEFTARG = my_integer,
+ RIGHTARG = my_integer
+);
+
+-- should not be precalculated
+SELECT x_stl2_boolean(
+ ('(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer)) IS DISTINCT FROM
+ TRUE
+)
+FROM generate_series(1, 4) x;
+
+-- should not be precalculated
+SELECT x_stl2_boolean(
+ ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) = ANY('{TRUE}')
+)
+FROM generate_series(1, 4) x;
+
+-- should not be precalculated
+SELECT x_stl2_boolean(
+ ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) = ALL('{TRUE}')
+)
+FROM generate_series(1, 4) x;
+
+-- should not be precalculated
+SELECT x_stl2_boolean(
+ ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) IN (TRUE, FALSE)
+)
+FROM generate_series(1, 4) x;
+
+SELECT x_stl2_boolean(
+ (1 ==== ANY('{2, 3}')) IS DISTINCT FROM
+ (1 ==== ALL('{2, 3}'))
+)
+FROM generate_series(1, 4) x;
+
+DROP OPERATOR = (my_integer, my_integer);
+CREATE OPERATOR = (
+ PROCEDURE = equal_my_integer_stl,
+ LEFTARG = my_integer,
+ RIGHTARG = my_integer
+);
+
+SELECT x_stl2_boolean(
+ ('(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer)) IS DISTINCT FROM
+ TRUE
+)
+FROM generate_series(1, 4) x;
+
+SELECT x_stl2_boolean(
+ ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) = ANY('{TRUE}')
+)
+FROM generate_series(1, 4) x;
+
+SELECT x_stl2_boolean(
+ ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) = ALL('{TRUE}')
+)
+FROM generate_series(1, 4) x;
+
+SELECT x_stl2_boolean(
+ ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) IN (TRUE, FALSE)
+)
+FROM generate_series(1, 4) x;
+
+-- Mixed NULLIF and "scalar op ANY/ALL (array)" / "scalar IN (2 or more values)"
+-- expressions testing
+
+-- should not be precalculated
+SELECT x_stl2_boolean(NULLIF(1 === ANY('{2, 3}'), 1 === ALL('{2, 3}')))
+FROM generate_series(1, 4) x;
+
+DROP OPERATOR = (my_integer, my_integer);
+CREATE OPERATOR = (
+ PROCEDURE = equal_my_integer_vlt,
+ LEFTARG = my_integer,
+ RIGHTARG = my_integer
+);
+
+-- should not be precalculated
+SELECT x_stl2_boolean(NULLIF(
+ '(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer),
+ TRUE
+))
+FROM generate_series(1, 4) x;
+
+-- should not be precalculated
+SELECT (
+ NULLIF('(1)'::my_integer, '(2)'::my_integer) ====
+ ANY('{(3)}'::my_integer[])
+)
+FROM generate_series(1, 4) x;
+
+-- should not be precalculated
+SELECT (
+ NULLIF('(1)'::my_integer, '(2)'::my_integer) ====
+ ALL('{(3)}'::my_integer[])
+)
+FROM generate_series(1, 4) x;
+
+-- should not be precalculated
+SELECT (
+ NULLIF('(1)'::my_integer, '(2)'::my_integer) IN
+ ('(3)'::my_integer, '(2)'::my_integer)
+)
+FROM generate_series(1, 4) x;
+
+SELECT x_stl2_boolean(NULLIF(1 ==== ANY('{2, 3}'), 1 ==== ALL('{2, 3}')))
+FROM generate_series(1, 4) x;
+
+DROP OPERATOR = (my_integer, my_integer);
+CREATE OPERATOR = (
+ PROCEDURE = equal_my_integer_stl,
+ LEFTARG = my_integer,
+ RIGHTARG = my_integer
+);
+
+SELECT x_stl2_boolean(NULLIF(
+ '(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer),
+ TRUE
+))
+FROM generate_series(1, 4) x;
+
+SELECT (
+ NULLIF('(1)'::my_integer, '(2)'::my_integer) ====
+ ANY('{(3)}'::my_integer[])
+)
+FROM generate_series(1, 4) x;
+
+SELECT (
+ NULLIF('(1)'::my_integer, '(2)'::my_integer) ====
+ ALL('{(3)}'::my_integer[])
+)
+FROM generate_series(1, 4) x;
+
+SELECT (
+ NULLIF('(1)'::my_integer, '(2)'::my_integer) IN
+ ('(3)'::my_integer, '(2)'::my_integer)
+)
+FROM generate_series(1, 4) x;
+
+-- Tracking functions testing
+
+SET track_functions TO 'all';
+
+SELECT x_vlt() FROM generate_series(1, 3) x; -- should not be precalculated
+SELECT x_stl() FROM generate_series(1, 3) x;
+
+SELECT x_vlt() FROM generate_series(1, 4) x WHERE x_vlt() < x; -- should not be precalculated
+SELECT x_stl() FROM generate_series(1, 4) x WHERE x_stl() < x;
+
+SELECT x_stl2(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
+SELECT x_imm2(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
+
+SELECT x_stl2(x_stl2(1)) FROM generate_series(1, 4) x;
+SELECT x_imm2(x_stl2(1)) FROM generate_series(1, 4) x;
+
+SELECT x_stl2_strict(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
+SELECT x_imm2_strict(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
+
+SELECT x_stl2_strict(x_stl2_strict(1)) FROM generate_series(1, 4) x;
+SELECT x_imm2_strict(x_stl2_strict(1)) FROM generate_series(1, 4) x;
+
+SELECT x_stl2_strict(x_stl2(NULL)) FROM generate_series(1, 4) x;
+SELECT x_imm2_strict(x_stl2(NULL)) FROM generate_series(1, 4) x;
+
+SELECT 1 === 2 FROM generate_series(1, 4) x; -- should not be precalculated
+SELECT 1 ==== 2 FROM generate_series(1, 4) x;
+SELECT 1 ===== 2 FROM generate_series(1, 4) x;
+
+SELECT (x_vlt() ==== 2) ====== (x_vlt() ===== 3) FROM generate_series(1, 4) x; -- should not be precalculated
+SELECT (1 ==== 2) ====== (3 ==== 3) FROM generate_series(1, 4) x;
+SELECT x_stl2_boolean(NULL) ====== (3 ==== 3) FROM generate_series(1, 4) x;
+
+SELECT x_vlt() ==== 2 FROM generate_series(1, 4) x; -- should not be precalculated
+SELECT x_vlt() ===== 2 FROM generate_series(1, 4) x; -- should not be precalculated
+
+SELECT x_stl() ==== x_stl() FROM generate_series(1, 4) x;
+SELECT x_stl2_boolean(1 ==== 2) FROM generate_series(1, 4) x;
+
+SET track_functions TO DEFAULT;
+
+-- PL/pgSQL Simple expressions
+-- Make sure precalculated stable functions can't be simple expressions: these
+-- expressions are only initialized once per transaction and then executed
+-- multiple times.
+
+BEGIN;
+SELECT simple();
+INSERT INTO two VALUES (3);
+SELECT simple();
+ROLLBACK;
+
+-- Drop tables for testing
+
+DROP TABLE two;
--
1.9.1
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers