Hi
so 9. 3. 2019 v 7:22 odesÃlatel Pavel Stehule <[email protected]>
napsal:
> Hi,
>
> Tom introduced supported functions for calculation function's selectivity.
> Still I have similar idea to use supported function for calculation
> function's parameter's types and function return type.
>
> Motivation:
>
> Reduce a necessity of overloading of functions. My motivation is related
> primary to Orafce, but this feature should be helpful for anybody with
> similar goals. The function's overloading is great functionality but it is
> hard for maintenance.
>
> My idea to enhance a CREATE FUNCTION command to be able do
>
> CREATE FUCNTION foo("any")
> RETURNS "any" AS ...
> TYPEINFO foo_typeinfo
>
> CREATE FUNCTION decode(VARIADIC "any")
> RETURNS "any" AS ...
> TYPEINFO decode_typeinfo.
>
> The typeinfo functions returns a pointer tu structure with param types and
> result type. Only function with "any" parameters or "any" result can use
> TYPEINFO supported function. This functionality should not be allowed for
> common functions.
>
> This functionality is limited just for C coders. But I expect so typical
> application coder doesn't need it. It doesn't replace my proposal of
> introduction other polymorphic type - now named "commontype" (can be named
> differently). The commontype is good enough solution for application
> coders, developers.
>
> Comments, notes?
>
here is a patch
I have not a plan to push decode function to upstream. Patch contains it
just as demo.
Regards
Pavel
>
> Regards
>
> Pavel
>
>
>
diff --git a/src/backend/catalog/namespace.c b/src/backend/catalog/namespace.c
index cdd5006a72..79fce58cd9 100644
--- a/src/backend/catalog/namespace.c
+++ b/src/backend/catalog/namespace.c
@@ -1084,6 +1084,8 @@ FuncnameGetCandidates(List *names, int nargs, List *argnames,
effective_nargs * sizeof(Oid));
newResult->pathpos = pathpos;
newResult->oid = procform->oid;
+ newResult->support_func = InvalidOid;
+ newResult->rettype = procform->prorettype;
newResult->nargs = effective_nargs;
newResult->argnumbers = argnumbers;
if (argnumbers)
@@ -1100,6 +1102,7 @@ FuncnameGetCandidates(List *names, int nargs, List *argnames,
/* Simple positional case, just copy proargtypes as-is */
memcpy(newResult->args, procform->proargtypes.values,
pronargs * sizeof(Oid));
+
}
if (variadic)
{
@@ -1114,6 +1117,25 @@ FuncnameGetCandidates(List *names, int nargs, List *argnames,
newResult->nvargs = 0;
newResult->ndargs = use_defaults ? pronargs - nargs : 0;
+ /*
+ * When there are support function, we should to detect, if
+ * there is any "any" argument, and if there is, then we should
+ * to set support_tp_func_oid field.
+ */
+ if (OidIsValid(procform->prosupport))
+ {
+ int i;
+
+ for (i = 0; i < pronargs; i++)
+ {
+ if (newResult->args[i] == ANYOID)
+ {
+ newResult->support_func = procform->prosupport;
+ break;
+ }
+ }
+ }
+
/*
* Does it have the same arguments as something we already accepted?
* If so, decide what to do to avoid returning duplicate argument
@@ -1701,6 +1723,8 @@ OpernameGetCandidates(List *names, char oprkind, bool missing_schema_ok)
newResult->pathpos = pathpos;
newResult->oid = operform->oid;
+ newResult->support_func = InvalidOid;
+ newResult->rettype = operform->oprresult;
newResult->nargs = 2;
newResult->nvargs = 0;
newResult->ndargs = 0;
diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c
index 752cf1b315..46989604e3 100644
--- a/src/backend/parser/parse_func.c
+++ b/src/backend/parser/parse_func.c
@@ -22,6 +22,7 @@
#include "lib/stringinfo.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
+#include "nodes/supportnodes.h"
#include "parser/parse_agg.h"
#include "parser/parse_clause.h"
#include "parser/parse_coerce.h"
@@ -927,6 +928,36 @@ func_match_argtypes(int nargs,
current_candidate = next_candidate)
{
next_candidate = current_candidate->next;
+
+ if (OidIsValid(current_candidate->support_func))
+ {
+ SupportRequestArgTypes req;
+ SupportRequestArgTypes *result;
+
+ req.type = T_SupportRequestArgTypes;
+ req.candidate = current_candidate;
+ req.nargs = nargs;
+ req.typeids = input_typeids;
+
+ result = (SupportRequestArgTypes *)
+ DatumGetPointer(OidFunctionCall1(current_candidate->support_func,
+ PointerGetDatum(&req)));
+
+ /*
+ * Support function should not to support this request. Then
+ * do nothing. Support function can detect failure, and returns
+ * null as candidate. Then eliminate this candidate from list.
+ */
+ if (result == &req)
+ {
+ if (result->candidate)
+ current_candidate = result->candidate;
+ else
+ /* skip this candidate */
+ continue;
+ }
+ }
+
if (can_coerce_type(nargs, input_typeids, current_candidate->args,
COERCION_IMPLICIT))
{
@@ -1589,6 +1620,7 @@ func_get_detail(List *funcname,
*funcid = best_candidate->oid;
*nvargs = best_candidate->nvargs;
+ *rettype = best_candidate->rettype;
*true_typeids = best_candidate->args;
/*
@@ -1617,7 +1649,6 @@ func_get_detail(List *funcname,
elog(ERROR, "cache lookup failed for function %u",
best_candidate->oid);
pform = (Form_pg_proc) GETSTRUCT(ftup);
- *rettype = pform->prorettype;
*retset = pform->proretset;
*vatype = pform->provariadic;
/* fetch default args if caller wants 'em */
diff --git a/src/backend/utils/adt/oracle_compat.c b/src/backend/utils/adt/oracle_compat.c
index 9d01e0be9c..18002e41d1 100644
--- a/src/backend/utils/adt/oracle_compat.c
+++ b/src/backend/utils/adt/oracle_compat.c
@@ -15,9 +15,16 @@
*/
#include "postgres.h"
+#include "nodes/nodes.h"
+#include "nodes/supportnodes.h"
+#include "catalog/namespace.h"
+#include "catalog/pg_type.h"
#include "common/int.h"
+#include "parser/parse_coerce.h"
+#include "parser/parse_oper.h"
#include "utils/builtins.h"
#include "utils/formatting.h"
+#include "utils/lsyscache.h"
#include "mb/pg_wchar.h"
@@ -1066,3 +1073,337 @@ repeat(PG_FUNCTION_ARGS)
PG_RETURN_TEXT_P(result);
}
+
+typedef struct decode_fmgr_cache
+{
+ Oid typid;
+ FmgrInfo eqop_finfo;
+} decode_fmgr_cache;
+
+/*
+ * Decode function
+ */
+Datum
+decode(PG_FUNCTION_ARGS)
+{
+ decode_fmgr_cache *fmgr_cache;
+ Oid search_oid;
+ Oid result_oid;
+ Oid rettype;
+ Oid collation;
+ int i;
+ int result_narg;
+ int nargs = PG_NARGS();
+ bool expr_isnull;
+
+ if (nargs < 3)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("too few function arguments"),
+ errhint("The decode function requires at least 3 arguments")));
+
+ search_oid = get_fn_expr_argtype(fcinfo->flinfo, 1);
+ result_oid = get_fn_expr_argtype(fcinfo->flinfo, 2);
+ collation = PG_GET_COLLATION();
+
+ if (fcinfo->flinfo->fn_extra == NULL)
+ {
+ MemoryContext oldcxt;
+ Oid eqop;
+
+ get_sort_group_operators(search_oid, false, true, false, NULL, &eqop, NULL, NULL);
+
+ oldcxt = MemoryContextSwitchTo(fcinfo->flinfo->fn_mcxt);
+
+ fmgr_cache = palloc(sizeof(decode_fmgr_cache));
+ fmgr_cache->typid = search_oid;
+ fmgr_info(get_opcode(eqop), &fmgr_cache->eqop_finfo);
+
+ MemoryContextSwitchTo(oldcxt);
+
+ fcinfo->flinfo->fn_extra = fmgr_cache;
+ }
+ else
+ fmgr_cache = fcinfo->flinfo->fn_extra;
+
+ /* recheck of fmgr_cache validity, should not be */
+ if (fmgr_cache->typid != search_oid)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("search expression has unexpected type"),
+ errhint("The decode expects \"%s\" type",
+ format_type_be(search_oid))));
+
+ /* recheck rerttype, should not be */
+ rettype = get_fn_expr_rettype(fcinfo->flinfo);
+ if (rettype != result_oid)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("result expression has unexpected type"),
+ errhint("The decode expects \"%s\" type",
+ format_type_be(result_oid))));
+
+ result_narg = nargs % 2 ? -1 : nargs - 1;
+ expr_isnull = PG_ARGISNULL(0);
+
+ for (i = 1; i < nargs; i += 2)
+ {
+ if (expr_isnull)
+ {
+ if (PG_ARGISNULL(i) && i + 1 < nargs)
+ {
+ result_narg = i + 1;
+ break;
+ }
+ }
+ else
+ {
+ if (!PG_ARGISNULL(i) && i + 1 < nargs)
+ {
+ Datum result;
+
+ /* recheck type */
+ if (get_fn_expr_argtype(fcinfo->flinfo, i) != search_oid)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("search expression has unexpected type"),
+ errhint("The decode expects \"%s\" type",
+ format_type_be(search_oid))));
+
+ result = FunctionCall2Coll(&fmgr_cache->eqop_finfo,
+ collation,
+ PG_GETARG_DATUM(0),
+ PG_GETARG_DATUM(i));
+
+ if (DatumGetBool(result))
+ {
+ result_narg = i + 1;
+ break;
+ }
+ }
+ }
+ }
+
+ if (result_narg >= 0 && !PG_ARGISNULL(result_narg))
+ {
+ if (get_fn_expr_argtype(fcinfo->flinfo, result_narg) != result_oid)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("result expression has unexpected type"),
+ errhint("The decode expects \"%s\" type",
+ format_type_be(result_oid))));
+
+ PG_RETURN_DATUM(PG_GETARG_DATUM(result_narg));
+ }
+ else
+ PG_RETURN_NULL();
+}
+
+/*
+ * select_common_type_from_vector()
+ * Determine the common supertype of vector of Oids.
+ *
+ * Similar to select_common_type() but simplified for polymorphics
+ * type processing. When there are no supertype, then returns InvalidOid,
+ * when noerror is true, or raise exception when noerror is false.
+ */
+static Oid
+select_common_type_from_vector(int nargs, Oid *typeids, bool noerror)
+{
+ int i = 0;
+ Oid ptype;
+ TYPCATEGORY pcategory;
+ bool pispreferred;
+
+ Assert(nargs > 0);
+ ptype = typeids[0];
+
+ /* fast leave when all types are same */
+ if (ptype != UNKNOWNOID)
+ {
+ for (i = 1; i < nargs; i++)
+ {
+ if (ptype != typeids[i])
+ break;
+ }
+
+ if (i == nargs)
+ return ptype;
+ }
+
+ /*
+ * Nope, so set up for the full algorithm. Note that at this point, lc
+ * points to the first list item with type different from pexpr's; we need
+ * not re-examine any items the previous loop advanced over.
+ */
+ ptype = getBaseType(ptype);
+ get_type_category_preferred(ptype, &pcategory, &pispreferred);
+
+ for (; i < nargs; i++)
+ {
+ Oid ntype = getBaseType(typeids[i]);
+
+ /* move on to next one if no new information... */
+ if (ntype != UNKNOWNOID && ntype != ptype)
+ {
+ TYPCATEGORY ncategory;
+ bool nispreferred;
+
+ get_type_category_preferred(ntype, &ncategory, &nispreferred);
+
+ if (ptype == UNKNOWNOID)
+ {
+ /* so far, only unknowns so take anything... */
+ ptype = ntype;
+ pcategory = ncategory;
+ pispreferred = nispreferred;
+ }
+ else if (ncategory != pcategory)
+ {
+ if (noerror)
+ return InvalidOid;
+
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("types %s and %s cannot be matched",
+ format_type_be(ptype),
+ format_type_be(ntype))));
+ }
+ else if (!pispreferred &&
+ can_coerce_type(1, &ptype, &ntype, COERCION_IMPLICIT) &&
+ !can_coerce_type(1, &ntype, &ptype, COERCION_IMPLICIT))
+ {
+ /*
+ * take new type if can coerce to it implicitly but not the
+ * other way; but if we have a preferred type, stay on it.
+ */
+ ptype = ntype;
+ pcategory = ncategory;
+ pispreferred = nispreferred;
+ }
+ }
+ }
+
+ /*
+ * Be consistent with select_common_type()
+ */
+ if (ptype == UNKNOWNOID)
+ ptype = TEXTOID;
+
+ return ptype;
+}
+
+/*
+ * Support function for decode function
+ *
+ * It converts VARIADIC "any" to real types, and set real expected type.
+ */
+Datum
+decode_support(PG_FUNCTION_ARGS)
+{
+ Node *rawreq = (Node *) PG_GETARG_POINTER(0);
+ Node *ret = NULL;
+
+ if (IsA(rawreq, SupportRequestArgTypes))
+ {
+ SupportRequestArgTypes *fd = (SupportRequestArgTypes *) rawreq;
+ FuncCandidateList candidate = fd->candidate;
+
+ if (candidate->nargs >= 3 && !candidate->argnumbers && fd->nargs == candidate->nargs)
+ {
+ FuncCandidateList newc;
+ Oid search_oid;
+ Oid result_oid;
+ Oid search_typids[FUNC_MAX_ARGS];
+ Oid result_typids[FUNC_MAX_ARGS];
+ int search_nargs = 0;
+ int result_nargs = 0;
+ int i;
+
+ newc = palloc(offsetof(struct _FuncCandidateList, args) +
+ candidate->nargs * sizeof(Oid));
+
+ /*
+ * Oracle should not to find most common types for numeric types, because
+ * type number is generic and widely used as integer type too. Using same
+ * setup is too simple for Postgres - using numeric instead int can has
+ * negative performance impact.
+ *
+ * When type is not TEXT, then the most common type is used.
+ */
+ search_oid = fd->typeids[1] != UNKNOWNOID ? fd->typeids[1] : TEXTOID;
+ result_oid = fd->typeids[2] != UNKNOWNOID ? fd->typeids[2] : TEXTOID;
+
+ if (search_oid != TEXTOID || result_oid != TEXTOID)
+ {
+ for (i = 0; i < fd->nargs; i++)
+ {
+ if (i == 0)
+ search_typids[search_nargs++] = fd->typeids[i];
+ else if (i % 2) /* even position */
+ {
+ if (i + 1 < fd->nargs)
+ search_typids[search_nargs++] = fd->typeids[i];
+ else
+ result_typids[result_nargs++] = fd->typeids[i];
+ }
+ else /* odd position */
+ result_typids[result_nargs++] = fd->typeids[i];
+ }
+
+ if (search_oid != TEXTOID)
+ {
+ search_oid = select_common_type_from_vector(search_nargs,
+ search_typids,
+ true);
+
+ if (!OidIsValid(search_oid)) /* should not to be */
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("cannot to detect common type for search expression")));
+ }
+
+ if (result_oid != TEXTOID)
+ {
+ result_oid = select_common_type_from_vector(result_nargs,
+ result_typids,
+ true);
+
+ if (!OidIsValid(result_oid)) /* should not to be */
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("cannot to detect common type for result expression")));
+ }
+ }
+
+ memcpy(newc, candidate, sizeof(struct _FuncCandidateList));
+
+ newc->rettype = result_oid;
+
+ /* expression is casted to search */
+ newc->args[0] = search_oid;
+
+ for (i = 1; i < candidate->nargs; i += 2)
+ {
+ /* is pair or alone default? */
+ if (i + 1 < candidate->nargs)
+ {
+ newc->args[i] = search_oid;
+ newc->args[i+1] = result_oid;
+ }
+ else
+ newc->args[i] = result_oid;
+ }
+
+ candidate = newc;
+ }
+ else
+ candidate = NULL;
+
+ fd->candidate = candidate;
+
+ ret = (Node *) fd;
+ }
+
+ PG_RETURN_POINTER(ret);
+}
diff --git a/src/include/catalog/namespace.h b/src/include/catalog/namespace.h
index 67418346e6..9a52a8260b 100644
--- a/src/include/catalog/namespace.h
+++ b/src/include/catalog/namespace.h
@@ -30,6 +30,8 @@ typedef struct _FuncCandidateList
struct _FuncCandidateList *next;
int pathpos; /* for internal use of namespace lookup */
Oid oid; /* the function or operator's OID */
+ Oid support_func; /* oid of support function if exists */
+ Oid rettype; /* return type set by support function */
int nargs; /* number of arg types returned */
int nvargs; /* number of args to become variadic array */
int ndargs; /* number of defaulted args */
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index eac909109c..02fb56ef24 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3318,6 +3318,15 @@
proname => 'repeat', prorettype => 'text', proargtypes => 'text int4',
prosrc => 'repeat' },
+{ oid => '6107', descr => 'choose a value from list',
+ proname => 'decode', provariadic => 'any', proisstrict => 'f',
+ prosupport => 'decode_support', prorettype => 'any', proargtypes => 'any',
+ prosrc => 'decode',
+ proallargtypes => '{any}', proargmodes => '{v}'},
+{ oid => '6108', descr => 'parser support for decode',
+ proname => 'decode_support', prorettype => 'internal',
+ proargtypes => 'internal', prosrc => 'decode_support' },
+
{ oid => '1623', descr => 'convert SQL99 regexp pattern to POSIX style',
proname => 'similar_escape', proisstrict => 'f', prorettype => 'text',
proargtypes => 'text text', prosrc => 'similar_escape' },
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index ffb4cd4bcc..daee08c25d 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -512,7 +512,8 @@ typedef enum NodeTag
T_SupportRequestSelectivity, /* in nodes/supportnodes.h */
T_SupportRequestCost, /* in nodes/supportnodes.h */
T_SupportRequestRows, /* in nodes/supportnodes.h */
- T_SupportRequestIndexCondition /* in nodes/supportnodes.h */
+ T_SupportRequestIndexCondition, /* in nodes/supportnodes.h */
+ T_SupportRequestArgTypes /* in nodes/supportnodes.h */
} NodeTag;
/*
diff --git a/src/include/nodes/supportnodes.h b/src/include/nodes/supportnodes.h
index 460d75bd2d..7a083e5241 100644
--- a/src/include/nodes/supportnodes.h
+++ b/src/include/nodes/supportnodes.h
@@ -38,7 +38,7 @@
struct PlannerInfo; /* avoid including pathnodes.h here */
struct IndexOptInfo;
struct SpecialJoinInfo;
-
+struct _FuncCandidateList;
/*
* The Simplify request allows the support function to perform plan-time
@@ -239,4 +239,26 @@ typedef struct SupportRequestIndexCondition
* equivalent of the function call */
} SupportRequestIndexCondition;
+/*
+ * PostgreSQL function's parameters are described by list of their types.
+ * For some specific functions this technique is not practical (require
+ * lot of overloaded functions), and better is using some algoritm. This
+ * node is used for functions that returns "any" type or has some "any"
+ * parameter.
+ *
+ * Support function is required for functions that returns "any" type.
+ * For "any" parameters it is optional, but it can increase a execution,
+ * because type detection (using exprType()) can be processed in parse
+ * time.
+ */
+typedef struct SupportRequestArgTypes
+{
+ NodeTag type;
+
+ int nargs;
+ Oid *typeids;
+ Oid rettype;
+ struct _FuncCandidateList *candidate;
+} SupportRequestArgTypes;
+
#endif /* SUPPORTNODES_H */
