From 6cf52e350abf475901e52c3436f015e5020d17c9 Mon Sep 17 00:00:00 2001 From: Marina Polyakova Date: Mon, 17 Jul 2017 11:08:42 +0300 Subject: [PATCH v5 2/3] Precalculate stable functions, planning and execution 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 expressions by appropriate cached expressions - planning and execution cached expressions - regression tests - changed documentation --- doc/src/sgml/ref/create_function.sgml | 14 + doc/src/sgml/xfunc.sgml | 14 +- src/backend/executor/execExpr.c | 32 + src/backend/executor/execExprInterp.c | 52 + src/backend/optimizer/path/allpaths.c | 9 +- src/backend/optimizer/path/clausesel.c | 12 + src/backend/optimizer/plan/planagg.c | 1 + src/backend/optimizer/plan/planner.c | 40 + src/backend/optimizer/util/clauses.c | 26 + src/backend/utils/adt/ruleutils.c | 5 + src/include/executor/execExpr.h | 31 + src/include/optimizer/planner.h | 5 + src/include/optimizer/tlist.h | 8 +- src/pl/plpgsql/src/pl_exec.c | 10 + .../expected/precalculate_stable_functions.out | 6306 ++++++++++++++++++++ src/test/regress/serial_schedule | 1 + .../regress/sql/precalculate_stable_functions.sql | 1843 ++++++ 17 files changed, 8405 insertions(+), 4 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/doc/src/sgml/ref/create_function.sgml b/doc/src/sgml/ref/create_function.sgml index 072e033..dfdc3b5 100644 --- a/doc/src/sgml/ref/create_function.sgml +++ b/doc/src/sgml/ref/create_function.sgml @@ -337,6 +337,20 @@ CREATE [ OR REPLACE ] FUNCTION setval(). + + + Stable, immutable functions and other nonovolatile expressions are + precalculated (= calculated once for all output rows, but as many times + as expression is mentioned in query), if they don't return a set and + their arguments are constants or recursively precalculated expressions. + + + + Now this feature is not supported for the generic plans of prepared + statements (see ). + + + For additional details see . diff --git a/doc/src/sgml/xfunc.sgml b/doc/src/sgml/xfunc.sgml index cd6dd84..f97b637 100644 --- a/doc/src/sgml/xfunc.sgml +++ b/doc/src/sgml/xfunc.sgml @@ -1467,9 +1467,21 @@ CREATE FUNCTION test(int, int) RETURNS int For best optimization results, you should label your functions with the - strictest volatility category that is valid for them. + strictest volatility category that is valid for them. Stable, immutable + functions and other nonovolatile expressions are precalculated (= calculated + once for all output rows, but as many times as expression is mentioned in + query), if they don't return a set and their arguments are constants or + recursively precalculated expressions. + + + Now nonvolatile expressions with constant/precalculated arguments are not + precalculated in the generic plans of prepared statements (see ). + + + Any function with side-effects must be labeled VOLATILE, so that calls to it cannot be optimized away. diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c index 3523543..247b43f 100644 --- a/src/backend/executor/execExpr.c +++ b/src/backend/executor/execExpr.c @@ -791,6 +791,38 @@ ExecInitExprRec(Expr *node, PlanState *parent, ExprState *state, break; } + case T_CachedExpr: + { + /* + * Allocate CachedExprState used by all steps of CachedExpr + * evaluation. + */ + scratch.d.cachedexpr.state = (CachedExprState *) palloc( + sizeof(CachedExprState)); + scratch.d.cachedexpr.state->isExecuted = false; + scratch.d.cachedexpr.state->resnull = false; + scratch.d.cachedexpr.state->resvalue = (Datum) 0; + scratch.d.cachedexpr.state->restypid = exprType( + (const Node *) node); + + /* add EEOP_CACHEDEXPR_IF_CACHED step */ + scratch.opcode = EEOP_CACHEDEXPR_IF_CACHED; + ExprEvalPushStep(state, &scratch); + + /* add subexpression steps */ + ExecInitExprRec((Expr *) ((CachedExpr *) node)->subexpr, parent, + state, resv, resnull); + + /* add EEOP_CACHEDEXPR_SUBEXPR_END step */ + scratch.opcode = EEOP_CACHEDEXPR_SUBEXPR_END; + ExprEvalPushStep(state, &scratch); + + /* adjust jump target */ + scratch.d.cachedexpr.state->jumpdone = state->steps_len; + + break; + } + case T_ArrayRef: { ArrayRef *aref = (ArrayRef *) node; diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c index c227d9b..1a810e9 100644 --- a/src/backend/executor/execExprInterp.c +++ b/src/backend/executor/execExprInterp.c @@ -70,6 +70,7 @@ #include "pgstat.h" #include "utils/builtins.h" #include "utils/date.h" +#include "utils/datum.h" #include "utils/lsyscache.h" #include "utils/timestamp.h" #include "utils/typcache.h" @@ -363,6 +364,8 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull) &&CASE_EEOP_WINDOW_FUNC, &&CASE_EEOP_SUBPLAN, &&CASE_EEOP_ALTERNATIVE_SUBPLAN, + &&CASE_EEOP_CACHEDEXPR_IF_CACHED, + &&CASE_EEOP_CACHEDEXPR_SUBEXPR_END, &&CASE_EEOP_LAST }; @@ -1500,6 +1503,55 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull) EEO_NEXT(); } + EEO_CASE(EEOP_CACHEDEXPR_IF_CACHED) + { + if (op->d.cachedexpr.state->isExecuted) + { + /* use saved result and skip subexpression evaluation */ + *op->resnull = op->d.cachedexpr.state->resnull; + if (!(*op->resnull)) + *op->resvalue = op->d.cachedexpr.state->resvalue; + + EEO_JUMP(op->d.cachedexpr.state->jumpdone); + } + + /* we are ready for subexpression evaluation */ + EEO_NEXT(); + } + + EEO_CASE(EEOP_CACHEDEXPR_SUBEXPR_END) + { + int16 restyplen; + bool restypbyval; + MemoryContext oldContext; + + /* save result */ + op->d.cachedexpr.state->resnull = *op->resnull; + if (!(*op->resnull)) + { + get_typlenbyval(op->d.cachedexpr.state->restypid, &restyplen, + &restypbyval); + + /* + * Switch per-query memory context. It is necessary to save the + * subexpression result between all tuples if its value datum is + * a pointer. + */ + oldContext = MemoryContextSwitchTo( + econtext->ecxt_per_query_memory); + + op->d.cachedexpr.state->resvalue = datumCopy(*op->resvalue, + restypbyval, + restyplen); + + /* switch memory context back */ + MemoryContextSwitchTo(oldContext); + } + op->d.cachedexpr.state->isExecuted = true; + + EEO_NEXT(); + } + EEO_CASE(EEOP_SUBPLAN) { /* too complex for an inline implementation */ diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c index f087ddb..341b519 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, root); + 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, root); + /* 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 9d34025..a504336 100644 --- a/src/backend/optimizer/path/clausesel.c +++ b/src/backend/optimizer/path/clausesel.c @@ -825,6 +825,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, + (Node *) ((CachedExpr *) clause)->subexpr, + varRelid, + jointype, + sjinfo); + } else { /* diff --git a/src/backend/optimizer/plan/planagg.c b/src/backend/optimizer/plan/planagg.c index bba8a1f..a8b7991 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 bce4a41..0490d56 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -6102,6 +6102,46 @@ 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, PlannerInfo *root) +{ + replace_cached_expressions_context context; + + context.root = root; + context.innermost_caseexpr_nonconst_or_noncached_testvalue = NULL; + context.innermost_coercetodomain_nonconst_or_noncached_value = NULL; + + target->exprs = (List *) replace_cached_expressions_mutator( + (Node *) target->exprs, &context); + + 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, PlannerInfo *root) +{ + replace_cached_expressions_context context; + + context.root = root; + context.innermost_caseexpr_nonconst_or_noncached_testvalue = NULL; + context.innermost_coercetodomain_nonconst_or_noncached_value = NULL; + + return (List *) replace_cached_expressions_mutator((Node *) quals, + &context); +} + static Node * replace_cached_expressions_mutator(Node *node, replace_cached_expressions_context *context) diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c index 8961ed8..7b72335 100644 --- a/src/backend/optimizer/util/clauses.c +++ b/src/backend/optimizer/util/clauses.c @@ -2553,6 +2553,32 @@ eval_const_expressions_mutator(Node *node, return (Node *) newexpr; } + case T_CachedExpr: + { + CachedExpr *cachedexpr = (CachedExpr *) node; + CacheableExpr *new_subexpr = (CacheableExpr *) + eval_const_expressions_mutator((Node *) cachedexpr->subexpr, + context); + CachedExpr *new_cachedexpr; + + if (IsA(new_subexpr, Const)) + { + /* successfully simplified it */ + return (Node *) 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->subexpr = new_subexpr; + + return (Node *) new_cachedexpr; + } + } case T_FuncExpr: { FuncExpr *expr = (FuncExpr *) node; diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 5cfb916..80dd539 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -7629,6 +7629,11 @@ get_rule_expr(Node *node, deparse_context *context, get_windowfunc_expr((WindowFunc *) node, context); break; + case T_CachedExpr: + get_rule_expr((Node *) ((CachedExpr *) node)->subexpr, context, + showimplicit); + break; + case T_ArrayRef: { ArrayRef *aref = (ArrayRef *) node; diff --git a/src/include/executor/execExpr.h b/src/include/executor/execExpr.h index 7a65339..da0c74e 100644 --- a/src/include/executor/execExpr.h +++ b/src/include/executor/execExpr.h @@ -212,6 +212,16 @@ typedef enum ExprEvalOp EEOP_SUBPLAN, EEOP_ALTERNATIVE_SUBPLAN, + /* + * 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, + /* non-existent operation, used e.g. to check array lengths */ EEOP_LAST } ExprEvalOp; @@ -560,6 +570,13 @@ typedef struct ExprEvalStep /* out-of-line state, created by nodeSubplan.c */ AlternativeSubPlanState *asstate; } alternative_subplan; + + /* for EEOP_CACHEDEXPR_* */ + struct + { + /* steps for evaluation the same CachedExpr have the same state */ + struct CachedExprState *state; + } cachedexpr; } d; } ExprEvalStep; @@ -600,6 +617,20 @@ typedef struct ArrayRefState } ArrayRefState; +/* + * Non-inline data for EEOP_CACHEDEXPR_* operations (steps for evaluation the + * same CachedExpr have the same state). + */ +typedef struct CachedExprState +{ + bool isExecuted; + bool resnull; + Datum resvalue; + Oid restypid; /* for copying resvalue of subexpression */ + int jumpdone; /* jump here if result determined */ +} CachedExprState; + + extern void ExecReadyInterpretedExpr(ExprState *state); extern ExprEvalOp ExecEvalStepOp(ExprState *state, ExprEvalStep *op); diff --git a/src/include/optimizer/planner.h b/src/include/optimizer/planner.h index 2a4cf71..1a64696 100644 --- a/src/include/optimizer/planner.h +++ b/src/include/optimizer/planner.h @@ -59,4 +59,9 @@ 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, + PlannerInfo *root); + +extern List *replace_qual_cached_expressions(List *quals, PlannerInfo *root); + #endif /* PLANNER_H */ diff --git a/src/include/optimizer/tlist.h b/src/include/optimizer/tlist.h index 0d3ec92..1b4fd8f 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), root)) #endif /* TLIST_H */ diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c index c98492b..16199f1 100644 --- a/src/pl/plpgsql/src/pl_exec.c +++ b/src/pl/plpgsql/src/pl_exec.c @@ -6471,6 +6471,16 @@ exec_simple_check_node(Node *node) case T_Param: 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_ArrayRef: { ArrayRef *expr = (ArrayRef *) 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..fd187f5 --- /dev/null +++ b/src/test/regress/expected/precalculate_stable_functions.out @@ -0,0 +1,6306 @@ +-- +-- PRECALCULATE STABLE FUNCTIONS +-- +-- Create types and tables for testing +CREATE TYPE my_integer AS (value integer); +CREATE TYPE composite_type AS (first integer, second integer[], third boolean); +CREATE TABLE x (x integer); +INSERT INTO x SELECT generate_series(1, 4) x; +CREATE TABLE wxyz (w integer, x integer[], y boolean, z integer); +CREATE TABLE wxyz_child () INHERITS (wxyz); +CREATE TABLE wxyz_child2 (a integer, b integer) INHERITS (wxyz); +CREATE TABLE no_columns (); +CREATE TABLE no_columns_child () INHERITS (no_columns); +CREATE TABLE no_columns_child2 (a integer, b integer) INHERITS (no_columns); +-- 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.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.x_vlt_array_integer ( +) +RETURNS int[] VOLATILE AS +$body$ +BEGIN + RAISE NOTICE 'v array_integer'; + RETURN '{2, 3}'::integer[]; +END; +$body$ +LANGUAGE 'plpgsql'; +CREATE OR REPLACE FUNCTION public.x_vlt_boolean ( +) +RETURNS boolean VOLATILE AS +$body$ +BEGIN + RAISE NOTICE 'v boolean'; + RETURN TRUE; +END; +$body$ +LANGUAGE 'plpgsql'; +CREATE OR REPLACE FUNCTION public.x_vlt_wxyz ( +) +RETURNS wxyz VOLATILE AS +$body$ +BEGIN + RAISE NOTICE 'v wxyz'; + RETURN '(1, {2}, TRUE, 3)'::wxyz; +END; +$body$ +LANGUAGE 'plpgsql'; +CREATE OR REPLACE FUNCTION public.x_vlt_wxyz_child ( +) +RETURNS wxyz_child VOLATILE AS +$body$ +BEGIN + RAISE NOTICE 'v wxyz_child'; + RETURN '(1, {2}, TRUE, 3)'::wxyz_child; +END; +$body$ +LANGUAGE 'plpgsql'; +CREATE OR REPLACE FUNCTION public.x_vlt_wxyz_child2 ( +) +RETURNS wxyz_child2 VOLATILE AS +$body$ +BEGIN + RAISE NOTICE 'v wxyz_child2'; + RETURN '(1, {2}, TRUE, 3, 4, 5)'::wxyz_child2; +END; +$body$ +LANGUAGE 'plpgsql'; +CREATE OR REPLACE FUNCTION public.x_vlt_oid ( +) +RETURNS oid VOLATILE AS +$body$ +BEGIN + RAISE NOTICE 'v oid'; + RETURN 1; +END; +$body$ +LANGUAGE 'plpgsql'; +CREATE OR REPLACE FUNCTION public.x_vlt_text_integer ( +) +RETURNS text VOLATILE AS +$body$ +BEGIN + RAISE NOTICE 'v text integer'; + RETURN 1::text; +END; +$body$ +LANGUAGE 'plpgsql'; +CREATE OR REPLACE FUNCTION public.x_vlt_text_my_integer ( +) +RETURNS text VOLATILE AS +$body$ +BEGIN + RAISE NOTICE 'v text my_integer'; + RETURN '(1)'::text; +END; +$body$ +LANGUAGE 'plpgsql'; +CREATE OR REPLACE FUNCTION public.x_vlt_text_xml ( +) +RETURNS text VOLATILE AS +$body$ +BEGIN + RAISE NOTICE 'v text xml'; + RETURN 'Manual'::text; +END; +$body$ +LANGUAGE 'plpgsql'; +CREATE OR REPLACE FUNCTION public.x_vlt_text_xml_content ( +) +RETURNS text VOLATILE AS +$body$ +BEGIN + RAISE NOTICE 'v text xml content'; + RETURN 'abcbarfoo'::text; +END; +$body$ +LANGUAGE 'plpgsql'; +CREATE OR REPLACE FUNCTION public.x_vlt_text_xml_instruction_content ( +) +RETURNS text VOLATILE AS +$body$ +BEGIN + RAISE NOTICE 'v text xml instruction content'; + RETURN 'echo "hello world";'::text; +END; +$body$ +LANGUAGE 'plpgsql'; +CREATE OR REPLACE FUNCTION public.x_vlt_xml ( +) +RETURNS xml VOLATILE AS +$body$ +BEGIN + RAISE NOTICE 'v xml'; + RETURN 'foo'::xml; +END; +$body$ +LANGUAGE 'plpgsql'; +CREATE OR REPLACE FUNCTION public.x_vlt_xml_content ( +) +RETURNS xml VOLATILE AS +$body$ +BEGIN + RAISE NOTICE 'v xml content'; + RETURN 'abcbarfoo'::xml; +END; +$body$ +LANGUAGE 'plpgsql'; +CREATE OR REPLACE FUNCTION public.x_vlt2 ( + integer +) +RETURNS integer VOLATILE AS +$body$ +BEGIN + RAISE NOTICE 'v2'; + 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.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.cast_integer_as_my_integer_vlt ( + integer +) +RETURNS my_integer VOLATILE AS +$body$ +BEGIN + RAISE NOTICE 'cast integer as my_integer volatile'; + RETURN ROW($1)::my_integer; +END; +$body$ +LANGUAGE 'plpgsql'; +CREATE OR REPLACE FUNCTION public.cast_my_integer_as_integer_vlt ( + my_integer +) +RETURNS integer VOLATILE AS +$body$ +BEGIN + RAISE NOTICE 'cast my_integer as integer volatile'; + RETURN $1.value; +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_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.x_stl_array_integer ( +) +RETURNS int[] STABLE AS +$body$ +BEGIN + RAISE NOTICE 's array_integer'; + RETURN '{2, 3}'::integer[]; +END; +$body$ +LANGUAGE 'plpgsql'; +CREATE OR REPLACE FUNCTION public.x_stl_wxyz ( +) +RETURNS wxyz STABLE AS +$body$ +BEGIN + RAISE NOTICE 's wxyz'; + RETURN '(1, {2}, TRUE, 3)'::wxyz; +END; +$body$ +LANGUAGE 'plpgsql'; +CREATE OR REPLACE FUNCTION public.x_stl_wxyz_child ( +) +RETURNS wxyz_child STABLE AS +$body$ +BEGIN + RAISE NOTICE 's wxyz_child'; + RETURN '(1, {2}, TRUE, 3)'::wxyz_child; +END; +$body$ +LANGUAGE 'plpgsql'; +CREATE OR REPLACE FUNCTION public.x_stl_wxyz_child2 ( +) +RETURNS wxyz_child2 STABLE AS +$body$ +BEGIN + RAISE NOTICE 's wxyz_child2'; + RETURN '(1, {2}, TRUE, 3, 4, 5)'::wxyz_child2; +END; +$body$ +LANGUAGE 'plpgsql'; +CREATE OR REPLACE FUNCTION public.x_stl_oid ( +) +RETURNS oid STABLE AS +$body$ +BEGIN + RAISE NOTICE 's oid'; + RETURN 1; +END; +$body$ +LANGUAGE 'plpgsql'; +CREATE OR REPLACE FUNCTION public.x_stl_text_integer ( +) +RETURNS text STABLE AS +$body$ +BEGIN + RAISE NOTICE 's text integer'; + RETURN 1::text; +END; +$body$ +LANGUAGE 'plpgsql'; +CREATE OR REPLACE FUNCTION public.x_stl_text_my_integer ( +) +RETURNS text STABLE AS +$body$ +BEGIN + RAISE NOTICE 's text my_integer'; + RETURN '(1)'::text; +END; +$body$ +LANGUAGE 'plpgsql'; +CREATE OR REPLACE FUNCTION public.x_stl_text_xml ( +) +RETURNS text STABLE AS +$body$ +BEGIN + RAISE NOTICE 's text xml'; + RETURN 'Manual'::text; +END; +$body$ +LANGUAGE 'plpgsql'; +CREATE OR REPLACE FUNCTION public.x_stl_text_xml_content ( +) +RETURNS text STABLE AS +$body$ +BEGIN + RAISE NOTICE 's xml content'; + RETURN 'abcbarfoo'::text; +END; +$body$ +LANGUAGE 'plpgsql'; +CREATE OR REPLACE FUNCTION public.x_stl_text_xml_instruction_content ( +) +RETURNS text STABLE AS +$body$ +BEGIN + RAISE NOTICE 's text xml instruction content'; + RETURN 'echo "hello world";'::text; +END; +$body$ +LANGUAGE 'plpgsql'; +CREATE OR REPLACE FUNCTION public.x_stl_xml ( +) +RETURNS xml STABLE AS +$body$ +BEGIN + RAISE NOTICE 's xml'; + RETURN 'foo'::xml; +END; +$body$ +LANGUAGE 'plpgsql'; +CREATE OR REPLACE FUNCTION public.x_stl_xml_content ( +) +RETURNS xml STABLE AS +$body$ +BEGIN + RAISE NOTICE 's xml content'; + RETURN 'abcbarfoo'::xml; +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.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.x_stl2_array_integer ( + integer[] +) +RETURNS integer[] STABLE AS +$body$ +BEGIN + RAISE NOTICE 's2 array_integer'; + RETURN $1; +END; +$body$ +LANGUAGE 'plpgsql'; +CREATE OR REPLACE FUNCTION public.x_stl2_array_oid ( + oid[] +) +RETURNS oid[] STABLE AS +$body$ +BEGIN + RAISE NOTICE 's2 array_oid'; + RETURN $1; +END; +$body$ +LANGUAGE 'plpgsql'; +CREATE OR REPLACE FUNCTION public.x_stl2_wxyz ( + wxyz +) +RETURNS wxyz STABLE AS +$body$ +BEGIN + RAISE NOTICE 's2 wxyz'; + RETURN $1; +END; +$body$ +LANGUAGE 'plpgsql'; +CREATE OR REPLACE FUNCTION public.x_stl2_composite_type ( + composite_type +) +RETURNS composite_type STABLE AS +$body$ +BEGIN + RAISE NOTICE 's2 composite_type'; + RETURN $1; +END; +$body$ +LANGUAGE 'plpgsql'; +CREATE OR REPLACE FUNCTION public.x_stl2_my_integer ( + my_integer +) +RETURNS my_integer STABLE AS +$body$ +BEGIN + RAISE NOTICE 's2 my_integer'; + RETURN $1; +END; +$body$ +LANGUAGE 'plpgsql'; +CREATE OR REPLACE FUNCTION public.x_stl2_no_columns ( + no_columns +) +RETURNS no_columns STABLE AS +$body$ +BEGIN + RAISE NOTICE 's2 no_columns'; + RETURN $1; +END; +$body$ +LANGUAGE 'plpgsql'; +CREATE OR REPLACE FUNCTION public.x_stl2_name ( + name +) +RETURNS name STABLE AS +$body$ +BEGIN + RAISE NOTICE 's2 name'; + RETURN $1; +END; +$body$ +LANGUAGE 'plpgsql'; +CREATE OR REPLACE FUNCTION public.x_stl2_xml ( + xml +) +RETURNS xml STABLE AS +$body$ +BEGIN + RAISE NOTICE 's2 xml'; + RETURN $1; +END; +$body$ +LANGUAGE 'plpgsql'; +CREATE OR REPLACE FUNCTION public.x_stl2_text ( + text +) +RETURNS text STABLE AS +$body$ +BEGIN + RAISE NOTICE 's2 text'; + 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.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.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.cast_integer_as_my_integer_stl ( + integer +) +RETURNS my_integer STABLE AS +$body$ +BEGIN + RAISE NOTICE 'cast integer as my_integer stable'; + RETURN ROW($1)::my_integer; +END; +$body$ +LANGUAGE 'plpgsql'; +CREATE OR REPLACE FUNCTION public.cast_my_integer_as_integer_stl ( + my_integer +) +RETURNS integer STABLE AS +$body$ +BEGIN + RAISE NOTICE 'cast my_integer as integer stable'; + RETURN $1.value; +END; +$body$ +LANGUAGE 'plpgsql'; +CREATE OR REPLACE FUNCTION public.stable_max( +) +RETURNS integer STABLE AS +$body$ +BEGIN + RETURN (SELECT max(x) from x); +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.x_imm2_my_integer ( + my_integer +) +RETURNS my_integer IMMUTABLE AS +$body$ +BEGIN + RAISE NOTICE 'i2 my_integer'; + 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_booleans_imm ( + boolean, + boolean +) +RETURNS boolean IMMUTABLE AS +$body$ +BEGIN + RAISE NOTICE 'equal booleans 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_booleans_imm, + LEFTARG = boolean, + RIGHTARG = boolean +); +-- Create domains for testing +CREATE DOMAIN my_integer_no_check AS integer; +CREATE DOMAIN my_integer_not_null AS integer; +CREATE DOMAIN my_integer_vlt_check AS integer CHECK (VALUE === 1); +CREATE DOMAIN my_integer_stl_check AS integer CHECK (VALUE ==== 1); +CREATE DOMAIN my_integer_imm_check AS integer CHECK (VALUE ===== 1); +CREATE OR REPLACE FUNCTION public.x_stl2_my_integer_no_check ( + my_integer_no_check +) +RETURNS my_integer_no_check STABLE AS +$body$ +BEGIN + RAISE NOTICE 's2 my_integer_no_check'; + RETURN $1; +END; +$body$ +LANGUAGE 'plpgsql'; +CREATE OR REPLACE FUNCTION public.x_stl2_my_integer_not_null ( + my_integer_not_null +) +RETURNS my_integer_not_null STABLE AS +$body$ +BEGIN + RAISE NOTICE 's2 my_integer_not_null'; + RETURN $1; +END; +$body$ +LANGUAGE 'plpgsql'; +CREATE OR REPLACE FUNCTION public.x_stl2_my_integer_vlt_check ( + my_integer_vlt_check +) +RETURNS my_integer_vlt_check STABLE AS +$body$ +BEGIN + RAISE NOTICE 's2 my_integer_vlt_check'; + RETURN $1; +END; +$body$ +LANGUAGE 'plpgsql'; +CREATE OR REPLACE FUNCTION public.x_stl2_my_integer_stl_check ( + my_integer_stl_check +) +RETURNS my_integer_stl_check STABLE AS +$body$ +BEGIN + RAISE NOTICE 's2 my_integer_stl_check'; + RETURN $1; +END; +$body$ +LANGUAGE 'plpgsql'; +CREATE OR REPLACE FUNCTION public.x_stl2_my_integer_imm_check ( + my_integer_imm_check +) +RETURNS my_integer_imm_check STABLE AS +$body$ +BEGIN + RAISE NOTICE 's2 my_integer_imm_check'; + RETURN $1; +END; +$body$ +LANGUAGE 'plpgsql'; +-- Functions testing +-- Simple functions testing +SELECT x_vlt() FROM x; -- should not be precalculated +NOTICE: v +NOTICE: v +NOTICE: v +NOTICE: v + x_vlt +------- + 1 + 1 + 1 + 1 +(4 rows) + +SELECT x_stl() FROM x; +NOTICE: s + x_stl +------- + 1 + 1 + 1 + 1 +(4 rows) + +-- WHERE clause testing +SELECT x_vlt() FROM 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 x WHERE x_stl() < x; +NOTICE: s +NOTICE: s +NOTICE: s + x_stl +------- + 1 + 1 + 1 +(3 rows) + +-- JOIN/ON clause testing +SELECT * FROM x JOIN generate_series(1, 2) y ON x_vlt() < x; -- should not be precalculated +NOTICE: v +NOTICE: v +NOTICE: v +NOTICE: v + x | y +---+--- + 2 | 1 + 3 | 1 + 4 | 1 + 2 | 2 + 3 | 2 + 4 | 2 +(6 rows) + +SELECT * FROM x JOIN generate_series(1, 2) y ON x_stl() < x; +NOTICE: s +NOTICE: s + x | y +---+--- + 2 | 1 + 3 | 1 + 4 | 1 + 2 | 2 + 3 | 2 + 4 | 2 +(6 rows) + +-- Functions with constant arguments testing +SELECT x_vlt2(1) FROM x; -- should not be precalculated +NOTICE: v2 +NOTICE: v2 +NOTICE: v2 +NOTICE: v2 + x_vlt2 +-------- + 1 + 1 + 1 + 1 +(4 rows) + +SELECT x_stl2(1) FROM x; +NOTICE: s2 + x_stl2 +-------- + 1 + 1 + 1 + 1 +(4 rows) + +-- Nested functions testing +SELECT x_stl2(x_vlt()) FROM 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 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_stl()) FROM x; +NOTICE: s +NOTICE: s2 + x_stl2 +-------- + 1 + 1 + 1 + 1 +(4 rows) + +SELECT x_imm2(x_stl()) FROM x; +NOTICE: s +NOTICE: i2 + x_imm2 +-------- + 1 + 1 + 1 + 1 +(4 rows) + +-- Strict functions testing +SELECT x_stl2_strict(x_vlt()) FROM 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 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 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 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 x; +NOTICE: s2 + x_stl2_strict +--------------- + + + + +(4 rows) + +SELECT x_imm2_strict(x_stl2(NULL)) FROM x; +NOTICE: s2 + x_imm2_strict +--------------- + + + + +(4 rows) + +-- Operators testing +SELECT 1 === 2 FROM 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 x; +NOTICE: equal integers stable + ?column? +---------- + f + f + f + f +(4 rows) + +-- Strict operators testing +SELECT x_stl2_boolean(NULL) ==== TRUE FROM x; +NOTICE: s2 boolean + ?column? +---------- + + + + +(4 rows) + +SELECT x_stl2_boolean(NULL) ===== TRUE FROM x; +NOTICE: s2 boolean +NOTICE: equal booleans immutable + ?column? +---------- + + + + +(4 rows) + +-- Mixed functions and operators testing +SELECT x_stl2_boolean(1 === 2) FROM x; -- should not be precalculated +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) + +SELECT x_stl2_boolean(1 ==== 2) FROM x; +NOTICE: equal integers stable +NOTICE: s2 boolean + x_stl2_boolean +---------------- + f + f + f + f +(4 rows) + +SELECT x_vlt() ==== 1 FROM 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? +---------- + t + t + t + t +(4 rows) + +SELECT x_stl() ==== 1 FROM x; +NOTICE: s +NOTICE: equal integers stable + ?column? +---------- + t + t + t + t +(4 rows) + +-- IS (NOT) 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 +); +SELECT '(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer FROM x; -- should not be precalculated +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) + +SELECT '(1)'::my_integer IS NOT DISTINCT FROM '(2)'::my_integer FROM x; -- should not be precalculated +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) + +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 x; +NOTICE: equal my_integer stable + ?column? +---------- + t + t + t + t +(4 rows) + +SELECT '(1)'::my_integer IS NOT DISTINCT FROM '(2)'::my_integer FROM x; +NOTICE: equal my_integer stable + ?column? +---------- + f + f + f + f +(4 rows) + +-- IS (NOT) DISTINCT FROM expressions with null arguments testing +SELECT x_stl2_boolean(x_stl2(NULL) IS DISTINCT FROM 1) FROM x; +NOTICE: s2 +NOTICE: s2 boolean + x_stl2_boolean +---------------- + t + t + t + t +(4 rows) + +SELECT x_stl2_boolean(x_stl2(NULL) IS NOT DISTINCT FROM 1) FROM x; +NOTICE: s2 +NOTICE: s2 boolean + x_stl2_boolean +---------------- + f + f + f + f +(4 rows) + +SELECT x_stl2_boolean(x_stl2(NULL) IS DISTINCT FROM x_stl2(NULL)) FROM x; +NOTICE: s2 +NOTICE: s2 +NOTICE: s2 boolean + x_stl2_boolean +---------------- + f + f + f + f +(4 rows) + +SELECT x_stl2_boolean(x_stl2(NULL) IS NOT DISTINCT FROM x_stl2(NULL)) FROM x; +NOTICE: s2 +NOTICE: s2 +NOTICE: s2 boolean + x_stl2_boolean +---------------- + t + t + t + t +(4 rows) + +-- Mixed functions and IS (NOT) 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( + ('(1)'::my_integer IS DISTINCT FROM '(1)'::my_integer), + ('(1)'::my_integer IS NOT DISTINCT FROM '(1)'::my_integer) +) +FROM 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 + equal_booleans_stl_strict +--------------------------- + 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 equal_booleans_stl_strict( + ('(1)'::my_integer IS DISTINCT FROM '(1)'::my_integer), + ('(1)'::my_integer IS NOT DISTINCT FROM '(1)'::my_integer) +) +FROM x; +NOTICE: equal my_integer stable +NOTICE: equal my_integer stable +NOTICE: equal booleans stable strict + equal_booleans_stl_strict +--------------------------- + f + f + f + f +(4 rows) + +SELECT (x_vlt_my_integer() IS DISTINCT FROM '(1)'::my_integer) FROM x; -- should not be precalculated +NOTICE: v my_integer +NOTICE: equal my_integer stable +NOTICE: v my_integer +NOTICE: equal my_integer stable +NOTICE: v my_integer +NOTICE: equal my_integer stable +NOTICE: v my_integer +NOTICE: equal my_integer stable + ?column? +---------- + f + f + f + f +(4 rows) + +SELECT (x_vlt_my_integer() IS NOT DISTINCT FROM '(1)'::my_integer) FROM x; -- should not be precalculated +NOTICE: v my_integer +NOTICE: equal my_integer stable +NOTICE: v my_integer +NOTICE: equal my_integer stable +NOTICE: v my_integer +NOTICE: equal my_integer stable +NOTICE: v my_integer +NOTICE: equal my_integer stable + ?column? +---------- + t + t + t + t +(4 rows) + +SELECT (x_stl_my_integer() IS DISTINCT FROM '(1)'::my_integer) FROM x; +NOTICE: s my_integer +NOTICE: equal my_integer stable + ?column? +---------- + f + f + f + f +(4 rows) + +SELECT (x_stl_my_integer() IS NOT DISTINCT FROM '(1)'::my_integer) FROM x; +NOTICE: s my_integer +NOTICE: equal my_integer stable + ?column? +---------- + t + t + t + t +(4 rows) + +-- NULLIF expressions testing +DROP OPERATOR = (my_integer, my_integer); +CREATE OPERATOR = ( + PROCEDURE = equal_my_integer_vlt, + LEFTARG = my_integer, + RIGHTARG = my_integer +); +SELECT NULLIF('(1)'::my_integer, '(2)'::my_integer) FROM x; -- should not be precalculated +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 x; +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 +); +SELECT NULLIF('(1)'::my_integer, '(2)'::my_integer) FROM x; +NOTICE: equal my_integer immutable + nullif +-------- + (1) + (1) + (1) + (1) +(4 rows) + +-- NULLIF expressions with null arguments testing +SELECT x_stl2(NULLIF(1, NULL)) FROM x; +NOTICE: s2 + x_stl2 +-------- + 1 + 1 + 1 + 1 +(4 rows) + +SELECT x_stl2(NULLIF(NULL::integer, NULL)) FROM x; +NOTICE: s2 + x_stl2 +-------- + + + + +(4 rows) + +-- Mixed functions 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 equal_my_integer_stl( + NULLIF('(1)'::my_integer, '(2)'::my_integer), + NULLIF('(2)'::my_integer, '(2)'::my_integer) +) +FROM 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 + equal_my_integer_stl +---------------------- + + + + +(4 rows) + +DROP OPERATOR = (my_integer, my_integer); +CREATE OPERATOR = ( + PROCEDURE = equal_my_integer_stl, + LEFTARG = my_integer, + RIGHTARG = my_integer +); +SELECT equal_my_integer_stl( + NULLIF('(1)'::my_integer, '(2)'::my_integer), + NULLIF('(2)'::my_integer, '(2)'::my_integer) +) +FROM x; +NOTICE: equal my_integer stable +NOTICE: equal my_integer stable +NOTICE: equal my_integer stable + equal_my_integer_stl +---------------------- + + + + +(4 rows) + +SELECT NULLIF(x_vlt_my_integer(), '(2)'::my_integer) FROM x; -- should not be precalculated +NOTICE: v my_integer +NOTICE: equal my_integer stable +NOTICE: v my_integer +NOTICE: equal my_integer stable +NOTICE: v my_integer +NOTICE: equal my_integer stable +NOTICE: v my_integer +NOTICE: equal my_integer stable + nullif +-------- + (1) + (1) + (1) + (1) +(4 rows) + +SELECT NULLIF(x_stl_my_integer(), '(2)'::my_integer) FROM x; +NOTICE: s my_integer +NOTICE: equal my_integer stable + 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 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 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 +); +SELECT '(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer) FROM x; -- should not be precalculated +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 x; +NOTICE: equal integers stable +NOTICE: equal integers stable + ?column? +---------- + f + f + f + f +(4 rows) + +SELECT 1 ==== ALL ('{2, 3}') FROM 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 x; +NOTICE: equal my_integer stable +NOTICE: equal my_integer stable + ?column? +---------- + f + f + f + f +(4 rows) + +SELECT 1 ===== ANY ('{2, 3}') FROM x; +NOTICE: equal integers immutable +NOTICE: equal integers immutable + ?column? +---------- + f + f + f + f +(4 rows) + +SELECT 1 ===== ALL ('{2, 3}') FROM x; +NOTICE: equal integers immutable + ?column? +---------- + f + f + f + f +(4 rows) + +DROP OPERATOR = (my_integer, my_integer); +CREATE OPERATOR = ( + PROCEDURE = equal_my_integer_imm, + LEFTARG = my_integer, + RIGHTARG = my_integer +); +SELECT '(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer) FROM x; +NOTICE: equal my_integer immutable +NOTICE: equal my_integer immutable + ?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 x; +NOTICE: equal integers stable +NOTICE: equal integers stable + ?column? +---------- + + + + +(4 rows) + +SELECT x_stl2_boolean(1 ==== ANY (NULL)) FROM x; +NOTICE: s2 boolean + x_stl2_boolean +---------------- + + + + +(4 rows) + +SELECT NULL ==== ANY ('{2, 3}'::integer[]) FROM x; +NOTICE: equal integers stable +NOTICE: equal integers stable + ?column? +---------- + + + + +(4 rows) + +SELECT NULL ==== ANY ('{2, NULL}'::integer[]) FROM x; +NOTICE: equal integers stable +NOTICE: equal integers stable + ?column? +---------- + + + + +(4 rows) + +SELECT x_stl2_boolean(NULL::integer ==== ANY (NULL)) FROM x; +NOTICE: s2 boolean + x_stl2_boolean +---------------- + + + + +(4 rows) + +SELECT 1 ==== ALL ('{2, NULL}') FROM x; +NOTICE: equal integers stable + ?column? +---------- + f + f + f + f +(4 rows) + +SELECT x_stl2_boolean(1 ==== ALL (NULL)) FROM x; +NOTICE: s2 boolean + x_stl2_boolean +---------------- + + + + +(4 rows) + +SELECT NULL ==== ALL ('{2, 3}'::integer[]) FROM x; +NOTICE: equal integers stable +NOTICE: equal integers stable + ?column? +---------- + + + + +(4 rows) + +SELECT NULL ==== ALL ('{2, NULL}'::integer[]) FROM x; +NOTICE: equal integers stable +NOTICE: equal integers stable + ?column? +---------- + + + + +(4 rows) + +SELECT x_stl2_boolean(NULL::integer ==== ALL (NULL)) FROM x; +NOTICE: s2 boolean + x_stl2_boolean +---------------- + + + + +(4 rows) + +SELECT x_stl2_boolean(1 IN (2, NULL)) FROM x; +NOTICE: s2 boolean + x_stl2_boolean +---------------- + + + + +(4 rows) + +SELECT x_stl2_boolean(NULL IN (2, 3)) FROM x; +NOTICE: s2 boolean + x_stl2_boolean +---------------- + + + + +(4 rows) + +SELECT x_stl2_boolean(NULL IN (2, NULL)) FROM x; +NOTICE: s2 boolean + x_stl2_boolean +---------------- + + + + +(4 rows) + +-- Mixed functions and "scalar op ANY/ALL (array)" / "scalar IN (2 or more +-- values)" expressions testing +SELECT x_stl2_boolean(1 === ANY ('{2, 3}')) FROM x; -- should not be precalculated +NOTICE: equal integers volatile +NOTICE: equal integers volatile +NOTICE: s2 boolean +NOTICE: equal integers volatile +NOTICE: equal integers volatile +NOTICE: s2 boolean +NOTICE: equal integers volatile +NOTICE: equal integers volatile +NOTICE: s2 boolean +NOTICE: equal integers volatile +NOTICE: equal integers volatile +NOTICE: s2 boolean + x_stl2_boolean +---------------- + f + f + f + f +(4 rows) + +SELECT x_stl2_boolean(1 === ALL ('{2, 3}')) FROM x; -- should not be precalculated +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_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) +) +FROM 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) + +SELECT x_stl2_boolean(1 ==== ANY ('{2, 3}')) FROM x; +NOTICE: equal integers stable +NOTICE: equal integers stable +NOTICE: s2 boolean + x_stl2_boolean +---------------- + f + f + f + f +(4 rows) + +SELECT x_stl2_boolean(1 ==== ALL ('{2, 3}')) FROM x; +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) +) +FROM x; +NOTICE: equal my_integer stable +NOTICE: equal my_integer stable +NOTICE: s2 boolean + x_stl2_boolean +---------------- + f + f + f + f +(4 rows) + +SELECT x_vlt() ==== ANY ('{2, 3}') FROM x; -- should not be precalculated +NOTICE: v +NOTICE: equal integers stable +NOTICE: equal integers stable +NOTICE: v +NOTICE: equal integers stable +NOTICE: equal integers stable +NOTICE: v +NOTICE: equal integers stable +NOTICE: equal integers stable +NOTICE: v +NOTICE: equal integers stable +NOTICE: equal integers stable + ?column? +---------- + f + f + f + f +(4 rows) + +SELECT x_vlt() ==== ALL ('{2, 3}') FROM 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 1 ==== ANY (x_vlt_array_integer()) FROM x; -- should not be precalculated +NOTICE: v array_integer +NOTICE: equal integers stable +NOTICE: equal integers stable +NOTICE: v array_integer +NOTICE: equal integers stable +NOTICE: equal integers stable +NOTICE: v array_integer +NOTICE: equal integers stable +NOTICE: equal integers stable +NOTICE: v array_integer +NOTICE: equal integers stable +NOTICE: equal integers stable + ?column? +---------- + f + f + f + f +(4 rows) + +SELECT 1 ==== ALL (x_vlt_array_integer()) FROM x; -- should not be precalculated +NOTICE: v array_integer +NOTICE: equal integers stable +NOTICE: v array_integer +NOTICE: equal integers stable +NOTICE: v array_integer +NOTICE: equal integers stable +NOTICE: v array_integer +NOTICE: equal integers stable + ?column? +---------- + f + f + f + f +(4 rows) + +SELECT x_vlt_my_integer() IN ('(2)'::my_integer, '(3)'::my_integer) FROM x; -- should not be precalculated +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 + ?column? +---------- + f + f + f + f +(4 rows) + +SELECT x_stl() ==== ANY ('{2, 3}') FROM x; +NOTICE: s +NOTICE: equal integers stable +NOTICE: equal integers stable + ?column? +---------- + f + f + f + f +(4 rows) + +SELECT x_stl() ==== ALL ('{2, 3}') FROM x; +NOTICE: s +NOTICE: equal integers stable + ?column? +---------- + f + f + f + f +(4 rows) + +SELECT 1 ==== ANY (x_stl_array_integer()) FROM x; +NOTICE: s array_integer +NOTICE: equal integers stable +NOTICE: equal integers stable + ?column? +---------- + f + f + f + f +(4 rows) + +SELECT 1 ==== ALL (x_stl_array_integer()) FROM x; +NOTICE: s array_integer +NOTICE: equal integers stable + ?column? +---------- + f + f + f + f +(4 rows) + +SELECT x_stl_my_integer() IN ('(2)'::my_integer, '(3)'::my_integer) FROM x; +NOTICE: s my_integer +NOTICE: equal my_integer stable +NOTICE: equal my_integer stable + ?column? +---------- + f + f + f + f +(4 rows) + +-- Boolean expressions testing +SELECT x_stl2_boolean(x_vlt_boolean() AND x_stl2_boolean(TRUE)) FROM x; -- should not be precalculated +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean + x_stl2_boolean +---------------- + t + t + t + t +(4 rows) + +SELECT x_stl2_boolean(x_vlt_boolean() OR x_stl2_boolean(TRUE)) FROM x; -- should not be precalculated +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean + x_stl2_boolean +---------------- + t + t + t + t +(4 rows) + +SELECT x_stl2_boolean(NOT x_vlt_boolean()) FROM x; -- should not be precalculated +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean + x_stl2_boolean +---------------- + f + f + f + f +(4 rows) + +SELECT x_stl2_boolean(x_stl2_boolean(TRUE) AND x_stl2_boolean(TRUE)) FROM x; +NOTICE: s2 boolean +NOTICE: s2 boolean +NOTICE: s2 boolean + x_stl2_boolean +---------------- + t + t + t + t +(4 rows) + +SELECT x_stl2_boolean(x_stl2_boolean(TRUE) OR x_stl2_boolean(TRUE)) FROM x; +NOTICE: s2 boolean +NOTICE: s2 boolean + x_stl2_boolean +---------------- + t + t + t + t +(4 rows) + +SELECT x_stl2_boolean(NOT x_stl2_boolean(TRUE)) FROM x; +NOTICE: s2 boolean +NOTICE: s2 boolean + x_stl2_boolean +---------------- + f + f + f + f +(4 rows) + +-- ARRAY[] expressions testing +SELECT x_stl2_array_integer(ARRAY[x_vlt(), 2]) FROM x; -- should not be precalculated +NOTICE: v +NOTICE: s2 array_integer +NOTICE: v +NOTICE: s2 array_integer +NOTICE: v +NOTICE: s2 array_integer +NOTICE: v +NOTICE: s2 array_integer + x_stl2_array_integer +---------------------- + {1,2} + {1,2} + {1,2} + {1,2} +(4 rows) + +SELECT x_stl2_array_integer(ARRAY[x_stl(), 2]) FROM x; +NOTICE: s +NOTICE: s2 array_integer + x_stl2_array_integer +---------------------- + {1,2} + {1,2} + {1,2} + {1,2} +(4 rows) + +-- Multidimensional ARRAY[] expressions testing +SELECT x_stl2_array_integer(ARRAY[[x_vlt(), 2], [3, 4]]) FROM x; -- should not be precalculated +NOTICE: v +NOTICE: s2 array_integer +NOTICE: v +NOTICE: s2 array_integer +NOTICE: v +NOTICE: s2 array_integer +NOTICE: v +NOTICE: s2 array_integer + x_stl2_array_integer +---------------------- + {{1,2},{3,4}} + {{1,2},{3,4}} + {{1,2},{3,4}} + {{1,2},{3,4}} +(4 rows) + +SELECT x_stl2_array_integer(ARRAY[[x_stl(), 2], [3, 4]]) FROM x; +NOTICE: s +NOTICE: s2 array_integer + x_stl2_array_integer +---------------------- + {{1,2},{3,4}} + {{1,2},{3,4}} + {{1,2},{3,4}} + {{1,2},{3,4}} +(4 rows) + +-- Array subscripting operations testing +SELECT x_stl2(('{1, 2}'::integer[])[1]) FROM x; +NOTICE: s2 + x_stl2 +-------- + 1 + 1 + 1 + 1 +(4 rows) + +SELECT x_stl2_array_integer(('{1, 2}'::integer[])[:]) FROM x; +NOTICE: s2 array_integer + x_stl2_array_integer +---------------------- + {1,2} + {1,2} + {1,2} + {1,2} +(4 rows) + +-- Mixed functions and array subscripting operations testing +SELECT x_stl2((x_vlt_array_integer())[x_vlt()]) FROM x; -- should not be precalculated +NOTICE: v array_integer +NOTICE: v +NOTICE: s2 +NOTICE: v array_integer +NOTICE: v +NOTICE: s2 +NOTICE: v array_integer +NOTICE: v +NOTICE: s2 +NOTICE: v array_integer +NOTICE: v +NOTICE: s2 + x_stl2 +-------- + 2 + 2 + 2 + 2 +(4 rows) + +SELECT x_stl2((x_vlt_array_integer())[1]) FROM x; -- should not be precalculated +NOTICE: v array_integer +NOTICE: s2 +NOTICE: v array_integer +NOTICE: s2 +NOTICE: v array_integer +NOTICE: s2 +NOTICE: v array_integer +NOTICE: s2 + x_stl2 +-------- + 2 + 2 + 2 + 2 +(4 rows) + +SELECT x_stl2_array_integer((x_vlt_array_integer())[:]) FROM x; -- should not be precalculated +NOTICE: v array_integer +NOTICE: s2 array_integer +NOTICE: v array_integer +NOTICE: s2 array_integer +NOTICE: v array_integer +NOTICE: s2 array_integer +NOTICE: v array_integer +NOTICE: s2 array_integer + x_stl2_array_integer +---------------------- + {2,3} + {2,3} + {2,3} + {2,3} +(4 rows) + +SELECT x_stl2(('{1, 2}'::integer[])[x_vlt()]) FROM 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_stl2((x_stl_array_integer())[x_stl()]) FROM x; +NOTICE: s array_integer +NOTICE: s +NOTICE: s2 + x_stl2 +-------- + 2 + 2 + 2 + 2 +(4 rows) + +SELECT x_stl2((x_stl_array_integer())[1]) FROM x; +NOTICE: s array_integer +NOTICE: s2 + x_stl2 +-------- + 2 + 2 + 2 + 2 +(4 rows) + +SELECT x_stl2_array_integer((x_stl_array_integer())[:]) FROM x; +NOTICE: s array_integer +NOTICE: s2 array_integer + x_stl2_array_integer +---------------------- + {2,3} + {2,3} + {2,3} + {2,3} +(4 rows) + +SELECT x_stl2(('{1, 2}'::integer[])[x_stl()]) FROM x; +NOTICE: s +NOTICE: s2 + x_stl2 +-------- + 1 + 1 + 1 + 1 +(4 rows) + +-- FieldSelect expressions testing +SELECT x_stl2(('(1, {2}, TRUE, 3)'::wxyz).w) FROM x; +NOTICE: s2 + x_stl2 +-------- + 1 + 1 + 1 + 1 +(4 rows) + +SELECT x_stl2(('(1)'::my_integer).value) FROM x; +NOTICE: s2 + x_stl2 +-------- + 1 + 1 + 1 + 1 +(4 rows) + +-- Mixed functions and FieldSelect expressions testing +SELECT x_stl2((x_vlt_wxyz()).w) FROM x; -- should not be precalculated +NOTICE: v wxyz +NOTICE: s2 +NOTICE: v wxyz +NOTICE: s2 +NOTICE: v wxyz +NOTICE: s2 +NOTICE: v wxyz +NOTICE: s2 + x_stl2 +-------- + 1 + 1 + 1 + 1 +(4 rows) + +SELECT x_stl2((x_vlt_my_integer()).value) FROM x; -- should not be precalculated +NOTICE: v my_integer +NOTICE: s2 +NOTICE: v my_integer +NOTICE: s2 +NOTICE: v my_integer +NOTICE: s2 +NOTICE: v my_integer +NOTICE: s2 + x_stl2 +-------- + 1 + 1 + 1 + 1 +(4 rows) + +SELECT x_stl2((x_stl_wxyz()).w) FROM x; +NOTICE: s wxyz +NOTICE: s2 + x_stl2 +-------- + 1 + 1 + 1 + 1 +(4 rows) + +SELECT x_stl2((x_stl_my_integer()).value) FROM x; +NOTICE: s my_integer +NOTICE: s2 + x_stl2 +-------- + 1 + 1 + 1 + 1 +(4 rows) + +-- ROW() expressions testing +SELECT x_stl2_wxyz((1, '{2}', TRUE, 3)) FROM x; +NOTICE: s2 wxyz + x_stl2_wxyz +------------- + (1,{2},t,3) + (1,{2},t,3) + (1,{2},t,3) + (1,{2},t,3) +(4 rows) + +SELECT x_stl2_wxyz(ROW(1, '{2}', TRUE, 3)) FROM x; +NOTICE: s2 wxyz + x_stl2_wxyz +------------- + (1,{2},t,3) + (1,{2},t,3) + (1,{2},t,3) + (1,{2},t,3) +(4 rows) + +SELECT x_stl2_wxyz((1, '{2}', TRUE, 3)::wxyz) FROM x; +NOTICE: s2 wxyz + x_stl2_wxyz +------------- + (1,{2},t,3) + (1,{2},t,3) + (1,{2},t,3) + (1,{2},t,3) +(4 rows) + +SELECT x_stl2_composite_type((1, '{2}', TRUE)) FROM x; +NOTICE: s2 composite_type + x_stl2_composite_type +----------------------- + (1,{2},t) + (1,{2},t) + (1,{2},t) + (1,{2},t) +(4 rows) + +SELECT x_stl2_composite_type(ROW(1, '{2}', TRUE)) FROM x; +NOTICE: s2 composite_type + x_stl2_composite_type +----------------------- + (1,{2},t) + (1,{2},t) + (1,{2},t) + (1,{2},t) +(4 rows) + +SELECT x_stl2_composite_type((1, '{2}', TRUE)::composite_type) FROM x; +NOTICE: s2 composite_type + x_stl2_composite_type +----------------------- + (1,{2},t) + (1,{2},t) + (1,{2},t) + (1,{2},t) +(4 rows) + +-- Mixed functions and ROW() expressions testing +SELECT x_stl2_wxyz((x_vlt(), '{2}', TRUE, 3)) FROM x; -- should not be precalculated +NOTICE: v +NOTICE: s2 wxyz +NOTICE: v +NOTICE: s2 wxyz +NOTICE: v +NOTICE: s2 wxyz +NOTICE: v +NOTICE: s2 wxyz + x_stl2_wxyz +------------- + (1,{2},t,3) + (1,{2},t,3) + (1,{2},t,3) + (1,{2},t,3) +(4 rows) + +SELECT x_stl2_wxyz((x_stl(), '{2}', TRUE, 3)) FROM x; +NOTICE: s +NOTICE: s2 wxyz + x_stl2_wxyz +------------- + (1,{2},t,3) + (1,{2},t,3) + (1,{2},t,3) + (1,{2},t,3) +(4 rows) + +-- RelabelType expressions testing +SELECT x_stl2(x_vlt_oid()::integer) FROM x; -- should not be precalculated +NOTICE: v oid +NOTICE: s2 +NOTICE: v oid +NOTICE: s2 +NOTICE: v oid +NOTICE: s2 +NOTICE: v oid +NOTICE: s2 + x_stl2 +-------- + 1 + 1 + 1 + 1 +(4 rows) + +SELECT x_stl2(x_stl_oid()::integer) FROM x; +NOTICE: s oid +NOTICE: s2 + x_stl2 +-------- + 1 + 1 + 1 + 1 +(4 rows) + +-- CoerceViaIO expressions testing +SELECT x_stl2_my_integer('(1)'::text::my_integer) FROM x; +NOTICE: s2 my_integer + x_stl2_my_integer +------------------- + (1) + (1) + (1) + (1) +(4 rows) + +SELECT x_stl2(x_vlt_text_integer()::integer) FROM x; -- should not be precalculated +NOTICE: v text integer +NOTICE: s2 +NOTICE: v text integer +NOTICE: s2 +NOTICE: v text integer +NOTICE: s2 +NOTICE: v text integer +NOTICE: s2 + x_stl2 +-------- + 1 + 1 + 1 + 1 +(4 rows) + +SELECT x_stl2(x_stl_text_integer()::integer) FROM x; +NOTICE: s text integer +NOTICE: s2 + x_stl2 +-------- + 1 + 1 + 1 + 1 +(4 rows) + +-- Mixed functions and CoerceViaIO expressions testing +SELECT x_stl2_my_integer(x_vlt_text_my_integer()::my_integer) FROM x; -- should not be precalculated +NOTICE: v text my_integer +NOTICE: s2 my_integer +NOTICE: v text my_integer +NOTICE: s2 my_integer +NOTICE: v text my_integer +NOTICE: s2 my_integer +NOTICE: v text my_integer +NOTICE: s2 my_integer + x_stl2_my_integer +------------------- + (1) + (1) + (1) + (1) +(4 rows) + +SELECT x_stl2_my_integer(x_stl_text_my_integer()::my_integer) FROM x; +NOTICE: s text my_integer +NOTICE: s2 my_integer + x_stl2_my_integer +------------------- + (1) + (1) + (1) + (1) +(4 rows) + +-- ArrayCoerce expressions testing +-- Binary-coercible types: +SELECT x_stl2_array_oid(x_vlt_array_integer()::oid[]) FROM x; -- should not be precalculated +NOTICE: v array_integer +NOTICE: s2 array_oid +NOTICE: v array_integer +NOTICE: s2 array_oid +NOTICE: v array_integer +NOTICE: s2 array_oid +NOTICE: v array_integer +NOTICE: s2 array_oid + x_stl2_array_oid +------------------ + {2,3} + {2,3} + {2,3} + {2,3} +(4 rows) + +SELECT x_stl2_array_oid(x_stl_array_integer()::oid[]) FROM x; +NOTICE: s array_integer +NOTICE: s2 array_oid + x_stl2_array_oid +------------------ + {2,3} + {2,3} + {2,3} + {2,3} +(4 rows) + +-- Not binary-coercible types: +-- create cast here because we will drop and reuse it several times +CREATE CAST (integer AS my_integer) + WITH FUNCTION cast_integer_as_my_integer_vlt; +SELECT '{1, 2}'::integer[]::my_integer[] FROM x; -- should not be precalculated +NOTICE: cast integer as my_integer volatile +NOTICE: cast integer as my_integer volatile +NOTICE: cast integer as my_integer volatile +NOTICE: cast integer as my_integer volatile +NOTICE: cast integer as my_integer volatile +NOTICE: cast integer as my_integer volatile +NOTICE: cast integer as my_integer volatile +NOTICE: cast integer as my_integer volatile + my_integer +------------ + {(1),(2)} + {(1),(2)} + {(1),(2)} + {(1),(2)} +(4 rows) + +DROP CAST (integer AS my_integer); +CREATE CAST (integer AS my_integer) + WITH FUNCTION cast_integer_as_my_integer_stl; +SELECT '{1, 2}'::integer[]::my_integer[] FROM x; +NOTICE: cast integer as my_integer stable +NOTICE: cast integer as my_integer stable + my_integer +------------ + {(1),(2)} + {(1),(2)} + {(1),(2)} + {(1),(2)} +(4 rows) + +-- Mixed functions and ArrayCoerce expressions testing +-- Not binary-coercible types: +-- create cast here because we will drop and reuse it several times +CREATE CAST (my_integer AS integer) + WITH FUNCTION cast_my_integer_as_integer_vlt; +SELECT x_stl2_array_integer('{(1), (2)}'::my_integer[]::integer[]) FROM x; -- should not be precalculated +NOTICE: cast my_integer as integer volatile +NOTICE: cast my_integer as integer volatile +NOTICE: s2 array_integer +NOTICE: cast my_integer as integer volatile +NOTICE: cast my_integer as integer volatile +NOTICE: s2 array_integer +NOTICE: cast my_integer as integer volatile +NOTICE: cast my_integer as integer volatile +NOTICE: s2 array_integer +NOTICE: cast my_integer as integer volatile +NOTICE: cast my_integer as integer volatile +NOTICE: s2 array_integer + x_stl2_array_integer +---------------------- + {1,2} + {1,2} + {1,2} + {1,2} +(4 rows) + +DROP CAST (my_integer AS integer); +CREATE CAST (my_integer AS integer) + WITH FUNCTION cast_my_integer_as_integer_stl; +SELECT x_stl2_array_integer('{(1), (2)}'::my_integer[]::integer[]) FROM x; +NOTICE: cast my_integer as integer stable +NOTICE: cast my_integer as integer stable +NOTICE: s2 array_integer + x_stl2_array_integer +---------------------- + {1,2} + {1,2} + {1,2} + {1,2} +(4 rows) + +DROP CAST (integer AS my_integer); +CREATE CAST (integer AS my_integer) + WITH FUNCTION cast_integer_as_my_integer_stl; +SELECT x_vlt_array_integer()::my_integer[] FROM x; -- should not be precalculated +NOTICE: v array_integer +NOTICE: cast integer as my_integer stable +NOTICE: cast integer as my_integer stable +NOTICE: v array_integer +NOTICE: cast integer as my_integer stable +NOTICE: cast integer as my_integer stable +NOTICE: v array_integer +NOTICE: cast integer as my_integer stable +NOTICE: cast integer as my_integer stable +NOTICE: v array_integer +NOTICE: cast integer as my_integer stable +NOTICE: cast integer as my_integer stable + x_vlt_array_integer +--------------------- + {(2),(3)} + {(2),(3)} + {(2),(3)} + {(2),(3)} +(4 rows) + +SELECT x_stl_array_integer()::my_integer[] FROM x; +NOTICE: s array_integer +NOTICE: cast integer as my_integer stable +NOTICE: cast integer as my_integer stable + x_stl_array_integer +--------------------- + {(2),(3)} + {(2),(3)} + {(2),(3)} + {(2),(3)} +(4 rows) + +-- ConvertRowtypeExpr testing +SELECT x_stl2_wxyz('(1, {2}, TRUE, 3)'::wxyz_child::wxyz) FROM x; +NOTICE: s2 wxyz + x_stl2_wxyz +------------- + (1,{2},t,3) + (1,{2},t,3) + (1,{2},t,3) + (1,{2},t,3) +(4 rows) + +SELECT x_stl2_wxyz('(1, {2}, TRUE, 3, 4, 5)'::wxyz_child2::wxyz) FROM x; +NOTICE: s2 wxyz + x_stl2_wxyz +------------- + (1,{2},t,3) + (1,{2},t,3) + (1,{2},t,3) + (1,{2},t,3) +(4 rows) + +SELECT x_stl2_no_columns('()'::no_columns_child::no_columns) FROM x; +NOTICE: s2 no_columns + x_stl2_no_columns +------------------- + () + () + () + () +(4 rows) + +SELECT x_stl2_no_columns('(1, 2)'::no_columns_child2::no_columns) FROM x; +NOTICE: s2 no_columns + x_stl2_no_columns +------------------- + () + () + () + () +(4 rows) + +-- Mixed functions and ConvertRowtypeExpr testing +SELECT x_stl2_wxyz(x_vlt_wxyz_child()::wxyz_child::wxyz) FROM x; -- should not be precalculated +NOTICE: v wxyz_child +NOTICE: s2 wxyz +NOTICE: v wxyz_child +NOTICE: s2 wxyz +NOTICE: v wxyz_child +NOTICE: s2 wxyz +NOTICE: v wxyz_child +NOTICE: s2 wxyz + x_stl2_wxyz +------------- + (1,{2},t,3) + (1,{2},t,3) + (1,{2},t,3) + (1,{2},t,3) +(4 rows) + +SELECT x_stl2_wxyz(x_vlt_wxyz_child2()::wxyz_child2::wxyz) FROM x; -- should not be precalculated +NOTICE: v wxyz_child2 +NOTICE: s2 wxyz +NOTICE: v wxyz_child2 +NOTICE: s2 wxyz +NOTICE: v wxyz_child2 +NOTICE: s2 wxyz +NOTICE: v wxyz_child2 +NOTICE: s2 wxyz + x_stl2_wxyz +------------- + (1,{2},t,3) + (1,{2},t,3) + (1,{2},t,3) + (1,{2},t,3) +(4 rows) + +SELECT x_stl2_wxyz(x_stl_wxyz_child()::wxyz_child::wxyz) FROM x; +NOTICE: s wxyz_child +NOTICE: s2 wxyz + x_stl2_wxyz +------------- + (1,{2},t,3) + (1,{2},t,3) + (1,{2},t,3) + (1,{2},t,3) +(4 rows) + +SELECT x_stl2_wxyz(x_stl_wxyz_child2()::wxyz_child2::wxyz) FROM x; +NOTICE: s wxyz_child2 +NOTICE: s2 wxyz + x_stl2_wxyz +------------- + (1,{2},t,3) + (1,{2},t,3) + (1,{2},t,3) + (1,{2},t,3) +(4 rows) + +-- CASE expressions testing +SELECT x_stl2(CASE WHEN x_vlt_boolean() THEN x_vlt() ELSE x_vlt() END) FROM x; -- should not be precalculated +NOTICE: v boolean +NOTICE: v +NOTICE: s2 +NOTICE: v boolean +NOTICE: v +NOTICE: s2 +NOTICE: v boolean +NOTICE: v +NOTICE: s2 +NOTICE: v boolean +NOTICE: v +NOTICE: s2 + x_stl2 +-------- + 1 + 1 + 1 + 1 +(4 rows) + +SELECT x_stl2(CASE x_vlt() WHEN x_vlt() THEN x_vlt() ELSE x_vlt() END) FROM x; -- should not be precalculated +NOTICE: v +NOTICE: v +NOTICE: v +NOTICE: s2 +NOTICE: v +NOTICE: v +NOTICE: v +NOTICE: s2 +NOTICE: v +NOTICE: v +NOTICE: v +NOTICE: s2 +NOTICE: v +NOTICE: v +NOTICE: v +NOTICE: s2 + x_stl2 +-------- + 1 + 1 + 1 + 1 +(4 rows) + +SELECT x_stl2(CASE WHEN x_stl2_boolean(TRUE) THEN x_stl() ELSE x_stl() END) +FROM x; +NOTICE: s2 boolean +NOTICE: s +NOTICE: s2 + x_stl2 +-------- + 1 + 1 + 1 + 1 +(4 rows) + +SELECT x_stl2(CASE x_stl() WHEN x_stl() THEN x_stl() ELSE x_stl() END) FROM x; +NOTICE: s +NOTICE: s +NOTICE: s +NOTICE: s2 + x_stl2 +-------- + 1 + 1 + 1 + 1 +(4 rows) + +-- RowCompareExpr testing +SELECT x_stl2_boolean((1, 2) < (1, 3)) FROM x; +NOTICE: s2 boolean + x_stl2_boolean +---------------- + t + t + t + t +(4 rows) + +-- Mixed functions and RowCompareExpr testing +SELECT x_stl2_boolean((x_vlt(), 2) < (1, 3)) FROM x; -- should not be precalculated +NOTICE: v +NOTICE: s2 boolean +NOTICE: v +NOTICE: s2 boolean +NOTICE: v +NOTICE: s2 boolean +NOTICE: v +NOTICE: s2 boolean + x_stl2_boolean +---------------- + t + t + t + t +(4 rows) + +SELECT x_stl2_boolean((x_stl(), 2) < (1, 3)) FROM x; +NOTICE: s +NOTICE: s2 boolean + x_stl2_boolean +---------------- + t + t + t + t +(4 rows) + +-- COALESCE expressions testing +SELECT x_stl2(COALESCE(NULL, x_vlt2(NULL), 2)) FROM x; -- should not be precalculated +NOTICE: v2 +NOTICE: s2 +NOTICE: v2 +NOTICE: s2 +NOTICE: v2 +NOTICE: s2 +NOTICE: v2 +NOTICE: s2 + x_stl2 +-------- + 2 + 2 + 2 + 2 +(4 rows) + +SELECT x_stl2(COALESCE(NULL, x_stl2(NULL), 2)) FROM x; +NOTICE: s2 +NOTICE: s2 + x_stl2 +-------- + 2 + 2 + 2 + 2 +(4 rows) + +-- GREATEST and LEAST functions testing +SELECT x_stl2(GREATEST(2, 1, 3)) FROM x; +NOTICE: s2 + x_stl2 +-------- + 3 + 3 + 3 + 3 +(4 rows) + +SELECT x_stl2(LEAST(2, 1, 3)) FROM x; +NOTICE: s2 + x_stl2 +-------- + 1 + 1 + 1 + 1 +(4 rows) + +-- Mixed functions and GREATEST and LEAST functions testing +SELECT x_stl2(GREATEST(2, x_vlt(), 3)) FROM x; -- should not be precalculated +NOTICE: v +NOTICE: s2 +NOTICE: v +NOTICE: s2 +NOTICE: v +NOTICE: s2 +NOTICE: v +NOTICE: s2 + x_stl2 +-------- + 3 + 3 + 3 + 3 +(4 rows) + +SELECT x_stl2(LEAST(2, x_vlt(), 3)) FROM 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_stl2(GREATEST(2, x_stl(), 3)) FROM x; +NOTICE: s +NOTICE: s2 + x_stl2 +-------- + 3 + 3 + 3 + 3 +(4 rows) + +SELECT x_stl2(LEAST(2, x_stl(), 3)) FROM x; +NOTICE: s +NOTICE: s2 + x_stl2 +-------- + 1 + 1 + 1 + 1 +(4 rows) + +-- SQLValueFunction testing +CREATE ROLE regress_testrol2 SUPERUSER; +CREATE ROLE regress_testrol1 SUPERUSER LOGIN IN ROLE regress_testrol2; +\c - +SET SESSION AUTHORIZATION regress_testrol1; +SET ROLE regress_testrol2; +SELECT x_stl2_name(current_role) FROM x; +NOTICE: s2 name + x_stl2_name +------------------ + regress_testrol2 + regress_testrol2 + regress_testrol2 + regress_testrol2 +(4 rows) + +SELECT x_stl2_name(current_user) FROM x; +NOTICE: s2 name + x_stl2_name +------------------ + regress_testrol2 + regress_testrol2 + regress_testrol2 + regress_testrol2 +(4 rows) + +SELECT x_stl2_name(user) FROM x; +NOTICE: s2 name + x_stl2_name +------------------ + regress_testrol2 + regress_testrol2 + regress_testrol2 + regress_testrol2 +(4 rows) + +SELECT x_stl2_name(session_user) FROM x; +NOTICE: s2 name + x_stl2_name +------------------ + regress_testrol1 + regress_testrol1 + regress_testrol1 + regress_testrol1 +(4 rows) + +SELECT x_stl2_name(current_catalog) FROM x; +NOTICE: s2 name + x_stl2_name +------------- + regression + regression + regression + regression +(4 rows) + +SELECT x_stl2_name(current_schema) FROM x; +NOTICE: s2 name + x_stl2_name +------------- + public + public + public + public +(4 rows) + +\c +DROP ROLE regress_testrol1, regress_testrol2; +-- Xml expressions testing +SELECT x_stl2_xml(XMLCONCAT('', 'foo')) FROM x; +NOTICE: s2 xml + x_stl2_xml +---------------------- + foo + foo + foo + foo +(4 rows) + +SELECT x_stl2_xml( + XMLELEMENT(name foo, xmlattributes('bar' as bar), 'cont', 'ent') +) +FROM x; +NOTICE: s2 xml + x_stl2_xml +------------------------------ + content + content + content + content +(4 rows) + +SELECT x_stl2_xml(XMLFOREST('abc' AS foo, 123 AS bar)) FROM x; +NOTICE: s2 xml + x_stl2_xml +------------------------------ + abc123 + abc123 + abc123 + abc123 +(4 rows) + +SELECT x_stl2_xml(XMLPARSE( + DOCUMENT 'Manual' +)) +FROM x; +NOTICE: s2 xml + x_stl2_xml +------------------------------------ + Manual + Manual + Manual + Manual +(4 rows) + +SELECT x_stl2_xml(XMLPARSE(CONTENT 'abcbarfoo')) FROM x; +NOTICE: s2 xml + x_stl2_xml +--------------------------------- + abcbarfoo + abcbarfoo + abcbarfoo + abcbarfoo +(4 rows) + +SELECT x_stl2_xml(XMLPI(name php, 'echo "hello world";')) FROM x; +NOTICE: s2 xml + x_stl2_xml +----------------------------- + + + + +(4 rows) + +SELECT x_stl2_xml(XMLROOT( + 'abc', + version '1.0', + standalone yes +)) +FROM x; +NOTICE: s2 xml + x_stl2_xml +-------------------------------------------------------------- + abc + abc + abc + abc +(4 rows) + +SELECT x_stl2_text(XMLSERIALIZE( + DOCUMENT 'Manual' AS text +)) +FROM x; +NOTICE: s2 text + x_stl2_text +--------------------------------------------------------- + Manual + Manual + Manual + Manual +(4 rows) + +SELECT x_stl2_text(XMLSERIALIZE( + CONTENT 'abcbarfoo' AS text +)) +FROM x; +NOTICE: s2 text + x_stl2_text +--------------------------------- + abcbarfoo + abcbarfoo + abcbarfoo + abcbarfoo +(4 rows) + +SELECT x_stl2_boolean('abcbarfoo' IS DOCUMENT) FROM x; +NOTICE: s2 boolean + x_stl2_boolean +---------------- + f + f + f + f +(4 rows) + +-- Mixed functions and Xml expressions testing +-- should not be precalculated +SELECT x_stl2_xml(XMLCONCAT('', x_vlt_xml())) FROM x; +NOTICE: v xml +NOTICE: s2 xml +NOTICE: v xml +NOTICE: s2 xml +NOTICE: v xml +NOTICE: s2 xml +NOTICE: v xml +NOTICE: s2 xml + x_stl2_xml +---------------------- + foo + foo + foo + foo +(4 rows) + +-- should not be precalculated +SELECT x_stl2_xml( + XMLELEMENT(name foo, xmlattributes('bar' as bar), x_vlt_xml()) +) +FROM x; +NOTICE: v xml +NOTICE: s2 xml +NOTICE: v xml +NOTICE: s2 xml +NOTICE: v xml +NOTICE: s2 xml +NOTICE: v xml +NOTICE: s2 xml + x_stl2_xml +------------------------------------- + foo + foo + foo + foo +(4 rows) + +-- should not be precalculated +SELECT x_stl2_xml(XMLFOREST('abc' AS foo, x_vlt_xml() AS bar)) FROM x; +NOTICE: v xml +NOTICE: s2 xml +NOTICE: v xml +NOTICE: s2 xml +NOTICE: v xml +NOTICE: s2 xml +NOTICE: v xml +NOTICE: s2 xml + x_stl2_xml +----------------------------------------- + abcfoo + abcfoo + abcfoo + abcfoo +(4 rows) + +-- should not be precalculated +SELECT x_stl2_xml(XMLPARSE(DOCUMENT x_vlt_text_xml())) FROM x; +NOTICE: v text xml +NOTICE: s2 xml +NOTICE: v text xml +NOTICE: s2 xml +NOTICE: v text xml +NOTICE: s2 xml +NOTICE: v text xml +NOTICE: s2 xml + x_stl2_xml +------------------------------------ + Manual + Manual + Manual + Manual +(4 rows) + +-- should not be precalculated +SELECT x_stl2_xml(XMLPARSE(CONTENT x_vlt_text_xml_content())) FROM x; +NOTICE: v text xml content +NOTICE: s2 xml +NOTICE: v text xml content +NOTICE: s2 xml +NOTICE: v text xml content +NOTICE: s2 xml +NOTICE: v text xml content +NOTICE: s2 xml + x_stl2_xml +--------------------------------- + abcbarfoo + abcbarfoo + abcbarfoo + abcbarfoo +(4 rows) + +-- should not be precalculated +SELECT x_stl2_xml(XMLPI(name php, x_vlt_text_xml_instruction_content())) FROM x; +NOTICE: v text xml instruction content +NOTICE: s2 xml +NOTICE: v text xml instruction content +NOTICE: s2 xml +NOTICE: v text xml instruction content +NOTICE: s2 xml +NOTICE: v text xml instruction content +NOTICE: s2 xml + x_stl2_xml +----------------------------- + + + + +(4 rows) + +-- should not be precalculated +SELECT x_stl2_xml(XMLROOT(x_vlt_xml(), version '1.0', standalone yes)) FROM x; +NOTICE: v xml +NOTICE: s2 xml +NOTICE: v xml +NOTICE: s2 xml +NOTICE: v xml +NOTICE: s2 xml +NOTICE: v xml +NOTICE: s2 xml + x_stl2_xml +------------------------------------------------------ + foo + foo + foo + foo +(4 rows) + +-- should not be precalculated +SELECT x_stl2_text(XMLSERIALIZE(DOCUMENT x_vlt_xml() AS text)) FROM x; +NOTICE: v xml +NOTICE: s2 text +NOTICE: v xml +NOTICE: s2 text +NOTICE: v xml +NOTICE: s2 text +NOTICE: v xml +NOTICE: s2 text + x_stl2_text +---------------- + foo + foo + foo + foo +(4 rows) + +-- should not be precalculated +SELECT x_stl2_text(XMLSERIALIZE(CONTENT x_vlt_xml_content() AS text)) FROM x; +NOTICE: v xml content +NOTICE: s2 text +NOTICE: v xml content +NOTICE: s2 text +NOTICE: v xml content +NOTICE: s2 text +NOTICE: v xml content +NOTICE: s2 text + x_stl2_text +--------------------------------- + abcbarfoo + abcbarfoo + abcbarfoo + abcbarfoo +(4 rows) + +-- should not be precalculated +SELECT x_stl2_boolean(x_vlt_xml_content() IS DOCUMENT) FROM x; +NOTICE: v xml content +NOTICE: s2 boolean +NOTICE: v xml content +NOTICE: s2 boolean +NOTICE: v xml content +NOTICE: s2 boolean +NOTICE: v xml content +NOTICE: s2 boolean + x_stl2_boolean +---------------- + f + f + f + f +(4 rows) + +SELECT x_stl2_xml(XMLCONCAT('', x_stl_xml())) FROM x; +NOTICE: s xml +NOTICE: s2 xml + x_stl2_xml +---------------------- + foo + foo + foo + foo +(4 rows) + +SELECT x_stl2_xml( + XMLELEMENT(name foo, xmlattributes('bar' as bar), x_stl_xml()) +) +FROM x; +NOTICE: s xml +NOTICE: s2 xml + x_stl2_xml +------------------------------------- + foo + foo + foo + foo +(4 rows) + +SELECT x_stl2_xml(XMLFOREST('abc' AS foo, x_stl_xml() AS bar)) FROM x; +NOTICE: s xml +NOTICE: s2 xml + x_stl2_xml +----------------------------------------- + abcfoo + abcfoo + abcfoo + abcfoo +(4 rows) + +SELECT x_stl2_xml(XMLPARSE(DOCUMENT x_stl_text_xml())) FROM x; +NOTICE: s text xml +NOTICE: s2 xml + x_stl2_xml +------------------------------------ + Manual + Manual + Manual + Manual +(4 rows) + +SELECT x_stl2_xml(XMLPARSE(CONTENT x_stl_text_xml_content())) FROM x; +NOTICE: s xml content +NOTICE: s2 xml + x_stl2_xml +--------------------------------- + abcbarfoo + abcbarfoo + abcbarfoo + abcbarfoo +(4 rows) + +SELECT x_stl2_xml(XMLPI(name php, x_stl_text_xml_instruction_content())) FROM x; +NOTICE: s text xml instruction content +NOTICE: s2 xml + x_stl2_xml +----------------------------- + + + + +(4 rows) + +SELECT x_stl2_xml(XMLROOT(x_stl_xml(), version '1.0', standalone yes)) FROM x; +NOTICE: s xml +NOTICE: s2 xml + x_stl2_xml +------------------------------------------------------ + foo + foo + foo + foo +(4 rows) + +SELECT x_stl2_text(XMLSERIALIZE(DOCUMENT x_stl_xml() AS text)) FROM x; +NOTICE: s xml +NOTICE: s2 text + x_stl2_text +---------------- + foo + foo + foo + foo +(4 rows) + +SELECT x_stl2_text(XMLSERIALIZE(CONTENT x_stl_xml_content() AS text)) FROM x; +NOTICE: s xml content +NOTICE: s2 text + x_stl2_text +--------------------------------- + abcbarfoo + abcbarfoo + abcbarfoo + abcbarfoo +(4 rows) + +SELECT x_stl2_boolean(x_stl_xml_content() IS DOCUMENT) FROM x; +NOTICE: s xml content +NOTICE: s2 boolean + x_stl2_boolean +---------------- + f + f + f + f +(4 rows) + +-- NullTest expressions testing +SELECT x_stl2_boolean(x_vlt() IS NULL) FROM x; -- should not be precalculated +NOTICE: v +NOTICE: s2 boolean +NOTICE: v +NOTICE: s2 boolean +NOTICE: v +NOTICE: s2 boolean +NOTICE: v +NOTICE: s2 boolean + x_stl2_boolean +---------------- + f + f + f + f +(4 rows) + +SELECT x_stl2_boolean(x_vlt() IS NOT NULL) FROM x; -- should not be precalculated +NOTICE: v +NOTICE: s2 boolean +NOTICE: v +NOTICE: s2 boolean +NOTICE: v +NOTICE: s2 boolean +NOTICE: v +NOTICE: s2 boolean + x_stl2_boolean +---------------- + t + t + t + t +(4 rows) + +SELECT x_stl2_boolean(x_vlt_wxyz() IS NULL) FROM x; -- should not be precalculated +NOTICE: v wxyz +NOTICE: s2 boolean +NOTICE: v wxyz +NOTICE: s2 boolean +NOTICE: v wxyz +NOTICE: s2 boolean +NOTICE: v wxyz +NOTICE: s2 boolean + x_stl2_boolean +---------------- + f + f + f + f +(4 rows) + +SELECT x_stl2_boolean(x_vlt_wxyz() IS NOT NULL) FROM x; -- should not be precalculated +NOTICE: v wxyz +NOTICE: s2 boolean +NOTICE: v wxyz +NOTICE: s2 boolean +NOTICE: v wxyz +NOTICE: s2 boolean +NOTICE: v wxyz +NOTICE: s2 boolean + x_stl2_boolean +---------------- + t + t + t + t +(4 rows) + +SELECT x_stl2_boolean(x_stl() IS NULL) FROM x; +NOTICE: s +NOTICE: s2 boolean + x_stl2_boolean +---------------- + f + f + f + f +(4 rows) + +SELECT x_stl2_boolean(x_stl() IS NOT NULL) FROM x; +NOTICE: s +NOTICE: s2 boolean + x_stl2_boolean +---------------- + t + t + t + t +(4 rows) + +SELECT x_stl2_boolean(x_stl_wxyz() IS NULL) FROM x; +NOTICE: s wxyz +NOTICE: s2 boolean + x_stl2_boolean +---------------- + f + f + f + f +(4 rows) + +SELECT x_stl2_boolean(x_stl_wxyz() IS NOT NULL) FROM x; +NOTICE: s wxyz +NOTICE: s2 boolean + x_stl2_boolean +---------------- + t + t + t + t +(4 rows) + +-- BooleanTest expressions testing +SELECT x_stl2_boolean(x_vlt_boolean() IS TRUE) FROM x; -- should not be precalculated +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean + x_stl2_boolean +---------------- + t + t + t + t +(4 rows) + +SELECT x_stl2_boolean(x_vlt_boolean() IS NOT TRUE) FROM x; -- should not be precalculated +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean + x_stl2_boolean +---------------- + f + f + f + f +(4 rows) + +SELECT x_stl2_boolean(x_vlt_boolean() IS FALSE) FROM x; -- should not be precalculated +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean + x_stl2_boolean +---------------- + f + f + f + f +(4 rows) + +SELECT x_stl2_boolean(x_vlt_boolean() IS NOT FALSE) FROM x; -- should not be precalculated +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean + x_stl2_boolean +---------------- + t + t + t + t +(4 rows) + +SELECT x_stl2_boolean(x_vlt_boolean() IS UNKNOWN) FROM x; -- should not be precalculated +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean + x_stl2_boolean +---------------- + f + f + f + f +(4 rows) + +SELECT x_stl2_boolean(x_vlt_boolean() IS NOT UNKNOWN) FROM x; -- should not be precalculated +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean + x_stl2_boolean +---------------- + t + t + t + t +(4 rows) + +SELECT x_stl2_boolean(x_stl2_boolean(TRUE) IS TRUE) FROM x; +NOTICE: s2 boolean +NOTICE: s2 boolean + x_stl2_boolean +---------------- + t + t + t + t +(4 rows) + +SELECT x_stl2_boolean(x_stl2_boolean(TRUE) IS NOT TRUE) FROM x; +NOTICE: s2 boolean +NOTICE: s2 boolean + x_stl2_boolean +---------------- + f + f + f + f +(4 rows) + +SELECT x_stl2_boolean(x_stl2_boolean(TRUE) IS FALSE) FROM x; +NOTICE: s2 boolean +NOTICE: s2 boolean + x_stl2_boolean +---------------- + f + f + f + f +(4 rows) + +SELECT x_stl2_boolean(x_stl2_boolean(TRUE) IS NOT FALSE) FROM x; +NOTICE: s2 boolean +NOTICE: s2 boolean + x_stl2_boolean +---------------- + t + t + t + t +(4 rows) + +SELECT x_stl2_boolean(x_stl2_boolean(NULL) IS UNKNOWN) FROM x; +NOTICE: s2 boolean +NOTICE: s2 boolean + x_stl2_boolean +---------------- + t + t + t + t +(4 rows) + +SELECT x_stl2_boolean(x_stl2_boolean(NULL) IS NOT UNKNOWN) FROM x; +NOTICE: s2 boolean +NOTICE: s2 boolean + x_stl2_boolean +---------------- + f + f + f + f +(4 rows) + +-- CoerceToDomain expressions testing +SELECT x_stl2_my_integer_no_check(1::my_integer_no_check) FROM x; +NOTICE: s2 my_integer_no_check + x_stl2_my_integer_no_check +---------------------------- + 1 + 1 + 1 + 1 +(4 rows) + +SELECT x_stl2_my_integer_not_null(1::my_integer_not_null) FROM x; +NOTICE: s2 my_integer_not_null + x_stl2_my_integer_not_null +---------------------------- + 1 + 1 + 1 + 1 +(4 rows) + +SELECT x_stl2_my_integer_vlt_check(1::my_integer_vlt_check) FROM x; -- should not be precalculated +NOTICE: equal integers volatile +NOTICE: s2 my_integer_vlt_check +NOTICE: equal integers volatile +NOTICE: s2 my_integer_vlt_check +NOTICE: equal integers volatile +NOTICE: s2 my_integer_vlt_check +NOTICE: equal integers volatile +NOTICE: s2 my_integer_vlt_check + x_stl2_my_integer_vlt_check +----------------------------- + 1 + 1 + 1 + 1 +(4 rows) + +SELECT x_stl2_my_integer_stl_check(1::my_integer_stl_check) FROM x; +NOTICE: equal integers stable +NOTICE: s2 my_integer_stl_check + x_stl2_my_integer_stl_check +----------------------------- + 1 + 1 + 1 + 1 +(4 rows) + +SELECT x_stl2_my_integer_imm_check(1::my_integer_imm_check) FROM x; +NOTICE: equal integers immutable +NOTICE: s2 my_integer_imm_check + x_stl2_my_integer_imm_check +----------------------------- + 1 + 1 + 1 + 1 +(4 rows) + +-- Mixed functions and CoerceToDomain expressions testing +SELECT x_stl2_my_integer_no_check(x_vlt()::my_integer_no_check) FROM x; -- should not be precalculated +NOTICE: v +NOTICE: s2 my_integer_no_check +NOTICE: v +NOTICE: s2 my_integer_no_check +NOTICE: v +NOTICE: s2 my_integer_no_check +NOTICE: v +NOTICE: s2 my_integer_no_check + x_stl2_my_integer_no_check +---------------------------- + 1 + 1 + 1 + 1 +(4 rows) + +SELECT x_stl2_my_integer_not_null(x_vlt()::my_integer_not_null) FROM x; -- should not be precalculated +NOTICE: v +NOTICE: s2 my_integer_not_null +NOTICE: v +NOTICE: s2 my_integer_not_null +NOTICE: v +NOTICE: s2 my_integer_not_null +NOTICE: v +NOTICE: s2 my_integer_not_null + x_stl2_my_integer_not_null +---------------------------- + 1 + 1 + 1 + 1 +(4 rows) + +SELECT x_stl2_my_integer_stl_check(x_vlt()::my_integer_stl_check) FROM x; -- should not be precalculated +NOTICE: v +NOTICE: equal integers stable +NOTICE: s2 my_integer_stl_check +NOTICE: v +NOTICE: equal integers stable +NOTICE: s2 my_integer_stl_check +NOTICE: v +NOTICE: equal integers stable +NOTICE: s2 my_integer_stl_check +NOTICE: v +NOTICE: equal integers stable +NOTICE: s2 my_integer_stl_check + x_stl2_my_integer_stl_check +----------------------------- + 1 + 1 + 1 + 1 +(4 rows) + +SELECT x_stl2_my_integer_imm_check(x_vlt()::my_integer_imm_check) FROM x; -- should not be precalculated +NOTICE: v +NOTICE: equal integers immutable +NOTICE: s2 my_integer_imm_check +NOTICE: v +NOTICE: equal integers immutable +NOTICE: s2 my_integer_imm_check +NOTICE: v +NOTICE: equal integers immutable +NOTICE: s2 my_integer_imm_check +NOTICE: v +NOTICE: equal integers immutable +NOTICE: s2 my_integer_imm_check + x_stl2_my_integer_imm_check +----------------------------- + 1 + 1 + 1 + 1 +(4 rows) + +SELECT x_stl2_my_integer_no_check(x_stl()::my_integer_no_check) FROM x; +NOTICE: s +NOTICE: s2 my_integer_no_check + x_stl2_my_integer_no_check +---------------------------- + 1 + 1 + 1 + 1 +(4 rows) + +SELECT x_stl2_my_integer_not_null(x_stl()::my_integer_not_null) FROM x; +NOTICE: s +NOTICE: s2 my_integer_not_null + x_stl2_my_integer_not_null +---------------------------- + 1 + 1 + 1 + 1 +(4 rows) + +SELECT x_stl2_my_integer_stl_check(x_stl()::my_integer_stl_check) FROM x; +NOTICE: s +NOTICE: equal integers stable +NOTICE: s2 my_integer_stl_check + x_stl2_my_integer_stl_check +----------------------------- + 1 + 1 + 1 + 1 +(4 rows) + +SELECT x_stl2_my_integer_imm_check(x_stl()::my_integer_imm_check) FROM x; +NOTICE: s +NOTICE: equal integers immutable +NOTICE: s2 my_integer_imm_check + x_stl2_my_integer_imm_check +----------------------------- + 1 + 1 + 1 + 1 +(4 rows) + +-- Tracking functions testing +SET track_functions TO 'all'; +-- Simple functions testing +SELECT x_vlt() FROM x; -- should not be precalculated +NOTICE: v +NOTICE: v +NOTICE: v +NOTICE: v + x_vlt +------- + 1 + 1 + 1 + 1 +(4 rows) + +SELECT x_stl() FROM x; +NOTICE: s + x_stl +------- + 1 + 1 + 1 + 1 +(4 rows) + +-- WHERE clause testing +SELECT x_vlt() FROM 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 x WHERE x_stl() < x; +NOTICE: s +NOTICE: s +NOTICE: s + x_stl +------- + 1 + 1 + 1 +(3 rows) + +-- JOIN/ON clause testing +SELECT * FROM x JOIN generate_series(1, 2) y ON x_vlt() < x; -- should not be precalculated +NOTICE: v +NOTICE: v +NOTICE: v +NOTICE: v + x | y +---+--- + 2 | 1 + 3 | 1 + 4 | 1 + 2 | 2 + 3 | 2 + 4 | 2 +(6 rows) + +SELECT * FROM x JOIN generate_series(1, 2) y ON x_stl() < x; +NOTICE: s +NOTICE: s + x | y +---+--- + 2 | 1 + 3 | 1 + 4 | 1 + 2 | 2 + 3 | 2 + 4 | 2 +(6 rows) + +-- Functions with constant arguments testing +SELECT x_vlt2(1) FROM x; -- should not be precalculated +NOTICE: v2 +NOTICE: v2 +NOTICE: v2 +NOTICE: v2 + x_vlt2 +-------- + 1 + 1 + 1 + 1 +(4 rows) + +SELECT x_stl2(1) FROM x; +NOTICE: s2 + x_stl2 +-------- + 1 + 1 + 1 + 1 +(4 rows) + +-- Nested functions testing +SELECT x_stl2(x_vlt()) FROM 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 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_stl()) FROM x; +NOTICE: s +NOTICE: s2 + x_stl2 +-------- + 1 + 1 + 1 + 1 +(4 rows) + +SELECT x_imm2(x_stl()) FROM x; +NOTICE: s +NOTICE: i2 + x_imm2 +-------- + 1 + 1 + 1 + 1 +(4 rows) + +-- Strict functions testing +SELECT x_stl2_strict(x_vlt()) FROM 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 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 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 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 x; +NOTICE: s2 + x_stl2_strict +--------------- + + + + +(4 rows) + +SELECT x_imm2_strict(x_stl2(NULL)) FROM x; +NOTICE: s2 + x_imm2_strict +--------------- + + + + +(4 rows) + +-- Operators testing +SELECT 1 === 2 FROM 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 x; +NOTICE: equal integers stable + ?column? +---------- + f + f + f + f +(4 rows) + +-- Strict operators testing +SELECT x_stl2_boolean(NULL) ==== TRUE FROM x; +NOTICE: s2 boolean + ?column? +---------- + + + + +(4 rows) + +SELECT x_stl2_boolean(NULL) ===== TRUE FROM x; +NOTICE: s2 boolean +NOTICE: equal booleans immutable + ?column? +---------- + + + + +(4 rows) + +-- Mixed functions and operators testing +SELECT x_stl2_boolean(1 === 2) FROM x; -- should not be precalculated +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) + +SELECT x_stl2_boolean(1 ==== 2) FROM x; +NOTICE: equal integers stable +NOTICE: s2 boolean + x_stl2_boolean +---------------- + f + f + f + f +(4 rows) + +SELECT x_vlt() ==== 1 FROM 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? +---------- + t + t + t + t +(4 rows) + +SELECT x_stl() ==== 1 FROM x; +NOTICE: s +NOTICE: equal integers stable + ?column? +---------- + t + t + t + t +(4 rows) + +-- Mixed functions and IS (NOT) 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( + ('(1)'::my_integer IS DISTINCT FROM '(1)'::my_integer), + ('(1)'::my_integer IS NOT DISTINCT FROM '(1)'::my_integer) +) +FROM 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 + equal_booleans_stl_strict +--------------------------- + 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 equal_booleans_stl_strict( + ('(1)'::my_integer IS DISTINCT FROM '(1)'::my_integer), + ('(1)'::my_integer IS NOT DISTINCT FROM '(1)'::my_integer) +) +FROM x; +NOTICE: equal my_integer stable +NOTICE: equal my_integer stable +NOTICE: equal booleans stable strict + equal_booleans_stl_strict +--------------------------- + f + f + f + f +(4 rows) + +SELECT (x_vlt_my_integer() IS DISTINCT FROM '(1)'::my_integer) FROM x; -- should not be precalculated +NOTICE: v my_integer +NOTICE: equal my_integer stable +NOTICE: v my_integer +NOTICE: equal my_integer stable +NOTICE: v my_integer +NOTICE: equal my_integer stable +NOTICE: v my_integer +NOTICE: equal my_integer stable + ?column? +---------- + f + f + f + f +(4 rows) + +SELECT (x_vlt_my_integer() IS NOT DISTINCT FROM '(1)'::my_integer) FROM x; -- should not be precalculated +NOTICE: v my_integer +NOTICE: equal my_integer stable +NOTICE: v my_integer +NOTICE: equal my_integer stable +NOTICE: v my_integer +NOTICE: equal my_integer stable +NOTICE: v my_integer +NOTICE: equal my_integer stable + ?column? +---------- + t + t + t + t +(4 rows) + +SELECT (x_stl_my_integer() IS DISTINCT FROM '(1)'::my_integer) FROM x; +NOTICE: s my_integer +NOTICE: equal my_integer stable + ?column? +---------- + f + f + f + f +(4 rows) + +SELECT (x_stl_my_integer() IS NOT DISTINCT FROM '(1)'::my_integer) FROM x; +NOTICE: s my_integer +NOTICE: equal my_integer stable + ?column? +---------- + t + t + t + t +(4 rows) + +-- Mixed functions 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 equal_my_integer_stl( + NULLIF('(1)'::my_integer, '(2)'::my_integer), + NULLIF('(2)'::my_integer, '(2)'::my_integer) +) +FROM 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 + equal_my_integer_stl +---------------------- + + + + +(4 rows) + +DROP OPERATOR = (my_integer, my_integer); +CREATE OPERATOR = ( + PROCEDURE = equal_my_integer_stl, + LEFTARG = my_integer, + RIGHTARG = my_integer +); +SELECT equal_my_integer_stl( + NULLIF('(1)'::my_integer, '(2)'::my_integer), + NULLIF('(2)'::my_integer, '(2)'::my_integer) +) +FROM x; +NOTICE: equal my_integer stable +NOTICE: equal my_integer stable +NOTICE: equal my_integer stable + equal_my_integer_stl +---------------------- + + + + +(4 rows) + +SELECT NULLIF(x_vlt_my_integer(), '(2)'::my_integer) FROM x; -- should not be precalculated +NOTICE: v my_integer +NOTICE: equal my_integer stable +NOTICE: v my_integer +NOTICE: equal my_integer stable +NOTICE: v my_integer +NOTICE: equal my_integer stable +NOTICE: v my_integer +NOTICE: equal my_integer stable + nullif +-------- + (1) + (1) + (1) + (1) +(4 rows) + +SELECT NULLIF(x_stl_my_integer(), '(2)'::my_integer) FROM x; +NOTICE: s my_integer +NOTICE: equal my_integer stable + nullif +-------- + (1) + (1) + (1) + (1) +(4 rows) + +-- Mixed functions and "scalar op ANY/ALL (array)" / "scalar IN (2 or more +-- values)" expressions testing +SELECT x_stl2_boolean(1 === ANY ('{2, 3}')) FROM x; -- should not be precalculated +NOTICE: equal integers volatile +NOTICE: equal integers volatile +NOTICE: s2 boolean +NOTICE: equal integers volatile +NOTICE: equal integers volatile +NOTICE: s2 boolean +NOTICE: equal integers volatile +NOTICE: equal integers volatile +NOTICE: s2 boolean +NOTICE: equal integers volatile +NOTICE: equal integers volatile +NOTICE: s2 boolean + x_stl2_boolean +---------------- + f + f + f + f +(4 rows) + +SELECT x_stl2_boolean(1 === ALL ('{2, 3}')) FROM x; -- should not be precalculated +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_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) +) +FROM 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) + +SELECT x_stl2_boolean(1 ==== ANY ('{2, 3}')) FROM x; +NOTICE: equal integers stable +NOTICE: equal integers stable +NOTICE: s2 boolean + x_stl2_boolean +---------------- + f + f + f + f +(4 rows) + +SELECT x_stl2_boolean(1 ==== ALL ('{2, 3}')) FROM x; +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) +) +FROM x; +NOTICE: equal my_integer stable +NOTICE: equal my_integer stable +NOTICE: s2 boolean + x_stl2_boolean +---------------- + f + f + f + f +(4 rows) + +SELECT x_vlt() ==== ANY ('{2, 3}') FROM x; -- should not be precalculated +NOTICE: v +NOTICE: equal integers stable +NOTICE: equal integers stable +NOTICE: v +NOTICE: equal integers stable +NOTICE: equal integers stable +NOTICE: v +NOTICE: equal integers stable +NOTICE: equal integers stable +NOTICE: v +NOTICE: equal integers stable +NOTICE: equal integers stable + ?column? +---------- + f + f + f + f +(4 rows) + +SELECT x_vlt() ==== ALL ('{2, 3}') FROM 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 1 ==== ANY (x_vlt_array_integer()) FROM x; -- should not be precalculated +NOTICE: v array_integer +NOTICE: equal integers stable +NOTICE: equal integers stable +NOTICE: v array_integer +NOTICE: equal integers stable +NOTICE: equal integers stable +NOTICE: v array_integer +NOTICE: equal integers stable +NOTICE: equal integers stable +NOTICE: v array_integer +NOTICE: equal integers stable +NOTICE: equal integers stable + ?column? +---------- + f + f + f + f +(4 rows) + +SELECT 1 ==== ALL (x_vlt_array_integer()) FROM x; -- should not be precalculated +NOTICE: v array_integer +NOTICE: equal integers stable +NOTICE: v array_integer +NOTICE: equal integers stable +NOTICE: v array_integer +NOTICE: equal integers stable +NOTICE: v array_integer +NOTICE: equal integers stable + ?column? +---------- + f + f + f + f +(4 rows) + +SELECT x_vlt_my_integer() IN ('(2)'::my_integer, '(3)'::my_integer) FROM x; -- should not be precalculated +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 + ?column? +---------- + f + f + f + f +(4 rows) + +SELECT x_stl() ==== ANY ('{2, 3}') FROM x; +NOTICE: s +NOTICE: equal integers stable +NOTICE: equal integers stable + ?column? +---------- + f + f + f + f +(4 rows) + +SELECT x_stl() ==== ALL ('{2, 3}') FROM x; +NOTICE: s +NOTICE: equal integers stable + ?column? +---------- + f + f + f + f +(4 rows) + +SELECT 1 ==== ANY (x_stl_array_integer()) FROM x; +NOTICE: s array_integer +NOTICE: equal integers stable +NOTICE: equal integers stable + ?column? +---------- + f + f + f + f +(4 rows) + +SELECT 1 ==== ALL (x_stl_array_integer()) FROM x; +NOTICE: s array_integer +NOTICE: equal integers stable + ?column? +---------- + f + f + f + f +(4 rows) + +SELECT x_stl_my_integer() IN ('(2)'::my_integer, '(3)'::my_integer) FROM x; +NOTICE: s my_integer +NOTICE: equal my_integer stable +NOTICE: equal my_integer stable + ?column? +---------- + f + f + f + f +(4 rows) + +-- Mixed functions and boolean expressions testing +SELECT x_stl2_boolean(x_vlt_boolean() AND x_stl2_boolean(TRUE)) FROM x; -- should not be precalculated +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean + x_stl2_boolean +---------------- + t + t + t + t +(4 rows) + +SELECT x_stl2_boolean(x_vlt_boolean() OR x_stl2_boolean(TRUE)) FROM x; -- should not be precalculated +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean + x_stl2_boolean +---------------- + t + t + t + t +(4 rows) + +SELECT x_stl2_boolean(NOT x_vlt_boolean()) FROM x; -- should not be precalculated +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean + x_stl2_boolean +---------------- + f + f + f + f +(4 rows) + +SELECT x_stl2_boolean(x_stl2_boolean(TRUE) AND x_stl2_boolean(TRUE)) FROM x; +NOTICE: s2 boolean +NOTICE: s2 boolean +NOTICE: s2 boolean + x_stl2_boolean +---------------- + t + t + t + t +(4 rows) + +SELECT x_stl2_boolean(x_stl2_boolean(TRUE) OR x_stl2_boolean(TRUE)) FROM x; +NOTICE: s2 boolean +NOTICE: s2 boolean + x_stl2_boolean +---------------- + t + t + t + t +(4 rows) + +SELECT x_stl2_boolean(NOT x_stl2_boolean(TRUE)) FROM x; +NOTICE: s2 boolean +NOTICE: s2 boolean + x_stl2_boolean +---------------- + f + f + f + f +(4 rows) + +-- Mixed functions and ARRAY[] expressions testing +SELECT x_stl2_array_integer(ARRAY[x_vlt()]) FROM x; -- should not be precalculated +NOTICE: v +NOTICE: s2 array_integer +NOTICE: v +NOTICE: s2 array_integer +NOTICE: v +NOTICE: s2 array_integer +NOTICE: v +NOTICE: s2 array_integer + x_stl2_array_integer +---------------------- + {1} + {1} + {1} + {1} +(4 rows) + +SELECT x_stl2_array_integer(ARRAY[x_stl()]) FROM x; +NOTICE: s +NOTICE: s2 array_integer + x_stl2_array_integer +---------------------- + {1} + {1} + {1} + {1} +(4 rows) + +-- Mixed functions and array subscripting operations testing +SELECT x_stl2((x_vlt_array_integer())[x_vlt()]) FROM x; -- should not be precalculated +NOTICE: v array_integer +NOTICE: v +NOTICE: s2 +NOTICE: v array_integer +NOTICE: v +NOTICE: s2 +NOTICE: v array_integer +NOTICE: v +NOTICE: s2 +NOTICE: v array_integer +NOTICE: v +NOTICE: s2 + x_stl2 +-------- + 2 + 2 + 2 + 2 +(4 rows) + +SELECT x_stl2((x_vlt_array_integer())[1]) FROM x; -- should not be precalculated +NOTICE: v array_integer +NOTICE: s2 +NOTICE: v array_integer +NOTICE: s2 +NOTICE: v array_integer +NOTICE: s2 +NOTICE: v array_integer +NOTICE: s2 + x_stl2 +-------- + 2 + 2 + 2 + 2 +(4 rows) + +SELECT x_stl2_array_integer((x_vlt_array_integer())[:]) FROM x; -- should not be precalculated +NOTICE: v array_integer +NOTICE: s2 array_integer +NOTICE: v array_integer +NOTICE: s2 array_integer +NOTICE: v array_integer +NOTICE: s2 array_integer +NOTICE: v array_integer +NOTICE: s2 array_integer + x_stl2_array_integer +---------------------- + {2,3} + {2,3} + {2,3} + {2,3} +(4 rows) + +SELECT x_stl2(('{1, 2}'::integer[])[x_vlt()]) FROM 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_stl2((x_stl_array_integer())[x_stl()]) FROM x; +NOTICE: s array_integer +NOTICE: s +NOTICE: s2 + x_stl2 +-------- + 2 + 2 + 2 + 2 +(4 rows) + +SELECT x_stl2((x_stl_array_integer())[1]) FROM x; +NOTICE: s array_integer +NOTICE: s2 + x_stl2 +-------- + 2 + 2 + 2 + 2 +(4 rows) + +SELECT x_stl2_array_integer((x_stl_array_integer())[:]) FROM x; +NOTICE: s array_integer +NOTICE: s2 array_integer + x_stl2_array_integer +---------------------- + {2,3} + {2,3} + {2,3} + {2,3} +(4 rows) + +SELECT x_stl2(('{1, 2}'::integer[])[x_stl()]) FROM x; +NOTICE: s +NOTICE: s2 + x_stl2 +-------- + 1 + 1 + 1 + 1 +(4 rows) + +-- Mixed functions and FieldSelect expressions testing +SELECT x_stl2((x_vlt_wxyz()).w) FROM x; -- should not be precalculated +NOTICE: v wxyz +NOTICE: s2 +NOTICE: v wxyz +NOTICE: s2 +NOTICE: v wxyz +NOTICE: s2 +NOTICE: v wxyz +NOTICE: s2 + x_stl2 +-------- + 1 + 1 + 1 + 1 +(4 rows) + +SELECT x_stl2((x_vlt_my_integer()).value) FROM x; -- should not be precalculated +NOTICE: v my_integer +NOTICE: s2 +NOTICE: v my_integer +NOTICE: s2 +NOTICE: v my_integer +NOTICE: s2 +NOTICE: v my_integer +NOTICE: s2 + x_stl2 +-------- + 1 + 1 + 1 + 1 +(4 rows) + +SELECT x_stl2((x_stl_wxyz()).w) FROM x; +NOTICE: s wxyz +NOTICE: s2 + x_stl2 +-------- + 1 + 1 + 1 + 1 +(4 rows) + +SELECT x_stl2((x_stl_my_integer()).value) FROM x; +NOTICE: s my_integer +NOTICE: s2 + x_stl2 +-------- + 1 + 1 + 1 + 1 +(4 rows) + +-- Mixed functions and ROW() expressions testing +SELECT x_stl2_wxyz((x_vlt(), '{2}', TRUE, 3)) FROM x; -- should not be precalculated +NOTICE: v +NOTICE: s2 wxyz +NOTICE: v +NOTICE: s2 wxyz +NOTICE: v +NOTICE: s2 wxyz +NOTICE: v +NOTICE: s2 wxyz + x_stl2_wxyz +------------- + (1,{2},t,3) + (1,{2},t,3) + (1,{2},t,3) + (1,{2},t,3) +(4 rows) + +SELECT x_stl2_wxyz((x_stl(), '{2}', TRUE, 3)) FROM x; +NOTICE: s +NOTICE: s2 wxyz + x_stl2_wxyz +------------- + (1,{2},t,3) + (1,{2},t,3) + (1,{2},t,3) + (1,{2},t,3) +(4 rows) + +-- Mixed functions and RelabelType expressions testing +SELECT x_stl2(x_vlt_oid()::integer) FROM x; -- should not be precalculated +NOTICE: v oid +NOTICE: s2 +NOTICE: v oid +NOTICE: s2 +NOTICE: v oid +NOTICE: s2 +NOTICE: v oid +NOTICE: s2 + x_stl2 +-------- + 1 + 1 + 1 + 1 +(4 rows) + +SELECT x_stl2(x_stl_oid()::integer) FROM x; +NOTICE: s oid +NOTICE: s2 + x_stl2 +-------- + 1 + 1 + 1 + 1 +(4 rows) + +-- Mixed functions and CoerceViaIO expressions testing +SELECT x_stl2(x_vlt_text_integer()::integer) FROM x; -- should not be precalculated +NOTICE: v text integer +NOTICE: s2 +NOTICE: v text integer +NOTICE: s2 +NOTICE: v text integer +NOTICE: s2 +NOTICE: v text integer +NOTICE: s2 + x_stl2 +-------- + 1 + 1 + 1 + 1 +(4 rows) + +SELECT x_stl2(x_stl_text_integer()::integer) FROM x; +NOTICE: s text integer +NOTICE: s2 + x_stl2 +-------- + 1 + 1 + 1 + 1 +(4 rows) + +SELECT x_stl2_my_integer(x_vlt_text_my_integer()::my_integer) FROM x; -- should not be precalculated +NOTICE: v text my_integer +NOTICE: s2 my_integer +NOTICE: v text my_integer +NOTICE: s2 my_integer +NOTICE: v text my_integer +NOTICE: s2 my_integer +NOTICE: v text my_integer +NOTICE: s2 my_integer + x_stl2_my_integer +------------------- + (1) + (1) + (1) + (1) +(4 rows) + +SELECT x_stl2_my_integer(x_stl_text_my_integer()::my_integer) FROM x; +NOTICE: s text my_integer +NOTICE: s2 my_integer + x_stl2_my_integer +------------------- + (1) + (1) + (1) + (1) +(4 rows) + +-- Mixed functions and ArrayCoerce expressions testing +-- Binary-coercible types: +SELECT x_stl2_array_oid(x_vlt_array_integer()::oid[]) FROM x; -- should not be precalculated +NOTICE: v array_integer +NOTICE: s2 array_oid +NOTICE: v array_integer +NOTICE: s2 array_oid +NOTICE: v array_integer +NOTICE: s2 array_oid +NOTICE: v array_integer +NOTICE: s2 array_oid + x_stl2_array_oid +------------------ + {2,3} + {2,3} + {2,3} + {2,3} +(4 rows) + +SELECT x_stl2_array_oid(x_stl_array_integer()::oid[]) FROM x; +NOTICE: s array_integer +NOTICE: s2 array_oid + x_stl2_array_oid +------------------ + {2,3} + {2,3} + {2,3} + {2,3} +(4 rows) + +-- Not binary-coercible types: +DROP CAST (my_integer AS integer); +CREATE CAST (my_integer AS integer) + WITH FUNCTION cast_my_integer_as_integer_vlt; +SELECT x_stl2_array_integer('{(1), (2)}'::my_integer[]::integer[]) FROM x; -- should not be precalculated +NOTICE: cast my_integer as integer volatile +NOTICE: cast my_integer as integer volatile +NOTICE: s2 array_integer +NOTICE: cast my_integer as integer volatile +NOTICE: cast my_integer as integer volatile +NOTICE: s2 array_integer +NOTICE: cast my_integer as integer volatile +NOTICE: cast my_integer as integer volatile +NOTICE: s2 array_integer +NOTICE: cast my_integer as integer volatile +NOTICE: cast my_integer as integer volatile +NOTICE: s2 array_integer + x_stl2_array_integer +---------------------- + {1,2} + {1,2} + {1,2} + {1,2} +(4 rows) + +DROP CAST (my_integer AS integer); +CREATE CAST (my_integer AS integer) + WITH FUNCTION cast_my_integer_as_integer_stl; +SELECT x_stl2_array_integer('{(1), (2)}'::my_integer[]::integer[]) FROM x; +NOTICE: cast my_integer as integer stable +NOTICE: cast my_integer as integer stable +NOTICE: s2 array_integer + x_stl2_array_integer +---------------------- + {1,2} + {1,2} + {1,2} + {1,2} +(4 rows) + +DROP CAST (integer AS my_integer); +CREATE CAST (integer AS my_integer) + WITH FUNCTION cast_integer_as_my_integer_stl; +SELECT x_vlt_array_integer()::my_integer[] FROM x; -- should not be precalculated +NOTICE: v array_integer +NOTICE: cast integer as my_integer stable +NOTICE: cast integer as my_integer stable +NOTICE: v array_integer +NOTICE: cast integer as my_integer stable +NOTICE: cast integer as my_integer stable +NOTICE: v array_integer +NOTICE: cast integer as my_integer stable +NOTICE: cast integer as my_integer stable +NOTICE: v array_integer +NOTICE: cast integer as my_integer stable +NOTICE: cast integer as my_integer stable + x_vlt_array_integer +--------------------- + {(2),(3)} + {(2),(3)} + {(2),(3)} + {(2),(3)} +(4 rows) + +SELECT x_stl_array_integer()::my_integer[] FROM x; +NOTICE: s array_integer +NOTICE: cast integer as my_integer stable +NOTICE: cast integer as my_integer stable + x_stl_array_integer +--------------------- + {(2),(3)} + {(2),(3)} + {(2),(3)} + {(2),(3)} +(4 rows) + +-- Mixed functions and ConvertRowtypeExpr testing +SELECT x_stl2_wxyz(x_vlt_wxyz_child()::wxyz_child::wxyz) FROM x; -- should not be precalculated +NOTICE: v wxyz_child +NOTICE: s2 wxyz +NOTICE: v wxyz_child +NOTICE: s2 wxyz +NOTICE: v wxyz_child +NOTICE: s2 wxyz +NOTICE: v wxyz_child +NOTICE: s2 wxyz + x_stl2_wxyz +------------- + (1,{2},t,3) + (1,{2},t,3) + (1,{2},t,3) + (1,{2},t,3) +(4 rows) + +SELECT x_stl2_wxyz(x_vlt_wxyz_child2()::wxyz_child2::wxyz) FROM x; -- should not be precalculated +NOTICE: v wxyz_child2 +NOTICE: s2 wxyz +NOTICE: v wxyz_child2 +NOTICE: s2 wxyz +NOTICE: v wxyz_child2 +NOTICE: s2 wxyz +NOTICE: v wxyz_child2 +NOTICE: s2 wxyz + x_stl2_wxyz +------------- + (1,{2},t,3) + (1,{2},t,3) + (1,{2},t,3) + (1,{2},t,3) +(4 rows) + +SELECT x_stl2_wxyz(x_stl_wxyz_child()::wxyz_child::wxyz) FROM x; +NOTICE: s wxyz_child +NOTICE: s2 wxyz + x_stl2_wxyz +------------- + (1,{2},t,3) + (1,{2},t,3) + (1,{2},t,3) + (1,{2},t,3) +(4 rows) + +SELECT x_stl2_wxyz(x_stl_wxyz_child2()::wxyz_child2::wxyz) FROM x; +NOTICE: s wxyz_child2 +NOTICE: s2 wxyz + x_stl2_wxyz +------------- + (1,{2},t,3) + (1,{2},t,3) + (1,{2},t,3) + (1,{2},t,3) +(4 rows) + +-- Mixed functions and CASE expressions testing +SELECT x_stl2(CASE WHEN x_vlt_boolean() THEN x_vlt() ELSE x_vlt() END) FROM x; -- should not be precalculated +NOTICE: v boolean +NOTICE: v +NOTICE: s2 +NOTICE: v boolean +NOTICE: v +NOTICE: s2 +NOTICE: v boolean +NOTICE: v +NOTICE: s2 +NOTICE: v boolean +NOTICE: v +NOTICE: s2 + x_stl2 +-------- + 1 + 1 + 1 + 1 +(4 rows) + +SELECT x_stl2(CASE x_vlt() WHEN x_vlt() THEN x_vlt() ELSE x_vlt() END) FROM x; -- should not be precalculated +NOTICE: v +NOTICE: v +NOTICE: v +NOTICE: s2 +NOTICE: v +NOTICE: v +NOTICE: v +NOTICE: s2 +NOTICE: v +NOTICE: v +NOTICE: v +NOTICE: s2 +NOTICE: v +NOTICE: v +NOTICE: v +NOTICE: s2 + x_stl2 +-------- + 1 + 1 + 1 + 1 +(4 rows) + +SELECT x_stl2(CASE WHEN x_stl2_boolean(TRUE) THEN x_stl() ELSE x_stl() END) +FROM x; +NOTICE: s2 boolean +NOTICE: s +NOTICE: s2 + x_stl2 +-------- + 1 + 1 + 1 + 1 +(4 rows) + +SELECT x_stl2(CASE x_stl() WHEN x_stl() THEN x_stl() ELSE x_stl() END) FROM x; +NOTICE: s +NOTICE: s +NOTICE: s +NOTICE: s2 + x_stl2 +-------- + 1 + 1 + 1 + 1 +(4 rows) + +-- Mixed functions and RowCompareExpr testing +SELECT x_stl2_boolean((x_vlt(), 2) < (1, 3)) FROM x; -- should not be precalculated +NOTICE: v +NOTICE: s2 boolean +NOTICE: v +NOTICE: s2 boolean +NOTICE: v +NOTICE: s2 boolean +NOTICE: v +NOTICE: s2 boolean + x_stl2_boolean +---------------- + t + t + t + t +(4 rows) + +SELECT x_stl2_boolean((x_stl(), 2) < (1, 3)) FROM x; +NOTICE: s +NOTICE: s2 boolean + x_stl2_boolean +---------------- + t + t + t + t +(4 rows) + +-- Mixed functions and COALESCE expressions testing +SELECT x_stl2(COALESCE(NULL, x_vlt2(NULL), 2)) FROM x; -- should not be precalculated +NOTICE: v2 +NOTICE: s2 +NOTICE: v2 +NOTICE: s2 +NOTICE: v2 +NOTICE: s2 +NOTICE: v2 +NOTICE: s2 + x_stl2 +-------- + 2 + 2 + 2 + 2 +(4 rows) + +SELECT x_stl2(COALESCE(NULL, x_stl2(NULL), 2)) FROM x; +NOTICE: s2 +NOTICE: s2 + x_stl2 +-------- + 2 + 2 + 2 + 2 +(4 rows) + +-- Mixed functions and GREATEST and LEAST functions testing +SELECT x_stl2(GREATEST(2, x_vlt(), 3)) FROM x; -- should not be precalculated +NOTICE: v +NOTICE: s2 +NOTICE: v +NOTICE: s2 +NOTICE: v +NOTICE: s2 +NOTICE: v +NOTICE: s2 + x_stl2 +-------- + 3 + 3 + 3 + 3 +(4 rows) + +SELECT x_stl2(LEAST(2, x_vlt(), 3)) FROM 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_stl2(GREATEST(2, x_stl(), 3)) FROM x; +NOTICE: s +NOTICE: s2 + x_stl2 +-------- + 3 + 3 + 3 + 3 +(4 rows) + +SELECT x_stl2(LEAST(2, x_stl(), 3)) FROM x; +NOTICE: s +NOTICE: s2 + x_stl2 +-------- + 1 + 1 + 1 + 1 +(4 rows) + +-- Mixed functions and Xml expressions testing +-- should not be precalculated +SELECT x_stl2_xml(XMLCONCAT('', x_vlt_xml())) FROM x; +NOTICE: v xml +NOTICE: s2 xml +NOTICE: v xml +NOTICE: s2 xml +NOTICE: v xml +NOTICE: s2 xml +NOTICE: v xml +NOTICE: s2 xml + x_stl2_xml +---------------------- + foo + foo + foo + foo +(4 rows) + +-- should not be precalculated +SELECT x_stl2_xml( + XMLELEMENT(name foo, xmlattributes('bar' as bar), x_vlt_xml()) +) +FROM x; +NOTICE: v xml +NOTICE: s2 xml +NOTICE: v xml +NOTICE: s2 xml +NOTICE: v xml +NOTICE: s2 xml +NOTICE: v xml +NOTICE: s2 xml + x_stl2_xml +------------------------------------- + foo + foo + foo + foo +(4 rows) + +-- should not be precalculated +SELECT x_stl2_xml(XMLFOREST('abc' AS foo, x_vlt_xml() AS bar)) FROM x; +NOTICE: v xml +NOTICE: s2 xml +NOTICE: v xml +NOTICE: s2 xml +NOTICE: v xml +NOTICE: s2 xml +NOTICE: v xml +NOTICE: s2 xml + x_stl2_xml +----------------------------------------- + abcfoo + abcfoo + abcfoo + abcfoo +(4 rows) + +-- should not be precalculated +SELECT x_stl2_xml(XMLPARSE(DOCUMENT x_vlt_text_xml())) FROM x; +NOTICE: v text xml +NOTICE: s2 xml +NOTICE: v text xml +NOTICE: s2 xml +NOTICE: v text xml +NOTICE: s2 xml +NOTICE: v text xml +NOTICE: s2 xml + x_stl2_xml +------------------------------------ + Manual + Manual + Manual + Manual +(4 rows) + +-- should not be precalculated +SELECT x_stl2_xml(XMLPARSE(CONTENT x_vlt_text_xml_content())) FROM x; +NOTICE: v text xml content +NOTICE: s2 xml +NOTICE: v text xml content +NOTICE: s2 xml +NOTICE: v text xml content +NOTICE: s2 xml +NOTICE: v text xml content +NOTICE: s2 xml + x_stl2_xml +--------------------------------- + abcbarfoo + abcbarfoo + abcbarfoo + abcbarfoo +(4 rows) + +-- should not be precalculated +SELECT x_stl2_xml(XMLPI(name php, x_vlt_text_xml_instruction_content())) FROM x; +NOTICE: v text xml instruction content +NOTICE: s2 xml +NOTICE: v text xml instruction content +NOTICE: s2 xml +NOTICE: v text xml instruction content +NOTICE: s2 xml +NOTICE: v text xml instruction content +NOTICE: s2 xml + x_stl2_xml +----------------------------- + + + + +(4 rows) + +-- should not be precalculated +SELECT x_stl2_xml(XMLROOT(x_vlt_xml(), version '1.0', standalone yes)) FROM x; +NOTICE: v xml +NOTICE: s2 xml +NOTICE: v xml +NOTICE: s2 xml +NOTICE: v xml +NOTICE: s2 xml +NOTICE: v xml +NOTICE: s2 xml + x_stl2_xml +------------------------------------------------------ + foo + foo + foo + foo +(4 rows) + +-- should not be precalculated +SELECT x_stl2_text(XMLSERIALIZE(DOCUMENT x_vlt_xml() AS text)) FROM x; +NOTICE: v xml +NOTICE: s2 text +NOTICE: v xml +NOTICE: s2 text +NOTICE: v xml +NOTICE: s2 text +NOTICE: v xml +NOTICE: s2 text + x_stl2_text +---------------- + foo + foo + foo + foo +(4 rows) + +-- should not be precalculated +SELECT x_stl2_text(XMLSERIALIZE(CONTENT x_vlt_xml_content() AS text)) FROM x; +NOTICE: v xml content +NOTICE: s2 text +NOTICE: v xml content +NOTICE: s2 text +NOTICE: v xml content +NOTICE: s2 text +NOTICE: v xml content +NOTICE: s2 text + x_stl2_text +--------------------------------- + abcbarfoo + abcbarfoo + abcbarfoo + abcbarfoo +(4 rows) + +-- should not be precalculated +SELECT x_stl2_boolean(x_vlt_xml_content() IS DOCUMENT) FROM x; +NOTICE: v xml content +NOTICE: s2 boolean +NOTICE: v xml content +NOTICE: s2 boolean +NOTICE: v xml content +NOTICE: s2 boolean +NOTICE: v xml content +NOTICE: s2 boolean + x_stl2_boolean +---------------- + f + f + f + f +(4 rows) + +SELECT x_stl2_xml(XMLCONCAT('', x_stl_xml())) FROM x; +NOTICE: s xml +NOTICE: s2 xml + x_stl2_xml +---------------------- + foo + foo + foo + foo +(4 rows) + +SELECT x_stl2_xml( + XMLELEMENT(name foo, xmlattributes('bar' as bar), x_stl_xml()) +) +FROM x; +NOTICE: s xml +NOTICE: s2 xml + x_stl2_xml +------------------------------------- + foo + foo + foo + foo +(4 rows) + +SELECT x_stl2_xml(XMLFOREST('abc' AS foo, x_stl_xml() AS bar)) FROM x; +NOTICE: s xml +NOTICE: s2 xml + x_stl2_xml +----------------------------------------- + abcfoo + abcfoo + abcfoo + abcfoo +(4 rows) + +SELECT x_stl2_xml(XMLPARSE(DOCUMENT x_stl_text_xml())) FROM x; +NOTICE: s text xml +NOTICE: s2 xml + x_stl2_xml +------------------------------------ + Manual + Manual + Manual + Manual +(4 rows) + +SELECT x_stl2_xml(XMLPARSE(CONTENT x_stl_text_xml_content())) FROM x; +NOTICE: s xml content +NOTICE: s2 xml + x_stl2_xml +--------------------------------- + abcbarfoo + abcbarfoo + abcbarfoo + abcbarfoo +(4 rows) + +SELECT x_stl2_xml(XMLPI(name php, x_stl_text_xml_instruction_content())) FROM x; +NOTICE: s text xml instruction content +NOTICE: s2 xml + x_stl2_xml +----------------------------- + + + + +(4 rows) + +SELECT x_stl2_xml(XMLROOT(x_stl_xml(), version '1.0', standalone yes)) FROM x; +NOTICE: s xml +NOTICE: s2 xml + x_stl2_xml +------------------------------------------------------ + foo + foo + foo + foo +(4 rows) + +SELECT x_stl2_text(XMLSERIALIZE(DOCUMENT x_stl_xml() AS text)) FROM x; +NOTICE: s xml +NOTICE: s2 text + x_stl2_text +---------------- + foo + foo + foo + foo +(4 rows) + +SELECT x_stl2_text(XMLSERIALIZE(CONTENT x_stl_xml_content() AS text)) FROM x; +NOTICE: s xml content +NOTICE: s2 text + x_stl2_text +--------------------------------- + abcbarfoo + abcbarfoo + abcbarfoo + abcbarfoo +(4 rows) + +SELECT x_stl2_boolean(x_stl_xml_content() IS DOCUMENT) FROM x; +NOTICE: s xml content +NOTICE: s2 boolean + x_stl2_boolean +---------------- + f + f + f + f +(4 rows) + +-- Mixed functions and NullTest expressions testing +SELECT x_stl2_boolean(x_vlt() IS NULL) FROM x; -- should not be precalculated +NOTICE: v +NOTICE: s2 boolean +NOTICE: v +NOTICE: s2 boolean +NOTICE: v +NOTICE: s2 boolean +NOTICE: v +NOTICE: s2 boolean + x_stl2_boolean +---------------- + f + f + f + f +(4 rows) + +SELECT x_stl2_boolean(x_vlt() IS NOT NULL) FROM x; -- should not be precalculated +NOTICE: v +NOTICE: s2 boolean +NOTICE: v +NOTICE: s2 boolean +NOTICE: v +NOTICE: s2 boolean +NOTICE: v +NOTICE: s2 boolean + x_stl2_boolean +---------------- + t + t + t + t +(4 rows) + +SELECT x_stl2_boolean(x_vlt_wxyz() IS NULL) FROM x; -- should not be precalculated +NOTICE: v wxyz +NOTICE: s2 boolean +NOTICE: v wxyz +NOTICE: s2 boolean +NOTICE: v wxyz +NOTICE: s2 boolean +NOTICE: v wxyz +NOTICE: s2 boolean + x_stl2_boolean +---------------- + f + f + f + f +(4 rows) + +SELECT x_stl2_boolean(x_vlt_wxyz() IS NOT NULL) FROM x; -- should not be precalculated +NOTICE: v wxyz +NOTICE: s2 boolean +NOTICE: v wxyz +NOTICE: s2 boolean +NOTICE: v wxyz +NOTICE: s2 boolean +NOTICE: v wxyz +NOTICE: s2 boolean + x_stl2_boolean +---------------- + t + t + t + t +(4 rows) + +SELECT x_stl2_boolean(x_stl() IS NULL) FROM x; +NOTICE: s +NOTICE: s2 boolean + x_stl2_boolean +---------------- + f + f + f + f +(4 rows) + +SELECT x_stl2_boolean(x_stl() IS NOT NULL) FROM x; +NOTICE: s +NOTICE: s2 boolean + x_stl2_boolean +---------------- + t + t + t + t +(4 rows) + +SELECT x_stl2_boolean(x_stl_wxyz() IS NULL) FROM x; +NOTICE: s wxyz +NOTICE: s2 boolean + x_stl2_boolean +---------------- + f + f + f + f +(4 rows) + +SELECT x_stl2_boolean(x_stl_wxyz() IS NOT NULL) FROM x; +NOTICE: s wxyz +NOTICE: s2 boolean + x_stl2_boolean +---------------- + t + t + t + t +(4 rows) + +-- Mixed functions and BooleanTest expressions testing +SELECT x_stl2_boolean(x_vlt_boolean() IS TRUE) FROM x; -- should not be precalculated +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean + x_stl2_boolean +---------------- + t + t + t + t +(4 rows) + +SELECT x_stl2_boolean(x_vlt_boolean() IS NOT TRUE) FROM x; -- should not be precalculated +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean + x_stl2_boolean +---------------- + f + f + f + f +(4 rows) + +SELECT x_stl2_boolean(x_vlt_boolean() IS FALSE) FROM x; -- should not be precalculated +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean + x_stl2_boolean +---------------- + f + f + f + f +(4 rows) + +SELECT x_stl2_boolean(x_vlt_boolean() IS NOT FALSE) FROM x; -- should not be precalculated +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean + x_stl2_boolean +---------------- + t + t + t + t +(4 rows) + +SELECT x_stl2_boolean(x_vlt_boolean() IS UNKNOWN) FROM x; -- should not be precalculated +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean + x_stl2_boolean +---------------- + f + f + f + f +(4 rows) + +SELECT x_stl2_boolean(x_vlt_boolean() IS NOT UNKNOWN) FROM x; -- should not be precalculated +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean +NOTICE: v boolean +NOTICE: s2 boolean + x_stl2_boolean +---------------- + t + t + t + t +(4 rows) + +SELECT x_stl2_boolean(x_stl2_boolean(TRUE) IS TRUE) FROM x; +NOTICE: s2 boolean +NOTICE: s2 boolean + x_stl2_boolean +---------------- + t + t + t + t +(4 rows) + +SELECT x_stl2_boolean(x_stl2_boolean(TRUE) IS NOT TRUE) FROM x; +NOTICE: s2 boolean +NOTICE: s2 boolean + x_stl2_boolean +---------------- + f + f + f + f +(4 rows) + +SELECT x_stl2_boolean(x_stl2_boolean(TRUE) IS FALSE) FROM x; +NOTICE: s2 boolean +NOTICE: s2 boolean + x_stl2_boolean +---------------- + f + f + f + f +(4 rows) + +SELECT x_stl2_boolean(x_stl2_boolean(TRUE) IS NOT FALSE) FROM x; +NOTICE: s2 boolean +NOTICE: s2 boolean + x_stl2_boolean +---------------- + t + t + t + t +(4 rows) + +SELECT x_stl2_boolean(x_stl2_boolean(NULL) IS UNKNOWN) FROM x; +NOTICE: s2 boolean +NOTICE: s2 boolean + x_stl2_boolean +---------------- + t + t + t + t +(4 rows) + +SELECT x_stl2_boolean(x_stl2_boolean(NULL) IS NOT UNKNOWN) FROM x; +NOTICE: s2 boolean +NOTICE: s2 boolean + x_stl2_boolean +---------------- + f + f + f + f +(4 rows) + +-- Mixed functions and CoerceToDomain expressions testing +SELECT x_stl2_my_integer_no_check(x_vlt()::my_integer_no_check) FROM x; -- should not be precalculated +NOTICE: v +NOTICE: s2 my_integer_no_check +NOTICE: v +NOTICE: s2 my_integer_no_check +NOTICE: v +NOTICE: s2 my_integer_no_check +NOTICE: v +NOTICE: s2 my_integer_no_check + x_stl2_my_integer_no_check +---------------------------- + 1 + 1 + 1 + 1 +(4 rows) + +SELECT x_stl2_my_integer_not_null(x_vlt()::my_integer_not_null) FROM x; -- should not be precalculated +NOTICE: v +NOTICE: s2 my_integer_not_null +NOTICE: v +NOTICE: s2 my_integer_not_null +NOTICE: v +NOTICE: s2 my_integer_not_null +NOTICE: v +NOTICE: s2 my_integer_not_null + x_stl2_my_integer_not_null +---------------------------- + 1 + 1 + 1 + 1 +(4 rows) + +SELECT x_stl2_my_integer_stl_check(x_vlt()::my_integer_stl_check) FROM x; -- should not be precalculated +NOTICE: v +NOTICE: equal integers stable +NOTICE: s2 my_integer_stl_check +NOTICE: v +NOTICE: equal integers stable +NOTICE: s2 my_integer_stl_check +NOTICE: v +NOTICE: equal integers stable +NOTICE: s2 my_integer_stl_check +NOTICE: v +NOTICE: equal integers stable +NOTICE: s2 my_integer_stl_check + x_stl2_my_integer_stl_check +----------------------------- + 1 + 1 + 1 + 1 +(4 rows) + +SELECT x_stl2_my_integer_imm_check(x_vlt()::my_integer_imm_check) FROM x; -- should not be precalculated +NOTICE: v +NOTICE: equal integers immutable +NOTICE: s2 my_integer_imm_check +NOTICE: v +NOTICE: equal integers immutable +NOTICE: s2 my_integer_imm_check +NOTICE: v +NOTICE: equal integers immutable +NOTICE: s2 my_integer_imm_check +NOTICE: v +NOTICE: equal integers immutable +NOTICE: s2 my_integer_imm_check + x_stl2_my_integer_imm_check +----------------------------- + 1 + 1 + 1 + 1 +(4 rows) + +SELECT x_stl2_my_integer_no_check(x_stl()::my_integer_no_check) FROM x; +NOTICE: s +NOTICE: s2 my_integer_no_check + x_stl2_my_integer_no_check +---------------------------- + 1 + 1 + 1 + 1 +(4 rows) + +SELECT x_stl2_my_integer_not_null(x_stl()::my_integer_not_null) FROM x; +NOTICE: s +NOTICE: s2 my_integer_not_null + x_stl2_my_integer_not_null +---------------------------- + 1 + 1 + 1 + 1 +(4 rows) + +SELECT x_stl2_my_integer_stl_check(x_stl()::my_integer_stl_check) FROM x; +NOTICE: s +NOTICE: equal integers stable +NOTICE: s2 my_integer_stl_check + x_stl2_my_integer_stl_check +----------------------------- + 1 + 1 + 1 + 1 +(4 rows) + +SELECT x_stl2_my_integer_imm_check(x_stl()::my_integer_imm_check) FROM x; +NOTICE: s +NOTICE: equal integers immutable +NOTICE: s2 my_integer_imm_check + x_stl2_my_integer_imm_check +----------------------------- + 1 + 1 + 1 + 1 +(4 rows) + +SET track_functions TO DEFAULT; +-- ROW() expressions with dropped columns testing +ALTER TABLE wxyz DROP COLUMN z; +VACUUM FULL; +-- ROW() expressions testing +SELECT x_stl2_wxyz((1, '{2}', TRUE)) FROM x; +NOTICE: s2 wxyz + x_stl2_wxyz +------------- + (1,{2},t) + (1,{2},t) + (1,{2},t) + (1,{2},t) +(4 rows) + +SELECT x_stl2_wxyz(ROW(1, '{2}', TRUE)) FROM x; +NOTICE: s2 wxyz + x_stl2_wxyz +------------- + (1,{2},t) + (1,{2},t) + (1,{2},t) + (1,{2},t) +(4 rows) + +SELECT x_stl2_wxyz((1, '{2}', TRUE)::wxyz) FROM x; +NOTICE: s2 wxyz + x_stl2_wxyz +------------- + (1,{2},t) + (1,{2},t) + (1,{2},t) + (1,{2},t) +(4 rows) + +-- Mixed functions and ROW() expressions testing +SELECT x_stl2_wxyz((x_vlt(), '{2}', TRUE)) FROM x; -- should not be precalculated +NOTICE: v +NOTICE: s2 wxyz +NOTICE: v +NOTICE: s2 wxyz +NOTICE: v +NOTICE: s2 wxyz +NOTICE: v +NOTICE: s2 wxyz + x_stl2_wxyz +------------- + (1,{2},t) + (1,{2},t) + (1,{2},t) + (1,{2},t) +(4 rows) + +SELECT x_stl2_wxyz((x_stl(), '{2}', TRUE)) FROM x; +NOTICE: s +NOTICE: s2 wxyz + x_stl2_wxyz +------------- + (1,{2},t) + (1,{2},t) + (1,{2},t) + (1,{2},t) +(4 rows) + +-- 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 +-------- + 4 +(1 row) + +INSERT INTO x VALUES (5); +SELECT simple(); + simple +-------- + 5 +(1 row) + +ROLLBACK; +-- Drop tables and domains for testing +DROP TABLE x; +DROP FUNCTION x_vlt_wxyz, x_vlt_wxyz_child, x_vlt_wxyz_child2; +DROP FUNCTION x_stl_wxyz, x_stl_wxyz_child, x_stl_wxyz_child2, x_stl2_wxyz; +DROP TABLE wxyz, wxyz_child, wxyz_child2; +DROP FUNCTION x_stl2_no_columns; +DROP TABLE no_columns, no_columns_child, no_columns_child2; +DROP FUNCTION x_stl2_my_integer_no_check; +DROP DOMAIN my_integer_no_check; +DROP FUNCTION x_stl2_my_integer_not_null; +DROP DOMAIN my_integer_not_null; +DROP FUNCTION x_stl2_my_integer_vlt_check; +DROP DOMAIN my_integer_vlt_check; +DROP FUNCTION x_stl2_my_integer_stl_check; +DROP DOMAIN my_integer_stl_check; +DROP FUNCTION x_stl2_my_integer_imm_check; +DROP DOMAIN my_integer_imm_check; diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index 5e8b7e9..f73a64f 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -180,3 +180,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..43444b8 --- /dev/null +++ b/src/test/regress/sql/precalculate_stable_functions.sql @@ -0,0 +1,1843 @@ +-- +-- PRECALCULATE STABLE FUNCTIONS +-- +-- Create types and tables for testing + +CREATE TYPE my_integer AS (value integer); +CREATE TYPE composite_type AS (first integer, second integer[], third boolean); + +CREATE TABLE x (x integer); +INSERT INTO x SELECT generate_series(1, 4) x; + +CREATE TABLE wxyz (w integer, x integer[], y boolean, z integer); +CREATE TABLE wxyz_child () INHERITS (wxyz); +CREATE TABLE wxyz_child2 (a integer, b integer) INHERITS (wxyz); + +CREATE TABLE no_columns (); +CREATE TABLE no_columns_child () INHERITS (no_columns); +CREATE TABLE no_columns_child2 (a integer, b integer) INHERITS (no_columns); + +-- 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.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.x_vlt_array_integer ( +) +RETURNS int[] VOLATILE AS +$body$ +BEGIN + RAISE NOTICE 'v array_integer'; + RETURN '{2, 3}'::integer[]; +END; +$body$ +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION public.x_vlt_boolean ( +) +RETURNS boolean VOLATILE AS +$body$ +BEGIN + RAISE NOTICE 'v boolean'; + RETURN TRUE; +END; +$body$ +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION public.x_vlt_wxyz ( +) +RETURNS wxyz VOLATILE AS +$body$ +BEGIN + RAISE NOTICE 'v wxyz'; + RETURN '(1, {2}, TRUE, 3)'::wxyz; +END; +$body$ +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION public.x_vlt_wxyz_child ( +) +RETURNS wxyz_child VOLATILE AS +$body$ +BEGIN + RAISE NOTICE 'v wxyz_child'; + RETURN '(1, {2}, TRUE, 3)'::wxyz_child; +END; +$body$ +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION public.x_vlt_wxyz_child2 ( +) +RETURNS wxyz_child2 VOLATILE AS +$body$ +BEGIN + RAISE NOTICE 'v wxyz_child2'; + RETURN '(1, {2}, TRUE, 3, 4, 5)'::wxyz_child2; +END; +$body$ +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION public.x_vlt_oid ( +) +RETURNS oid VOLATILE AS +$body$ +BEGIN + RAISE NOTICE 'v oid'; + RETURN 1; +END; +$body$ +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION public.x_vlt_text_integer ( +) +RETURNS text VOLATILE AS +$body$ +BEGIN + RAISE NOTICE 'v text integer'; + RETURN 1::text; +END; +$body$ +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION public.x_vlt_text_my_integer ( +) +RETURNS text VOLATILE AS +$body$ +BEGIN + RAISE NOTICE 'v text my_integer'; + RETURN '(1)'::text; +END; +$body$ +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION public.x_vlt_text_xml ( +) +RETURNS text VOLATILE AS +$body$ +BEGIN + RAISE NOTICE 'v text xml'; + RETURN 'Manual'::text; +END; +$body$ +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION public.x_vlt_text_xml_content ( +) +RETURNS text VOLATILE AS +$body$ +BEGIN + RAISE NOTICE 'v text xml content'; + RETURN 'abcbarfoo'::text; +END; +$body$ +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION public.x_vlt_text_xml_instruction_content ( +) +RETURNS text VOLATILE AS +$body$ +BEGIN + RAISE NOTICE 'v text xml instruction content'; + RETURN 'echo "hello world";'::text; +END; +$body$ +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION public.x_vlt_xml ( +) +RETURNS xml VOLATILE AS +$body$ +BEGIN + RAISE NOTICE 'v xml'; + RETURN 'foo'::xml; +END; +$body$ +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION public.x_vlt_xml_content ( +) +RETURNS xml VOLATILE AS +$body$ +BEGIN + RAISE NOTICE 'v xml content'; + RETURN 'abcbarfoo'::xml; +END; +$body$ +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION public.x_vlt2 ( + integer +) +RETURNS integer VOLATILE AS +$body$ +BEGIN + RAISE NOTICE 'v2'; + 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.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.cast_integer_as_my_integer_vlt ( + integer +) +RETURNS my_integer VOLATILE AS +$body$ +BEGIN + RAISE NOTICE 'cast integer as my_integer volatile'; + RETURN ROW($1)::my_integer; +END; +$body$ +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION public.cast_my_integer_as_integer_vlt ( + my_integer +) +RETURNS integer VOLATILE AS +$body$ +BEGIN + RAISE NOTICE 'cast my_integer as integer volatile'; + RETURN $1.value; +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_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.x_stl_array_integer ( +) +RETURNS int[] STABLE AS +$body$ +BEGIN + RAISE NOTICE 's array_integer'; + RETURN '{2, 3}'::integer[]; +END; +$body$ +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION public.x_stl_wxyz ( +) +RETURNS wxyz STABLE AS +$body$ +BEGIN + RAISE NOTICE 's wxyz'; + RETURN '(1, {2}, TRUE, 3)'::wxyz; +END; +$body$ +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION public.x_stl_wxyz_child ( +) +RETURNS wxyz_child STABLE AS +$body$ +BEGIN + RAISE NOTICE 's wxyz_child'; + RETURN '(1, {2}, TRUE, 3)'::wxyz_child; +END; +$body$ +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION public.x_stl_wxyz_child2 ( +) +RETURNS wxyz_child2 STABLE AS +$body$ +BEGIN + RAISE NOTICE 's wxyz_child2'; + RETURN '(1, {2}, TRUE, 3, 4, 5)'::wxyz_child2; +END; +$body$ +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION public.x_stl_oid ( +) +RETURNS oid STABLE AS +$body$ +BEGIN + RAISE NOTICE 's oid'; + RETURN 1; +END; +$body$ +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION public.x_stl_text_integer ( +) +RETURNS text STABLE AS +$body$ +BEGIN + RAISE NOTICE 's text integer'; + RETURN 1::text; +END; +$body$ +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION public.x_stl_text_my_integer ( +) +RETURNS text STABLE AS +$body$ +BEGIN + RAISE NOTICE 's text my_integer'; + RETURN '(1)'::text; +END; +$body$ +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION public.x_stl_text_xml ( +) +RETURNS text STABLE AS +$body$ +BEGIN + RAISE NOTICE 's text xml'; + RETURN 'Manual'::text; +END; +$body$ +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION public.x_stl_text_xml_content ( +) +RETURNS text STABLE AS +$body$ +BEGIN + RAISE NOTICE 's xml content'; + RETURN 'abcbarfoo'::text; +END; +$body$ +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION public.x_stl_text_xml_instruction_content ( +) +RETURNS text STABLE AS +$body$ +BEGIN + RAISE NOTICE 's text xml instruction content'; + RETURN 'echo "hello world";'::text; +END; +$body$ +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION public.x_stl_xml ( +) +RETURNS xml STABLE AS +$body$ +BEGIN + RAISE NOTICE 's xml'; + RETURN 'foo'::xml; +END; +$body$ +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION public.x_stl_xml_content ( +) +RETURNS xml STABLE AS +$body$ +BEGIN + RAISE NOTICE 's xml content'; + RETURN 'abcbarfoo'::xml; +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.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.x_stl2_array_integer ( + integer[] +) +RETURNS integer[] STABLE AS +$body$ +BEGIN + RAISE NOTICE 's2 array_integer'; + RETURN $1; +END; +$body$ +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION public.x_stl2_array_oid ( + oid[] +) +RETURNS oid[] STABLE AS +$body$ +BEGIN + RAISE NOTICE 's2 array_oid'; + RETURN $1; +END; +$body$ +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION public.x_stl2_wxyz ( + wxyz +) +RETURNS wxyz STABLE AS +$body$ +BEGIN + RAISE NOTICE 's2 wxyz'; + RETURN $1; +END; +$body$ +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION public.x_stl2_composite_type ( + composite_type +) +RETURNS composite_type STABLE AS +$body$ +BEGIN + RAISE NOTICE 's2 composite_type'; + RETURN $1; +END; +$body$ +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION public.x_stl2_my_integer ( + my_integer +) +RETURNS my_integer STABLE AS +$body$ +BEGIN + RAISE NOTICE 's2 my_integer'; + RETURN $1; +END; +$body$ +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION public.x_stl2_no_columns ( + no_columns +) +RETURNS no_columns STABLE AS +$body$ +BEGIN + RAISE NOTICE 's2 no_columns'; + RETURN $1; +END; +$body$ +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION public.x_stl2_name ( + name +) +RETURNS name STABLE AS +$body$ +BEGIN + RAISE NOTICE 's2 name'; + RETURN $1; +END; +$body$ +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION public.x_stl2_xml ( + xml +) +RETURNS xml STABLE AS +$body$ +BEGIN + RAISE NOTICE 's2 xml'; + RETURN $1; +END; +$body$ +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION public.x_stl2_text ( + text +) +RETURNS text STABLE AS +$body$ +BEGIN + RAISE NOTICE 's2 text'; + 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.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.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.cast_integer_as_my_integer_stl ( + integer +) +RETURNS my_integer STABLE AS +$body$ +BEGIN + RAISE NOTICE 'cast integer as my_integer stable'; + RETURN ROW($1)::my_integer; +END; +$body$ +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION public.cast_my_integer_as_integer_stl ( + my_integer +) +RETURNS integer STABLE AS +$body$ +BEGIN + RAISE NOTICE 'cast my_integer as integer stable'; + RETURN $1.value; +END; +$body$ +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION public.stable_max( +) +RETURNS integer STABLE AS +$body$ +BEGIN + RETURN (SELECT max(x) from x); +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.x_imm2_my_integer ( + my_integer +) +RETURNS my_integer IMMUTABLE AS +$body$ +BEGIN + RAISE NOTICE 'i2 my_integer'; + 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_booleans_imm ( + boolean, + boolean +) +RETURNS boolean IMMUTABLE AS +$body$ +BEGIN + RAISE NOTICE 'equal booleans 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_booleans_imm, + LEFTARG = boolean, + RIGHTARG = boolean +); + +-- Create domains for testing + +CREATE DOMAIN my_integer_no_check AS integer; +CREATE DOMAIN my_integer_not_null AS integer; +CREATE DOMAIN my_integer_vlt_check AS integer CHECK (VALUE === 1); +CREATE DOMAIN my_integer_stl_check AS integer CHECK (VALUE ==== 1); +CREATE DOMAIN my_integer_imm_check AS integer CHECK (VALUE ===== 1); + +CREATE OR REPLACE FUNCTION public.x_stl2_my_integer_no_check ( + my_integer_no_check +) +RETURNS my_integer_no_check STABLE AS +$body$ +BEGIN + RAISE NOTICE 's2 my_integer_no_check'; + RETURN $1; +END; +$body$ +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION public.x_stl2_my_integer_not_null ( + my_integer_not_null +) +RETURNS my_integer_not_null STABLE AS +$body$ +BEGIN + RAISE NOTICE 's2 my_integer_not_null'; + RETURN $1; +END; +$body$ +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION public.x_stl2_my_integer_vlt_check ( + my_integer_vlt_check +) +RETURNS my_integer_vlt_check STABLE AS +$body$ +BEGIN + RAISE NOTICE 's2 my_integer_vlt_check'; + RETURN $1; +END; +$body$ +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION public.x_stl2_my_integer_stl_check ( + my_integer_stl_check +) +RETURNS my_integer_stl_check STABLE AS +$body$ +BEGIN + RAISE NOTICE 's2 my_integer_stl_check'; + RETURN $1; +END; +$body$ +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION public.x_stl2_my_integer_imm_check ( + my_integer_imm_check +) +RETURNS my_integer_imm_check STABLE AS +$body$ +BEGIN + RAISE NOTICE 's2 my_integer_imm_check'; + RETURN $1; +END; +$body$ +LANGUAGE 'plpgsql'; + +-- Functions testing + +-- Simple functions testing +SELECT x_vlt() FROM x; -- should not be precalculated +SELECT x_stl() FROM x; + +-- WHERE clause testing +SELECT x_vlt() FROM x WHERE x_vlt() < x; -- should not be precalculated +SELECT x_stl() FROM x WHERE x_stl() < x; + +-- JOIN/ON clause testing +SELECT * FROM x JOIN generate_series(1, 2) y ON x_vlt() < x; -- should not be precalculated +SELECT * FROM x JOIN generate_series(1, 2) y ON x_stl() < x; + +-- Functions with constant arguments testing +SELECT x_vlt2(1) FROM x; -- should not be precalculated +SELECT x_stl2(1) FROM x; + +-- Nested functions testing +SELECT x_stl2(x_vlt()) FROM x; -- should not be precalculated +SELECT x_imm2(x_vlt()) FROM x; -- should not be precalculated + +SELECT x_stl2(x_stl()) FROM x; +SELECT x_imm2(x_stl()) FROM x; + +-- Strict functions testing +SELECT x_stl2_strict(x_vlt()) FROM x; -- should not be precalculated +SELECT x_imm2_strict(x_vlt()) FROM x; -- should not be precalculated + +SELECT x_stl2_strict(x_stl2_strict(1)) FROM x; +SELECT x_imm2_strict(x_stl2_strict(1)) FROM x; + +-- Strict functions with null arguments testing +SELECT x_stl2_strict(x_stl2(NULL)) FROM x; +SELECT x_imm2_strict(x_stl2(NULL)) FROM x; + +-- Operators testing + +SELECT 1 === 2 FROM x; -- should not be precalculated +SELECT 1 ==== 2 FROM x; + +-- Strict operators testing +SELECT x_stl2_boolean(NULL) ==== TRUE FROM x; +SELECT x_stl2_boolean(NULL) ===== TRUE FROM x; + +-- Mixed functions and operators testing +SELECT x_stl2_boolean(1 === 2) FROM x; -- should not be precalculated +SELECT x_stl2_boolean(1 ==== 2) FROM x; + +SELECT x_vlt() ==== 1 FROM x; -- should not be precalculated +SELECT x_stl() ==== 1 FROM x; + +-- IS (NOT) 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 +); + +SELECT '(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer FROM x; -- should not be precalculated +SELECT '(1)'::my_integer IS NOT DISTINCT FROM '(2)'::my_integer FROM x; -- should not be precalculated + +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 x; +SELECT '(1)'::my_integer IS NOT DISTINCT FROM '(2)'::my_integer FROM x; + +-- IS (NOT) DISTINCT FROM expressions with null arguments testing +SELECT x_stl2_boolean(x_stl2(NULL) IS DISTINCT FROM 1) FROM x; +SELECT x_stl2_boolean(x_stl2(NULL) IS NOT DISTINCT FROM 1) FROM x; + +SELECT x_stl2_boolean(x_stl2(NULL) IS DISTINCT FROM x_stl2(NULL)) FROM x; +SELECT x_stl2_boolean(x_stl2(NULL) IS NOT DISTINCT FROM x_stl2(NULL)) FROM x; + +-- Mixed functions and IS (NOT) 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( + ('(1)'::my_integer IS DISTINCT FROM '(1)'::my_integer), + ('(1)'::my_integer IS NOT DISTINCT FROM '(1)'::my_integer) +) +FROM x; + +DROP OPERATOR = (my_integer, my_integer); +CREATE OPERATOR = ( + PROCEDURE = equal_my_integer_stl, + LEFTARG = my_integer, + RIGHTARG = my_integer +); + +SELECT equal_booleans_stl_strict( + ('(1)'::my_integer IS DISTINCT FROM '(1)'::my_integer), + ('(1)'::my_integer IS NOT DISTINCT FROM '(1)'::my_integer) +) +FROM x; + +SELECT (x_vlt_my_integer() IS DISTINCT FROM '(1)'::my_integer) FROM x; -- should not be precalculated +SELECT (x_vlt_my_integer() IS NOT DISTINCT FROM '(1)'::my_integer) FROM x; -- should not be precalculated + +SELECT (x_stl_my_integer() IS DISTINCT FROM '(1)'::my_integer) FROM x; +SELECT (x_stl_my_integer() IS NOT DISTINCT FROM '(1)'::my_integer) FROM x; + +-- NULLIF expressions testing + +DROP OPERATOR = (my_integer, my_integer); +CREATE OPERATOR = ( + PROCEDURE = equal_my_integer_vlt, + LEFTARG = my_integer, + RIGHTARG = my_integer +); + +SELECT NULLIF('(1)'::my_integer, '(2)'::my_integer) FROM x; -- should not be precalculated + +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 x; + +DROP OPERATOR = (my_integer, my_integer); +CREATE OPERATOR = ( + PROCEDURE = equal_my_integer_imm, + LEFTARG = my_integer, + RIGHTARG = my_integer +); + +SELECT NULLIF('(1)'::my_integer, '(2)'::my_integer) FROM x; + +-- NULLIF expressions with null arguments testing +SELECT x_stl2(NULLIF(1, NULL)) FROM x; +SELECT x_stl2(NULLIF(NULL::integer, NULL)) FROM x; + +-- Mixed functions 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 equal_my_integer_stl( + NULLIF('(1)'::my_integer, '(2)'::my_integer), + NULLIF('(2)'::my_integer, '(2)'::my_integer) +) +FROM x; + +DROP OPERATOR = (my_integer, my_integer); +CREATE OPERATOR = ( + PROCEDURE = equal_my_integer_stl, + LEFTARG = my_integer, + RIGHTARG = my_integer +); + +SELECT equal_my_integer_stl( + NULLIF('(1)'::my_integer, '(2)'::my_integer), + NULLIF('(2)'::my_integer, '(2)'::my_integer) +) +FROM x; + +SELECT NULLIF(x_vlt_my_integer(), '(2)'::my_integer) FROM x; -- should not be precalculated +SELECT NULLIF(x_stl_my_integer(), '(2)'::my_integer) FROM x; + +-- "scalar op ANY/ALL (array)" / "scalar IN (2 or more values)" expressions +-- testing + +SELECT 1 === ANY ('{2, 3}') FROM x; -- should not be precalculated +SELECT 1 === ALL ('{2, 3}') FROM x; -- should not be precalculated + +DROP OPERATOR = (my_integer, my_integer); +CREATE OPERATOR = ( + PROCEDURE = equal_my_integer_vlt, + LEFTARG = my_integer, + RIGHTARG = my_integer +); + +SELECT '(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer) FROM x; -- should not be precalculated + +SELECT 1 ==== ANY ('{2, 3}') FROM x; +SELECT 1 ==== ALL ('{2, 3}') FROM 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 x; + +SELECT 1 ===== ANY ('{2, 3}') FROM x; +SELECT 1 ===== ALL ('{2, 3}') FROM x; + +DROP OPERATOR = (my_integer, my_integer); +CREATE OPERATOR = ( + PROCEDURE = equal_my_integer_imm, + LEFTARG = my_integer, + RIGHTARG = my_integer +); + +SELECT '(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer) FROM x; + +-- "scalar op ANY/ALL (array)" / "scalar IN (2 or more values)" expressions with +-- null arguments testing +SELECT 1 ==== ANY ('{2, NULL}') FROM x; +SELECT x_stl2_boolean(1 ==== ANY (NULL)) FROM x; +SELECT NULL ==== ANY ('{2, 3}'::integer[]) FROM x; +SELECT NULL ==== ANY ('{2, NULL}'::integer[]) FROM x; +SELECT x_stl2_boolean(NULL::integer ==== ANY (NULL)) FROM x; + +SELECT 1 ==== ALL ('{2, NULL}') FROM x; +SELECT x_stl2_boolean(1 ==== ALL (NULL)) FROM x; +SELECT NULL ==== ALL ('{2, 3}'::integer[]) FROM x; +SELECT NULL ==== ALL ('{2, NULL}'::integer[]) FROM x; +SELECT x_stl2_boolean(NULL::integer ==== ALL (NULL)) FROM x; + +SELECT x_stl2_boolean(1 IN (2, NULL)) FROM x; +SELECT x_stl2_boolean(NULL IN (2, 3)) FROM x; +SELECT x_stl2_boolean(NULL IN (2, NULL)) FROM x; + +-- Mixed functions and "scalar op ANY/ALL (array)" / "scalar IN (2 or more +-- values)" expressions testing +SELECT x_stl2_boolean(1 === ANY ('{2, 3}')) FROM x; -- should not be precalculated +SELECT x_stl2_boolean(1 === ALL ('{2, 3}')) FROM 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 x_stl2_boolean( + '(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer) +) +FROM x; + +SELECT x_stl2_boolean(1 ==== ANY ('{2, 3}')) FROM x; +SELECT x_stl2_boolean(1 ==== ALL ('{2, 3}')) FROM 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) +) +FROM x; + +SELECT x_vlt() ==== ANY ('{2, 3}') FROM x; -- should not be precalculated +SELECT x_vlt() ==== ALL ('{2, 3}') FROM x; -- should not be precalculated + +SELECT 1 ==== ANY (x_vlt_array_integer()) FROM x; -- should not be precalculated +SELECT 1 ==== ALL (x_vlt_array_integer()) FROM x; -- should not be precalculated + +SELECT x_vlt_my_integer() IN ('(2)'::my_integer, '(3)'::my_integer) FROM x; -- should not be precalculated + +SELECT x_stl() ==== ANY ('{2, 3}') FROM x; +SELECT x_stl() ==== ALL ('{2, 3}') FROM x; + +SELECT 1 ==== ANY (x_stl_array_integer()) FROM x; +SELECT 1 ==== ALL (x_stl_array_integer()) FROM x; + +SELECT x_stl_my_integer() IN ('(2)'::my_integer, '(3)'::my_integer) FROM x; + +-- Boolean expressions testing + +SELECT x_stl2_boolean(x_vlt_boolean() AND x_stl2_boolean(TRUE)) FROM x; -- should not be precalculated +SELECT x_stl2_boolean(x_vlt_boolean() OR x_stl2_boolean(TRUE)) FROM x; -- should not be precalculated +SELECT x_stl2_boolean(NOT x_vlt_boolean()) FROM x; -- should not be precalculated + +SELECT x_stl2_boolean(x_stl2_boolean(TRUE) AND x_stl2_boolean(TRUE)) FROM x; +SELECT x_stl2_boolean(x_stl2_boolean(TRUE) OR x_stl2_boolean(TRUE)) FROM x; +SELECT x_stl2_boolean(NOT x_stl2_boolean(TRUE)) FROM x; + +-- ARRAY[] expressions testing + +SELECT x_stl2_array_integer(ARRAY[x_vlt(), 2]) FROM x; -- should not be precalculated +SELECT x_stl2_array_integer(ARRAY[x_stl(), 2]) FROM x; + +-- Multidimensional ARRAY[] expressions testing + +SELECT x_stl2_array_integer(ARRAY[[x_vlt(), 2], [3, 4]]) FROM x; -- should not be precalculated +SELECT x_stl2_array_integer(ARRAY[[x_stl(), 2], [3, 4]]) FROM x; + +-- Array subscripting operations testing + +SELECT x_stl2(('{1, 2}'::integer[])[1]) FROM x; +SELECT x_stl2_array_integer(('{1, 2}'::integer[])[:]) FROM x; + +-- Mixed functions and array subscripting operations testing +SELECT x_stl2((x_vlt_array_integer())[x_vlt()]) FROM x; -- should not be precalculated +SELECT x_stl2((x_vlt_array_integer())[1]) FROM x; -- should not be precalculated +SELECT x_stl2_array_integer((x_vlt_array_integer())[:]) FROM x; -- should not be precalculated +SELECT x_stl2(('{1, 2}'::integer[])[x_vlt()]) FROM x; -- should not be precalculated + +SELECT x_stl2((x_stl_array_integer())[x_stl()]) FROM x; +SELECT x_stl2((x_stl_array_integer())[1]) FROM x; +SELECT x_stl2_array_integer((x_stl_array_integer())[:]) FROM x; +SELECT x_stl2(('{1, 2}'::integer[])[x_stl()]) FROM x; + +-- FieldSelect expressions testing + +SELECT x_stl2(('(1, {2}, TRUE, 3)'::wxyz).w) FROM x; +SELECT x_stl2(('(1)'::my_integer).value) FROM x; + +-- Mixed functions and FieldSelect expressions testing +SELECT x_stl2((x_vlt_wxyz()).w) FROM x; -- should not be precalculated +SELECT x_stl2((x_vlt_my_integer()).value) FROM x; -- should not be precalculated + +SELECT x_stl2((x_stl_wxyz()).w) FROM x; +SELECT x_stl2((x_stl_my_integer()).value) FROM x; + +-- ROW() expressions testing + +SELECT x_stl2_wxyz((1, '{2}', TRUE, 3)) FROM x; +SELECT x_stl2_wxyz(ROW(1, '{2}', TRUE, 3)) FROM x; +SELECT x_stl2_wxyz((1, '{2}', TRUE, 3)::wxyz) FROM x; + +SELECT x_stl2_composite_type((1, '{2}', TRUE)) FROM x; +SELECT x_stl2_composite_type(ROW(1, '{2}', TRUE)) FROM x; +SELECT x_stl2_composite_type((1, '{2}', TRUE)::composite_type) FROM x; + +-- Mixed functions and ROW() expressions testing +SELECT x_stl2_wxyz((x_vlt(), '{2}', TRUE, 3)) FROM x; -- should not be precalculated +SELECT x_stl2_wxyz((x_stl(), '{2}', TRUE, 3)) FROM x; + +-- RelabelType expressions testing + +SELECT x_stl2(x_vlt_oid()::integer) FROM x; -- should not be precalculated +SELECT x_stl2(x_stl_oid()::integer) FROM x; + +-- CoerceViaIO expressions testing + +SELECT x_stl2_my_integer('(1)'::text::my_integer) FROM x; + +SELECT x_stl2(x_vlt_text_integer()::integer) FROM x; -- should not be precalculated +SELECT x_stl2(x_stl_text_integer()::integer) FROM x; + +-- Mixed functions and CoerceViaIO expressions testing +SELECT x_stl2_my_integer(x_vlt_text_my_integer()::my_integer) FROM x; -- should not be precalculated +SELECT x_stl2_my_integer(x_stl_text_my_integer()::my_integer) FROM x; + +-- ArrayCoerce expressions testing + +-- Binary-coercible types: +SELECT x_stl2_array_oid(x_vlt_array_integer()::oid[]) FROM x; -- should not be precalculated +SELECT x_stl2_array_oid(x_stl_array_integer()::oid[]) FROM x; + +-- Not binary-coercible types: +-- create cast here because we will drop and reuse it several times +CREATE CAST (integer AS my_integer) + WITH FUNCTION cast_integer_as_my_integer_vlt; + +SELECT '{1, 2}'::integer[]::my_integer[] FROM x; -- should not be precalculated + +DROP CAST (integer AS my_integer); +CREATE CAST (integer AS my_integer) + WITH FUNCTION cast_integer_as_my_integer_stl; + +SELECT '{1, 2}'::integer[]::my_integer[] FROM x; + +-- Mixed functions and ArrayCoerce expressions testing +-- Not binary-coercible types: +-- create cast here because we will drop and reuse it several times +CREATE CAST (my_integer AS integer) + WITH FUNCTION cast_my_integer_as_integer_vlt; + +SELECT x_stl2_array_integer('{(1), (2)}'::my_integer[]::integer[]) FROM x; -- should not be precalculated + +DROP CAST (my_integer AS integer); +CREATE CAST (my_integer AS integer) + WITH FUNCTION cast_my_integer_as_integer_stl; + +SELECT x_stl2_array_integer('{(1), (2)}'::my_integer[]::integer[]) FROM x; + +DROP CAST (integer AS my_integer); +CREATE CAST (integer AS my_integer) + WITH FUNCTION cast_integer_as_my_integer_stl; + +SELECT x_vlt_array_integer()::my_integer[] FROM x; -- should not be precalculated +SELECT x_stl_array_integer()::my_integer[] FROM x; + +-- ConvertRowtypeExpr testing + +SELECT x_stl2_wxyz('(1, {2}, TRUE, 3)'::wxyz_child::wxyz) FROM x; +SELECT x_stl2_wxyz('(1, {2}, TRUE, 3, 4, 5)'::wxyz_child2::wxyz) FROM x; + +SELECT x_stl2_no_columns('()'::no_columns_child::no_columns) FROM x; +SELECT x_stl2_no_columns('(1, 2)'::no_columns_child2::no_columns) FROM x; + +-- Mixed functions and ConvertRowtypeExpr testing +SELECT x_stl2_wxyz(x_vlt_wxyz_child()::wxyz_child::wxyz) FROM x; -- should not be precalculated +SELECT x_stl2_wxyz(x_vlt_wxyz_child2()::wxyz_child2::wxyz) FROM x; -- should not be precalculated + +SELECT x_stl2_wxyz(x_stl_wxyz_child()::wxyz_child::wxyz) FROM x; +SELECT x_stl2_wxyz(x_stl_wxyz_child2()::wxyz_child2::wxyz) FROM x; + +-- CASE expressions testing + +SELECT x_stl2(CASE WHEN x_vlt_boolean() THEN x_vlt() ELSE x_vlt() END) FROM x; -- should not be precalculated +SELECT x_stl2(CASE x_vlt() WHEN x_vlt() THEN x_vlt() ELSE x_vlt() END) FROM x; -- should not be precalculated + +SELECT x_stl2(CASE WHEN x_stl2_boolean(TRUE) THEN x_stl() ELSE x_stl() END) +FROM x; + +SELECT x_stl2(CASE x_stl() WHEN x_stl() THEN x_stl() ELSE x_stl() END) FROM x; + +-- RowCompareExpr testing + +SELECT x_stl2_boolean((1, 2) < (1, 3)) FROM x; + +-- Mixed functions and RowCompareExpr testing +SELECT x_stl2_boolean((x_vlt(), 2) < (1, 3)) FROM x; -- should not be precalculated +SELECT x_stl2_boolean((x_stl(), 2) < (1, 3)) FROM x; + +-- COALESCE expressions testing + +SELECT x_stl2(COALESCE(NULL, x_vlt2(NULL), 2)) FROM x; -- should not be precalculated +SELECT x_stl2(COALESCE(NULL, x_stl2(NULL), 2)) FROM x; + +-- GREATEST and LEAST functions testing + +SELECT x_stl2(GREATEST(2, 1, 3)) FROM x; +SELECT x_stl2(LEAST(2, 1, 3)) FROM x; + +-- Mixed functions and GREATEST and LEAST functions testing +SELECT x_stl2(GREATEST(2, x_vlt(), 3)) FROM x; -- should not be precalculated +SELECT x_stl2(LEAST(2, x_vlt(), 3)) FROM x; -- should not be precalculated + +SELECT x_stl2(GREATEST(2, x_stl(), 3)) FROM x; +SELECT x_stl2(LEAST(2, x_stl(), 3)) FROM x; + +-- SQLValueFunction testing + +CREATE ROLE regress_testrol2 SUPERUSER; +CREATE ROLE regress_testrol1 SUPERUSER LOGIN IN ROLE regress_testrol2; + +\c - +SET SESSION AUTHORIZATION regress_testrol1; +SET ROLE regress_testrol2; + +SELECT x_stl2_name(current_role) FROM x; +SELECT x_stl2_name(current_user) FROM x; +SELECT x_stl2_name(user) FROM x; +SELECT x_stl2_name(session_user) FROM x; +SELECT x_stl2_name(current_catalog) FROM x; +SELECT x_stl2_name(current_schema) FROM x; + +\c +DROP ROLE regress_testrol1, regress_testrol2; + +-- Xml expressions testing + +SELECT x_stl2_xml(XMLCONCAT('', 'foo')) FROM x; + +SELECT x_stl2_xml( + XMLELEMENT(name foo, xmlattributes('bar' as bar), 'cont', 'ent') +) +FROM x; + +SELECT x_stl2_xml(XMLFOREST('abc' AS foo, 123 AS bar)) FROM x; + +SELECT x_stl2_xml(XMLPARSE( + DOCUMENT 'Manual' +)) +FROM x; + +SELECT x_stl2_xml(XMLPARSE(CONTENT 'abcbarfoo')) FROM x; + +SELECT x_stl2_xml(XMLPI(name php, 'echo "hello world";')) FROM x; + +SELECT x_stl2_xml(XMLROOT( + 'abc', + version '1.0', + standalone yes +)) +FROM x; + +SELECT x_stl2_text(XMLSERIALIZE( + DOCUMENT 'Manual' AS text +)) +FROM x; + +SELECT x_stl2_text(XMLSERIALIZE( + CONTENT 'abcbarfoo' AS text +)) +FROM x; + +SELECT x_stl2_boolean('abcbarfoo' IS DOCUMENT) FROM x; + +-- Mixed functions and Xml expressions testing +-- should not be precalculated +SELECT x_stl2_xml(XMLCONCAT('', x_vlt_xml())) FROM x; + +-- should not be precalculated +SELECT x_stl2_xml( + XMLELEMENT(name foo, xmlattributes('bar' as bar), x_vlt_xml()) +) +FROM x; + +-- should not be precalculated +SELECT x_stl2_xml(XMLFOREST('abc' AS foo, x_vlt_xml() AS bar)) FROM x; + +-- should not be precalculated +SELECT x_stl2_xml(XMLPARSE(DOCUMENT x_vlt_text_xml())) FROM x; + +-- should not be precalculated +SELECT x_stl2_xml(XMLPARSE(CONTENT x_vlt_text_xml_content())) FROM x; + +-- should not be precalculated +SELECT x_stl2_xml(XMLPI(name php, x_vlt_text_xml_instruction_content())) FROM x; + +-- should not be precalculated +SELECT x_stl2_xml(XMLROOT(x_vlt_xml(), version '1.0', standalone yes)) FROM x; + +-- should not be precalculated +SELECT x_stl2_text(XMLSERIALIZE(DOCUMENT x_vlt_xml() AS text)) FROM x; + +-- should not be precalculated +SELECT x_stl2_text(XMLSERIALIZE(CONTENT x_vlt_xml_content() AS text)) FROM x; + +-- should not be precalculated +SELECT x_stl2_boolean(x_vlt_xml_content() IS DOCUMENT) FROM x; + +SELECT x_stl2_xml(XMLCONCAT('', x_stl_xml())) FROM x; + +SELECT x_stl2_xml( + XMLELEMENT(name foo, xmlattributes('bar' as bar), x_stl_xml()) +) +FROM x; + +SELECT x_stl2_xml(XMLFOREST('abc' AS foo, x_stl_xml() AS bar)) FROM x; + +SELECT x_stl2_xml(XMLPARSE(DOCUMENT x_stl_text_xml())) FROM x; + +SELECT x_stl2_xml(XMLPARSE(CONTENT x_stl_text_xml_content())) FROM x; + +SELECT x_stl2_xml(XMLPI(name php, x_stl_text_xml_instruction_content())) FROM x; + +SELECT x_stl2_xml(XMLROOT(x_stl_xml(), version '1.0', standalone yes)) FROM x; + +SELECT x_stl2_text(XMLSERIALIZE(DOCUMENT x_stl_xml() AS text)) FROM x; + +SELECT x_stl2_text(XMLSERIALIZE(CONTENT x_stl_xml_content() AS text)) FROM x; + +SELECT x_stl2_boolean(x_stl_xml_content() IS DOCUMENT) FROM x; + +-- NullTest expressions testing + +SELECT x_stl2_boolean(x_vlt() IS NULL) FROM x; -- should not be precalculated +SELECT x_stl2_boolean(x_vlt() IS NOT NULL) FROM x; -- should not be precalculated + +SELECT x_stl2_boolean(x_vlt_wxyz() IS NULL) FROM x; -- should not be precalculated +SELECT x_stl2_boolean(x_vlt_wxyz() IS NOT NULL) FROM x; -- should not be precalculated + +SELECT x_stl2_boolean(x_stl() IS NULL) FROM x; +SELECT x_stl2_boolean(x_stl() IS NOT NULL) FROM x; + +SELECT x_stl2_boolean(x_stl_wxyz() IS NULL) FROM x; +SELECT x_stl2_boolean(x_stl_wxyz() IS NOT NULL) FROM x; + +-- BooleanTest expressions testing + +SELECT x_stl2_boolean(x_vlt_boolean() IS TRUE) FROM x; -- should not be precalculated +SELECT x_stl2_boolean(x_vlt_boolean() IS NOT TRUE) FROM x; -- should not be precalculated +SELECT x_stl2_boolean(x_vlt_boolean() IS FALSE) FROM x; -- should not be precalculated +SELECT x_stl2_boolean(x_vlt_boolean() IS NOT FALSE) FROM x; -- should not be precalculated +SELECT x_stl2_boolean(x_vlt_boolean() IS UNKNOWN) FROM x; -- should not be precalculated +SELECT x_stl2_boolean(x_vlt_boolean() IS NOT UNKNOWN) FROM x; -- should not be precalculated + +SELECT x_stl2_boolean(x_stl2_boolean(TRUE) IS TRUE) FROM x; +SELECT x_stl2_boolean(x_stl2_boolean(TRUE) IS NOT TRUE) FROM x; +SELECT x_stl2_boolean(x_stl2_boolean(TRUE) IS FALSE) FROM x; +SELECT x_stl2_boolean(x_stl2_boolean(TRUE) IS NOT FALSE) FROM x; +SELECT x_stl2_boolean(x_stl2_boolean(NULL) IS UNKNOWN) FROM x; +SELECT x_stl2_boolean(x_stl2_boolean(NULL) IS NOT UNKNOWN) FROM x; + +-- CoerceToDomain expressions testing + +SELECT x_stl2_my_integer_no_check(1::my_integer_no_check) FROM x; +SELECT x_stl2_my_integer_not_null(1::my_integer_not_null) FROM x; + +SELECT x_stl2_my_integer_vlt_check(1::my_integer_vlt_check) FROM x; -- should not be precalculated +SELECT x_stl2_my_integer_stl_check(1::my_integer_stl_check) FROM x; +SELECT x_stl2_my_integer_imm_check(1::my_integer_imm_check) FROM x; + +-- Mixed functions and CoerceToDomain expressions testing +SELECT x_stl2_my_integer_no_check(x_vlt()::my_integer_no_check) FROM x; -- should not be precalculated +SELECT x_stl2_my_integer_not_null(x_vlt()::my_integer_not_null) FROM x; -- should not be precalculated +SELECT x_stl2_my_integer_stl_check(x_vlt()::my_integer_stl_check) FROM x; -- should not be precalculated +SELECT x_stl2_my_integer_imm_check(x_vlt()::my_integer_imm_check) FROM x; -- should not be precalculated + +SELECT x_stl2_my_integer_no_check(x_stl()::my_integer_no_check) FROM x; +SELECT x_stl2_my_integer_not_null(x_stl()::my_integer_not_null) FROM x; +SELECT x_stl2_my_integer_stl_check(x_stl()::my_integer_stl_check) FROM x; +SELECT x_stl2_my_integer_imm_check(x_stl()::my_integer_imm_check) FROM x; + +-- Tracking functions testing + +SET track_functions TO 'all'; + +-- Simple functions testing +SELECT x_vlt() FROM x; -- should not be precalculated +SELECT x_stl() FROM x; + +-- WHERE clause testing +SELECT x_vlt() FROM x WHERE x_vlt() < x; -- should not be precalculated +SELECT x_stl() FROM x WHERE x_stl() < x; + +-- JOIN/ON clause testing +SELECT * FROM x JOIN generate_series(1, 2) y ON x_vlt() < x; -- should not be precalculated +SELECT * FROM x JOIN generate_series(1, 2) y ON x_stl() < x; + +-- Functions with constant arguments testing +SELECT x_vlt2(1) FROM x; -- should not be precalculated +SELECT x_stl2(1) FROM x; + +-- Nested functions testing +SELECT x_stl2(x_vlt()) FROM x; -- should not be precalculated +SELECT x_imm2(x_vlt()) FROM x; -- should not be precalculated + +SELECT x_stl2(x_stl()) FROM x; +SELECT x_imm2(x_stl()) FROM x; + +-- Strict functions testing +SELECT x_stl2_strict(x_vlt()) FROM x; -- should not be precalculated +SELECT x_imm2_strict(x_vlt()) FROM x; -- should not be precalculated + +SELECT x_stl2_strict(x_stl2_strict(1)) FROM x; +SELECT x_imm2_strict(x_stl2_strict(1)) FROM x; + +-- Strict functions with null arguments testing +SELECT x_stl2_strict(x_stl2(NULL)) FROM x; +SELECT x_imm2_strict(x_stl2(NULL)) FROM x; + +-- Operators testing +SELECT 1 === 2 FROM x; -- should not be precalculated +SELECT 1 ==== 2 FROM x; + +-- Strict operators testing +SELECT x_stl2_boolean(NULL) ==== TRUE FROM x; +SELECT x_stl2_boolean(NULL) ===== TRUE FROM x; + +-- Mixed functions and operators testing +SELECT x_stl2_boolean(1 === 2) FROM x; -- should not be precalculated +SELECT x_stl2_boolean(1 ==== 2) FROM x; + +SELECT x_vlt() ==== 1 FROM x; -- should not be precalculated +SELECT x_stl() ==== 1 FROM x; + +-- Mixed functions and IS (NOT) 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( + ('(1)'::my_integer IS DISTINCT FROM '(1)'::my_integer), + ('(1)'::my_integer IS NOT DISTINCT FROM '(1)'::my_integer) +) +FROM x; + +DROP OPERATOR = (my_integer, my_integer); +CREATE OPERATOR = ( + PROCEDURE = equal_my_integer_stl, + LEFTARG = my_integer, + RIGHTARG = my_integer +); + +SELECT equal_booleans_stl_strict( + ('(1)'::my_integer IS DISTINCT FROM '(1)'::my_integer), + ('(1)'::my_integer IS NOT DISTINCT FROM '(1)'::my_integer) +) +FROM x; + +SELECT (x_vlt_my_integer() IS DISTINCT FROM '(1)'::my_integer) FROM x; -- should not be precalculated +SELECT (x_vlt_my_integer() IS NOT DISTINCT FROM '(1)'::my_integer) FROM x; -- should not be precalculated + +SELECT (x_stl_my_integer() IS DISTINCT FROM '(1)'::my_integer) FROM x; +SELECT (x_stl_my_integer() IS NOT DISTINCT FROM '(1)'::my_integer) FROM x; + +-- Mixed functions 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 equal_my_integer_stl( + NULLIF('(1)'::my_integer, '(2)'::my_integer), + NULLIF('(2)'::my_integer, '(2)'::my_integer) +) +FROM x; + +DROP OPERATOR = (my_integer, my_integer); +CREATE OPERATOR = ( + PROCEDURE = equal_my_integer_stl, + LEFTARG = my_integer, + RIGHTARG = my_integer +); + +SELECT equal_my_integer_stl( + NULLIF('(1)'::my_integer, '(2)'::my_integer), + NULLIF('(2)'::my_integer, '(2)'::my_integer) +) +FROM x; + +SELECT NULLIF(x_vlt_my_integer(), '(2)'::my_integer) FROM x; -- should not be precalculated +SELECT NULLIF(x_stl_my_integer(), '(2)'::my_integer) FROM x; + +-- Mixed functions and "scalar op ANY/ALL (array)" / "scalar IN (2 or more +-- values)" expressions testing +SELECT x_stl2_boolean(1 === ANY ('{2, 3}')) FROM x; -- should not be precalculated +SELECT x_stl2_boolean(1 === ALL ('{2, 3}')) FROM 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 x_stl2_boolean( + '(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer) +) +FROM x; + +SELECT x_stl2_boolean(1 ==== ANY ('{2, 3}')) FROM x; +SELECT x_stl2_boolean(1 ==== ALL ('{2, 3}')) FROM 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) +) +FROM x; + +SELECT x_vlt() ==== ANY ('{2, 3}') FROM x; -- should not be precalculated +SELECT x_vlt() ==== ALL ('{2, 3}') FROM x; -- should not be precalculated + +SELECT 1 ==== ANY (x_vlt_array_integer()) FROM x; -- should not be precalculated +SELECT 1 ==== ALL (x_vlt_array_integer()) FROM x; -- should not be precalculated + +SELECT x_vlt_my_integer() IN ('(2)'::my_integer, '(3)'::my_integer) FROM x; -- should not be precalculated + +SELECT x_stl() ==== ANY ('{2, 3}') FROM x; +SELECT x_stl() ==== ALL ('{2, 3}') FROM x; + +SELECT 1 ==== ANY (x_stl_array_integer()) FROM x; +SELECT 1 ==== ALL (x_stl_array_integer()) FROM x; + +SELECT x_stl_my_integer() IN ('(2)'::my_integer, '(3)'::my_integer) FROM x; + +-- Mixed functions and boolean expressions testing +SELECT x_stl2_boolean(x_vlt_boolean() AND x_stl2_boolean(TRUE)) FROM x; -- should not be precalculated +SELECT x_stl2_boolean(x_vlt_boolean() OR x_stl2_boolean(TRUE)) FROM x; -- should not be precalculated +SELECT x_stl2_boolean(NOT x_vlt_boolean()) FROM x; -- should not be precalculated + +SELECT x_stl2_boolean(x_stl2_boolean(TRUE) AND x_stl2_boolean(TRUE)) FROM x; +SELECT x_stl2_boolean(x_stl2_boolean(TRUE) OR x_stl2_boolean(TRUE)) FROM x; +SELECT x_stl2_boolean(NOT x_stl2_boolean(TRUE)) FROM x; + +-- Mixed functions and ARRAY[] expressions testing +SELECT x_stl2_array_integer(ARRAY[x_vlt()]) FROM x; -- should not be precalculated +SELECT x_stl2_array_integer(ARRAY[x_stl()]) FROM x; + +-- Mixed functions and array subscripting operations testing +SELECT x_stl2((x_vlt_array_integer())[x_vlt()]) FROM x; -- should not be precalculated +SELECT x_stl2((x_vlt_array_integer())[1]) FROM x; -- should not be precalculated +SELECT x_stl2_array_integer((x_vlt_array_integer())[:]) FROM x; -- should not be precalculated +SELECT x_stl2(('{1, 2}'::integer[])[x_vlt()]) FROM x; -- should not be precalculated + +SELECT x_stl2((x_stl_array_integer())[x_stl()]) FROM x; +SELECT x_stl2((x_stl_array_integer())[1]) FROM x; +SELECT x_stl2_array_integer((x_stl_array_integer())[:]) FROM x; +SELECT x_stl2(('{1, 2}'::integer[])[x_stl()]) FROM x; + +-- Mixed functions and FieldSelect expressions testing +SELECT x_stl2((x_vlt_wxyz()).w) FROM x; -- should not be precalculated +SELECT x_stl2((x_vlt_my_integer()).value) FROM x; -- should not be precalculated + +SELECT x_stl2((x_stl_wxyz()).w) FROM x; +SELECT x_stl2((x_stl_my_integer()).value) FROM x; + +-- Mixed functions and ROW() expressions testing +SELECT x_stl2_wxyz((x_vlt(), '{2}', TRUE, 3)) FROM x; -- should not be precalculated +SELECT x_stl2_wxyz((x_stl(), '{2}', TRUE, 3)) FROM x; + +-- Mixed functions and RelabelType expressions testing +SELECT x_stl2(x_vlt_oid()::integer) FROM x; -- should not be precalculated +SELECT x_stl2(x_stl_oid()::integer) FROM x; + +-- Mixed functions and CoerceViaIO expressions testing +SELECT x_stl2(x_vlt_text_integer()::integer) FROM x; -- should not be precalculated +SELECT x_stl2(x_stl_text_integer()::integer) FROM x; + +SELECT x_stl2_my_integer(x_vlt_text_my_integer()::my_integer) FROM x; -- should not be precalculated +SELECT x_stl2_my_integer(x_stl_text_my_integer()::my_integer) FROM x; + +-- Mixed functions and ArrayCoerce expressions testing +-- Binary-coercible types: +SELECT x_stl2_array_oid(x_vlt_array_integer()::oid[]) FROM x; -- should not be precalculated +SELECT x_stl2_array_oid(x_stl_array_integer()::oid[]) FROM x; + +-- Not binary-coercible types: +DROP CAST (my_integer AS integer); +CREATE CAST (my_integer AS integer) + WITH FUNCTION cast_my_integer_as_integer_vlt; + +SELECT x_stl2_array_integer('{(1), (2)}'::my_integer[]::integer[]) FROM x; -- should not be precalculated + +DROP CAST (my_integer AS integer); +CREATE CAST (my_integer AS integer) + WITH FUNCTION cast_my_integer_as_integer_stl; + +SELECT x_stl2_array_integer('{(1), (2)}'::my_integer[]::integer[]) FROM x; + +DROP CAST (integer AS my_integer); +CREATE CAST (integer AS my_integer) + WITH FUNCTION cast_integer_as_my_integer_stl; + +SELECT x_vlt_array_integer()::my_integer[] FROM x; -- should not be precalculated +SELECT x_stl_array_integer()::my_integer[] FROM x; + +-- Mixed functions and ConvertRowtypeExpr testing +SELECT x_stl2_wxyz(x_vlt_wxyz_child()::wxyz_child::wxyz) FROM x; -- should not be precalculated +SELECT x_stl2_wxyz(x_vlt_wxyz_child2()::wxyz_child2::wxyz) FROM x; -- should not be precalculated + +SELECT x_stl2_wxyz(x_stl_wxyz_child()::wxyz_child::wxyz) FROM x; +SELECT x_stl2_wxyz(x_stl_wxyz_child2()::wxyz_child2::wxyz) FROM x; + +-- Mixed functions and CASE expressions testing +SELECT x_stl2(CASE WHEN x_vlt_boolean() THEN x_vlt() ELSE x_vlt() END) FROM x; -- should not be precalculated +SELECT x_stl2(CASE x_vlt() WHEN x_vlt() THEN x_vlt() ELSE x_vlt() END) FROM x; -- should not be precalculated + +SELECT x_stl2(CASE WHEN x_stl2_boolean(TRUE) THEN x_stl() ELSE x_stl() END) +FROM x; + +SELECT x_stl2(CASE x_stl() WHEN x_stl() THEN x_stl() ELSE x_stl() END) FROM x; + +-- Mixed functions and RowCompareExpr testing +SELECT x_stl2_boolean((x_vlt(), 2) < (1, 3)) FROM x; -- should not be precalculated +SELECT x_stl2_boolean((x_stl(), 2) < (1, 3)) FROM x; + +-- Mixed functions and COALESCE expressions testing +SELECT x_stl2(COALESCE(NULL, x_vlt2(NULL), 2)) FROM x; -- should not be precalculated +SELECT x_stl2(COALESCE(NULL, x_stl2(NULL), 2)) FROM x; + +-- Mixed functions and GREATEST and LEAST functions testing +SELECT x_stl2(GREATEST(2, x_vlt(), 3)) FROM x; -- should not be precalculated +SELECT x_stl2(LEAST(2, x_vlt(), 3)) FROM x; -- should not be precalculated + +SELECT x_stl2(GREATEST(2, x_stl(), 3)) FROM x; +SELECT x_stl2(LEAST(2, x_stl(), 3)) FROM x; + +-- Mixed functions and Xml expressions testing +-- should not be precalculated +SELECT x_stl2_xml(XMLCONCAT('', x_vlt_xml())) FROM x; + +-- should not be precalculated +SELECT x_stl2_xml( + XMLELEMENT(name foo, xmlattributes('bar' as bar), x_vlt_xml()) +) +FROM x; + +-- should not be precalculated +SELECT x_stl2_xml(XMLFOREST('abc' AS foo, x_vlt_xml() AS bar)) FROM x; + +-- should not be precalculated +SELECT x_stl2_xml(XMLPARSE(DOCUMENT x_vlt_text_xml())) FROM x; + +-- should not be precalculated +SELECT x_stl2_xml(XMLPARSE(CONTENT x_vlt_text_xml_content())) FROM x; + +-- should not be precalculated +SELECT x_stl2_xml(XMLPI(name php, x_vlt_text_xml_instruction_content())) FROM x; + +-- should not be precalculated +SELECT x_stl2_xml(XMLROOT(x_vlt_xml(), version '1.0', standalone yes)) FROM x; + +-- should not be precalculated +SELECT x_stl2_text(XMLSERIALIZE(DOCUMENT x_vlt_xml() AS text)) FROM x; + +-- should not be precalculated +SELECT x_stl2_text(XMLSERIALIZE(CONTENT x_vlt_xml_content() AS text)) FROM x; + +-- should not be precalculated +SELECT x_stl2_boolean(x_vlt_xml_content() IS DOCUMENT) FROM x; + +SELECT x_stl2_xml(XMLCONCAT('', x_stl_xml())) FROM x; + +SELECT x_stl2_xml( + XMLELEMENT(name foo, xmlattributes('bar' as bar), x_stl_xml()) +) +FROM x; + +SELECT x_stl2_xml(XMLFOREST('abc' AS foo, x_stl_xml() AS bar)) FROM x; + +SELECT x_stl2_xml(XMLPARSE(DOCUMENT x_stl_text_xml())) FROM x; + +SELECT x_stl2_xml(XMLPARSE(CONTENT x_stl_text_xml_content())) FROM x; + +SELECT x_stl2_xml(XMLPI(name php, x_stl_text_xml_instruction_content())) FROM x; + +SELECT x_stl2_xml(XMLROOT(x_stl_xml(), version '1.0', standalone yes)) FROM x; + +SELECT x_stl2_text(XMLSERIALIZE(DOCUMENT x_stl_xml() AS text)) FROM x; + +SELECT x_stl2_text(XMLSERIALIZE(CONTENT x_stl_xml_content() AS text)) FROM x; + +SELECT x_stl2_boolean(x_stl_xml_content() IS DOCUMENT) FROM x; + +-- Mixed functions and NullTest expressions testing +SELECT x_stl2_boolean(x_vlt() IS NULL) FROM x; -- should not be precalculated +SELECT x_stl2_boolean(x_vlt() IS NOT NULL) FROM x; -- should not be precalculated + +SELECT x_stl2_boolean(x_vlt_wxyz() IS NULL) FROM x; -- should not be precalculated +SELECT x_stl2_boolean(x_vlt_wxyz() IS NOT NULL) FROM x; -- should not be precalculated + +SELECT x_stl2_boolean(x_stl() IS NULL) FROM x; +SELECT x_stl2_boolean(x_stl() IS NOT NULL) FROM x; + +SELECT x_stl2_boolean(x_stl_wxyz() IS NULL) FROM x; +SELECT x_stl2_boolean(x_stl_wxyz() IS NOT NULL) FROM x; + +-- Mixed functions and BooleanTest expressions testing +SELECT x_stl2_boolean(x_vlt_boolean() IS TRUE) FROM x; -- should not be precalculated +SELECT x_stl2_boolean(x_vlt_boolean() IS NOT TRUE) FROM x; -- should not be precalculated +SELECT x_stl2_boolean(x_vlt_boolean() IS FALSE) FROM x; -- should not be precalculated +SELECT x_stl2_boolean(x_vlt_boolean() IS NOT FALSE) FROM x; -- should not be precalculated +SELECT x_stl2_boolean(x_vlt_boolean() IS UNKNOWN) FROM x; -- should not be precalculated +SELECT x_stl2_boolean(x_vlt_boolean() IS NOT UNKNOWN) FROM x; -- should not be precalculated + +SELECT x_stl2_boolean(x_stl2_boolean(TRUE) IS TRUE) FROM x; +SELECT x_stl2_boolean(x_stl2_boolean(TRUE) IS NOT TRUE) FROM x; +SELECT x_stl2_boolean(x_stl2_boolean(TRUE) IS FALSE) FROM x; +SELECT x_stl2_boolean(x_stl2_boolean(TRUE) IS NOT FALSE) FROM x; +SELECT x_stl2_boolean(x_stl2_boolean(NULL) IS UNKNOWN) FROM x; +SELECT x_stl2_boolean(x_stl2_boolean(NULL) IS NOT UNKNOWN) FROM x; + +-- Mixed functions and CoerceToDomain expressions testing +SELECT x_stl2_my_integer_no_check(x_vlt()::my_integer_no_check) FROM x; -- should not be precalculated +SELECT x_stl2_my_integer_not_null(x_vlt()::my_integer_not_null) FROM x; -- should not be precalculated +SELECT x_stl2_my_integer_stl_check(x_vlt()::my_integer_stl_check) FROM x; -- should not be precalculated +SELECT x_stl2_my_integer_imm_check(x_vlt()::my_integer_imm_check) FROM x; -- should not be precalculated + +SELECT x_stl2_my_integer_no_check(x_stl()::my_integer_no_check) FROM x; +SELECT x_stl2_my_integer_not_null(x_stl()::my_integer_not_null) FROM x; +SELECT x_stl2_my_integer_stl_check(x_stl()::my_integer_stl_check) FROM x; +SELECT x_stl2_my_integer_imm_check(x_stl()::my_integer_imm_check) FROM x; + +SET track_functions TO DEFAULT; + +-- ROW() expressions with dropped columns testing + +ALTER TABLE wxyz DROP COLUMN z; +VACUUM FULL; + +-- ROW() expressions testing +SELECT x_stl2_wxyz((1, '{2}', TRUE)) FROM x; +SELECT x_stl2_wxyz(ROW(1, '{2}', TRUE)) FROM x; +SELECT x_stl2_wxyz((1, '{2}', TRUE)::wxyz) FROM x; + +-- Mixed functions and ROW() expressions testing +SELECT x_stl2_wxyz((x_vlt(), '{2}', TRUE)) FROM x; -- should not be precalculated +SELECT x_stl2_wxyz((x_stl(), '{2}', TRUE)) FROM x; + +-- 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 x VALUES (5); +SELECT simple(); +ROLLBACK; + +-- Drop tables and domains for testing + +DROP TABLE x; + +DROP FUNCTION x_vlt_wxyz, x_vlt_wxyz_child, x_vlt_wxyz_child2; +DROP FUNCTION x_stl_wxyz, x_stl_wxyz_child, x_stl_wxyz_child2, x_stl2_wxyz; +DROP TABLE wxyz, wxyz_child, wxyz_child2; + +DROP FUNCTION x_stl2_no_columns; +DROP TABLE no_columns, no_columns_child, no_columns_child2; + +DROP FUNCTION x_stl2_my_integer_no_check; +DROP DOMAIN my_integer_no_check; + +DROP FUNCTION x_stl2_my_integer_not_null; +DROP DOMAIN my_integer_not_null; + +DROP FUNCTION x_stl2_my_integer_vlt_check; +DROP DOMAIN my_integer_vlt_check; + +DROP FUNCTION x_stl2_my_integer_stl_check; +DROP DOMAIN my_integer_stl_check; + +DROP FUNCTION x_stl2_my_integer_imm_check; +DROP DOMAIN my_integer_imm_check; -- 1.9.1