http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/49fd529a/src/test/feature/UDF/lib/function.c ---------------------------------------------------------------------- diff --git a/src/test/feature/UDF/lib/function.c b/src/test/feature/UDF/lib/function.c new file mode 100755 index 0000000..f346855 --- /dev/null +++ b/src/test/feature/UDF/lib/function.c @@ -0,0 +1,1245 @@ +#include "postgres.h" +#include "funcapi.h" +#include "tablefuncapi.h" + +#include <float.h> +#include <math.h> + +#include "access/transam.h" +#include "access/xact.h" +#include "catalog/pg_type.h" +#include "cdb/memquota.h" +#include "commands/sequence.h" +#include "commands/trigger.h" +#include "executor/executor.h" +#include "executor/spi.h" +#include "parser/parse_expr.h" +#include "libpq/auth.h" +#include "libpq/hba.h" +#include "utils/builtins.h" +#include "utils/geo_decls.h" +#include "utils/lsyscache.h" +#include "utils/resscheduler.h" + + +#define LDELIM '(' +#define RDELIM ')' +#define NARGS 3 +#define TTDUMMY_INFINITY 999999 + + +extern Datum int44in(PG_FUNCTION_ARGS); +extern Datum int44out(PG_FUNCTION_ARGS); +extern Datum check_primary_key(PG_FUNCTION_ARGS); +extern Datum check_foreign_key(PG_FUNCTION_ARGS); +extern Datum autoinc(PG_FUNCTION_ARGS); +extern Datum funny_dup17(PG_FUNCTION_ARGS); +extern Datum ttdummy(PG_FUNCTION_ARGS); +extern Datum set_ttdummy(PG_FUNCTION_ARGS); + + +#ifdef PG_MODULE_MAGIC +PG_MODULE_MAGIC; +#endif + + + +/* widget_in and widget_out */ +typedef struct +{ + Point center; + double radius; +} WIDGET; + +WIDGET *widget_in(char *str); +char *widget_out(WIDGET * widget); + +WIDGET *widget_in(char *str) +{ + char *p, *coord[NARGS], buf2[1000]; + int i; + WIDGET *result; + + if (str == NULL) + { + return NULL; + } + + for (i = 0, p = str; *p && i < NARGS && *p != RDELIM; p++) + { + if (*p == ',' || (*p == LDELIM && !i)) + { + coord[i++] = p + 1; + } + } + + if (i < NARGS - 1) + { + return NULL; + } + + result = (WIDGET *) palloc(sizeof(WIDGET)); + result->center.x = atof(coord[0]); + result->center.y = atof(coord[1]); + result->radius = atof(coord[2]); + + snprintf(buf2, sizeof(buf2), "widget_in: read (%f, %f, %f)\n", + result->center.x, result->center.y, result->radius); + + return result; +} + + +char *widget_out(WIDGET * widget) +{ + char *result; + + if (widget == NULL) + { + return NULL; + } + + result = (char *) palloc(60); + + sprintf(result, "(%g,%g,%g)", + widget->center.x, widget->center.y, widget->radius); + + return result; +} + + + +/* int44in and int44out */ +/* + * Type int44 has no real-world use, but the function tests use it. + * It's a four-element vector of int4's. + */ + +/* + * int44in: converts "num num ..." to internal form + * Note: Fills any missing positions with zeroes. + */ +PG_FUNCTION_INFO_V1(int44in); +Datum int44in(PG_FUNCTION_ARGS) +{ + char *input_string = PG_GETARG_CSTRING(0); + int32 *result = (int32 *) palloc(4 * sizeof(int32)); + int i; + + i = sscanf(input_string, + "%d, %d, %d, %d", + &result[0], + &result[1], + &result[2], + &result[3]); + + while (i < 4) + { + result[i++] = 0; + } + + PG_RETURN_POINTER(result); +} + +/* + * int44out: converts internal form to "num num ..." + */ +PG_FUNCTION_INFO_V1(int44out); +Datum int44out(PG_FUNCTION_ARGS) +{ + int32 *an_array = (int32 *) PG_GETARG_POINTER(0); + /* Allow 14 digits sign */ + char *result = (char *) palloc(16 * 4); + int i; + char *walk; + + walk = result; + for (i = 0; i < 4; i++) + { + pg_ltoa(an_array[i], walk); + while (*++walk != '\0') + ; + *walk++ = ' '; + } + *--walk = '\0'; + PG_RETURN_CSTRING(result); +} + + + +/* check_primary_key, check_foreign_key and helper function find_plan */ +typedef struct +{ + char *ident; + int nplans; + void **splan; +} EPlan; + +static EPlan *FPlans = NULL; +static int nFPlans = 0; +static EPlan *PPlans = NULL; +static int nPPlans = 0; + +static EPlan *find_plan(char *ident, EPlan ** eplan, int *nplans); + +/* + * check_primary_key () -- check that key in tuple being inserted/updated + * references existing tuple in "primary" table. + * Though it's called without args You have to specify referenced + * table/keys while creating trigger: key field names in triggered table, + * referenced table name, referenced key field names: + * EXECUTE PROCEDURE + * check_primary_key ('Fkey1', 'Fkey2', 'Ptable', 'Pkey1', 'Pkey2'). + */ +PG_FUNCTION_INFO_V1(check_primary_key); +Datum check_primary_key(PG_FUNCTION_ARGS) +{ + TriggerData *trigdata = (TriggerData *) fcinfo->context; + Trigger *trigger; /* to get trigger name */ + int nargs; /* # of args specified in CREATE TRIGGER */ + char **args; /* arguments: column names and table name */ + int nkeys; /* # of key columns (= nargs / 2) */ + Datum *kvals; /* key values */ + char *relname; /* referenced relation name */ + Relation rel; /* triggered relation */ + HeapTuple tuple = NULL; /* tuple to return */ + TupleDesc tupdesc; /* tuple description */ + EPlan *plan; /* prepared plan */ + Oid *argtypes = NULL; /* key types to prepare execution plan */ + bool isnull; /* to know is some column NULL or not */ + char ident[2 * NAMEDATALEN]; /* to identify myself */ + int ret; + int i; + +#ifdef DEBUG_QUERY + elog(DEBUG4, "check_primary_key: Enter Function"); +#endif + + /* + * Some checks first... + */ + + /* Called by trigger manager ? */ + if (!CALLED_AS_TRIGGER(fcinfo)) + /* internal error */ + elog(ERROR, "check_primary_key: not fired by trigger manager"); + + /* Should be called for ROW trigger */ + if (TRIGGER_FIRED_FOR_STATEMENT(trigdata->tg_event)) + /* internal error */ + elog(ERROR, "check_primary_key: can't process STATEMENT events"); + + /* If INSERTion then must check Tuple to being inserted */ + if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event)) + tuple = trigdata->tg_trigtuple; + + /* Not should be called for DELETE */ + else if (TRIGGER_FIRED_BY_DELETE(trigdata->tg_event)) + /* internal error */ + elog(ERROR, "check_primary_key: can't process DELETE events"); + + /* If UPDATion the must check new Tuple, not old one */ + else + tuple = trigdata->tg_newtuple; + + trigger = trigdata->tg_trigger; + nargs = trigger->tgnargs; + args = trigger->tgargs; + + if (nargs % 2 != 1) /* odd number of arguments! */ + /* internal error */ + elog(ERROR, "check_primary_key: odd number of arguments should be specified"); + + nkeys = nargs / 2; + relname = args[nkeys]; + rel = trigdata->tg_relation; + tupdesc = rel->rd_att; + + /* Connect to SPI manager */ + if ((ret = SPI_connect()) < 0) + /* internal error */ + elog(ERROR, "check_primary_key: SPI_connect returned %d", ret); + + /* + * We use SPI plan preparation feature, so allocate space to place key + * values. + */ + kvals = (Datum *) palloc(nkeys * sizeof(Datum)); + + /* + * Construct ident string as TriggerName $ TriggeredRelationId and try to + * find prepared execution plan. + */ + snprintf(ident, sizeof(ident), "%s$%u", trigger->tgname, rel->rd_id); + plan = find_plan(ident, &PPlans, &nPPlans); + + /* if there is no plan then allocate argtypes for preparation */ + if (plan->nplans <= 0) + argtypes = (Oid *) palloc(nkeys * sizeof(Oid)); + + /* For each column in key ... */ + for (i = 0; i < nkeys; i++) + { + /* get index of column in tuple */ + int fnumber = SPI_fnumber(tupdesc, args[i]); + + /* Bad guys may give us un-existing column in CREATE TRIGGER */ + if (fnumber < 0) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_COLUMN), + errmsg("there is no attribute \"%s\" in relation \"%s\"", + args[i], SPI_getrelname(rel)))); + + /* Well, get binary (in internal format) value of column */ + kvals[i] = SPI_getbinval(tuple, tupdesc, fnumber, &isnull); + + /* + * If it's NULL then nothing to do! DON'T FORGET call SPI_finish ()! + * DON'T FORGET return tuple! Executor inserts tuple you're returning! + * If you return NULL then nothing will be inserted! + */ + if (isnull) + { + SPI_finish(); + return PointerGetDatum(tuple); + } + + if (plan->nplans <= 0) /* Get typeId of column */ + argtypes[i] = SPI_gettypeid(tupdesc, fnumber); + } + + /* + * If we have to prepare plan ... + */ + if (plan->nplans <= 0) + { + void *pplan; + char sql[8192]; + + /* + * Construct query: SELECT 1 FROM _referenced_relation_ WHERE Pkey1 = + * $1 [AND Pkey2 = $2 [...]] + */ + snprintf(sql, sizeof(sql), "select 1 from %s where ", relname); + for (i = 0; i < nkeys; i++) + { + snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%s = $%d %s", + args[i + nkeys + 1], i + 1, (i < nkeys - 1) ? "and " : ""); + } + + /* Prepare plan for query */ + pplan = SPI_prepare(sql, nkeys, argtypes); + if (pplan == NULL) + /* internal error */ + elog(ERROR, "check_primary_key: SPI_prepare returned %d", SPI_result); + + /* + * Remember that SPI_prepare places plan in current memory context - + * so, we have to save plan in Top memory context for latter use. + */ + pplan = SPI_saveplan(pplan); + if (pplan == NULL) + /* internal error */ + elog(ERROR, "check_primary_key: SPI_saveplan returned %d", SPI_result); + plan->splan = (void **) malloc(sizeof(void *)); + *(plan->splan) = pplan; + plan->nplans = 1; + } + + /* + * Ok, execute prepared plan. + */ + ret = SPI_execp(*(plan->splan), kvals, NULL, 1); + /* we have no NULLs - so we pass ^^^^ here */ + + if (ret < 0) + /* internal error */ + elog(ERROR, "check_primary_key: SPI_execp returned %d", ret); + + /* + * If there are no tuples returned by SELECT then ... + */ + if (SPI_processed == 0) + ereport(ERROR, + (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION), + errmsg("tuple references non-existent key"), + errdetail("Trigger \"%s\" found tuple referencing non-existent key in \"%s\".", trigger->tgname, relname))); + + SPI_finish(); + + return PointerGetDatum(tuple); +} + + +/* + * check_foreign_key () -- check that key in tuple being deleted/updated + * is not referenced by tuples in "foreign" table(s). + * Though it's called without args You have to specify (while creating trigger): + * number of references, action to do if key referenced + * ('restrict' | 'setnull' | 'cascade'), key field names in triggered + * ("primary") table and referencing table(s)/keys: + * EXECUTE PROCEDURE + * check_foreign_key (2, 'restrict', 'Pkey1', 'Pkey2', + * 'Ftable1', 'Fkey11', 'Fkey12', 'Ftable2', 'Fkey21', 'Fkey22'). + */ +PG_FUNCTION_INFO_V1(check_foreign_key); +Datum check_foreign_key(PG_FUNCTION_ARGS) +{ + TriggerData *trigdata = (TriggerData *) fcinfo->context; + Trigger *trigger; /* to get trigger name */ + int nargs; /* # of args specified in CREATE TRIGGER */ + char **args; /* arguments: as described above */ + char **args_temp; + int nrefs; /* number of references (== # of plans) */ + char action; /* 'R'estrict | 'S'etnull | 'C'ascade */ + int nkeys; /* # of key columns */ + Datum *kvals; /* key values */ + char *relname; /* referencing relation name */ + Relation rel; /* triggered relation */ + HeapTuple trigtuple = NULL; /* tuple to being changed */ + HeapTuple newtuple = NULL; /* tuple to return */ + TupleDesc tupdesc; /* tuple description */ + EPlan *plan; /* prepared plan(s) */ + Oid *argtypes = NULL; /* key types to prepare execution plan */ + bool isnull; /* to know is some column NULL or not */ + bool isequal = true; /* are keys in both tuples equal (in UPDATE) */ + char ident[2 * NAMEDATALEN]; /* to identify myself */ + int is_update = 0; + int ret; + int i, + r; + +#ifdef DEBUG_QUERY + elog(DEBUG4, "check_foreign_key: Enter Function"); +#endif + + /* + * Some checks first... + */ + + /* Called by trigger manager ? */ + if (!CALLED_AS_TRIGGER(fcinfo)) + /* internal error */ + elog(ERROR, "check_foreign_key: not fired by trigger manager"); + + /* Should be called for ROW trigger */ + if (TRIGGER_FIRED_FOR_STATEMENT(trigdata->tg_event)) + /* internal error */ + elog(ERROR, "check_foreign_key: can't process STATEMENT events"); + + /* Not should be called for INSERT */ + if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event)) + /* internal error */ + elog(ERROR, "check_foreign_key: can't process INSERT events"); + + /* Have to check tg_trigtuple - tuple being deleted */ + trigtuple = trigdata->tg_trigtuple; + + /* + * But if this is UPDATE then we have to return tg_newtuple. Also, if key + * in tg_newtuple is the same as in tg_trigtuple then nothing to do. + */ + is_update = 0; + if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event)) + { + newtuple = trigdata->tg_newtuple; + is_update = 1; + } + trigger = trigdata->tg_trigger; + nargs = trigger->tgnargs; + args = trigger->tgargs; + + if (nargs < 5) /* nrefs, action, key, Relation, key - at + * least */ + /* internal error */ + elog(ERROR, "check_foreign_key: too short %d (< 5) list of arguments", nargs); + + nrefs = pg_atoi(args[0], sizeof(int), 0); + if (nrefs < 1) + /* internal error */ + elog(ERROR, "check_foreign_key: %d (< 1) number of references specified", nrefs); + action = tolower((unsigned char) *(args[1])); + if (action != 'r' && action != 'c' && action != 's') + /* internal error */ + elog(ERROR, "check_foreign_key: invalid action %s", args[1]); + nargs -= 2; + args += 2; + nkeys = (nargs - nrefs) / (nrefs + 1); + if (nkeys <= 0 || nargs != (nrefs + nkeys * (nrefs + 1))) + /* internal error */ + elog(ERROR, "check_foreign_key: invalid number of arguments %d for %d references", + nargs + 2, nrefs); + + rel = trigdata->tg_relation; + tupdesc = rel->rd_att; + + /* Connect to SPI manager */ + if ((ret = SPI_connect()) < 0) + /* internal error */ + elog(ERROR, "check_foreign_key: SPI_connect returned %d", ret); + + /* + * We use SPI plan preparation feature, so allocate space to place key + * values. + */ + kvals = (Datum *) palloc(nkeys * sizeof(Datum)); + + /* + * Construct ident string as TriggerName $ TriggeredRelationId and try to + * find prepared execution plan(s). + */ + snprintf(ident, sizeof(ident), "%s$%u", trigger->tgname, rel->rd_id); + plan = find_plan(ident, &FPlans, &nFPlans); + + /* if there is no plan(s) then allocate argtypes for preparation */ + if (plan->nplans <= 0) + argtypes = (Oid *) palloc(nkeys * sizeof(Oid)); + + /* + * else - check that we have exactly nrefs plan(s) ready + */ + else if (plan->nplans != nrefs) + /* internal error */ + elog(ERROR, "%s: check_foreign_key: # of plans changed in meantime", + trigger->tgname); + + /* For each column in key ... */ + for (i = 0; i < nkeys; i++) + { + /* get index of column in tuple */ + int fnumber = SPI_fnumber(tupdesc, args[i]); + + /* Bad guys may give us un-existing column in CREATE TRIGGER */ + if (fnumber < 0) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_COLUMN), + errmsg("there is no attribute \"%s\" in relation \"%s\"", + args[i], SPI_getrelname(rel)))); + + /* Well, get binary (in internal format) value of column */ + kvals[i] = SPI_getbinval(trigtuple, tupdesc, fnumber, &isnull); + + /* + * If it's NULL then nothing to do! DON'T FORGET call SPI_finish ()! + * DON'T FORGET return tuple! Executor inserts tuple you're returning! + * If you return NULL then nothing will be inserted! + */ + if (isnull) + { + SPI_finish(); + return PointerGetDatum((newtuple == NULL) ? trigtuple : newtuple); + } + + /* + * If UPDATE then get column value from new tuple being inserted and + * compare is this the same as old one. For the moment we use string + * presentation of values... + */ + if (newtuple != NULL) + { + char *oldval = SPI_getvalue(trigtuple, tupdesc, fnumber); + char *newval; + + /* this shouldn't happen! SPI_ERROR_NOOUTFUNC ? */ + if (oldval == NULL) + /* internal error */ + elog(ERROR, "check_foreign_key: SPI_getvalue returned %d", SPI_result); + newval = SPI_getvalue(newtuple, tupdesc, fnumber); + if (newval == NULL || strcmp(oldval, newval) != 0) + isequal = false; + } + + if (plan->nplans <= 0) /* Get typeId of column */ + argtypes[i] = SPI_gettypeid(tupdesc, fnumber); + } + args_temp = args; + nargs -= nkeys; + args += nkeys; + + /* + * If we have to prepare plans ... + */ + if (plan->nplans <= 0) + { + void *pplan; + char sql[8192]; + char **args2 = args; + + plan->splan = (void **) malloc(nrefs * sizeof(void *)); + + for (r = 0; r < nrefs; r++) + { + relname = args2[0]; + + /*--------- + * For 'R'estrict action we construct SELECT query: + * + * SELECT 1 + * FROM _referencing_relation_ + * WHERE Fkey1 = $1 [AND Fkey2 = $2 [...]] + * + * to check is tuple referenced or not. + *--------- + */ + if (action == 'r') + + snprintf(sql, sizeof(sql), "select 1 from %s where ", relname); + + /*--------- + * For 'C'ascade action we construct DELETE query + * + * DELETE + * FROM _referencing_relation_ + * WHERE Fkey1 = $1 [AND Fkey2 = $2 [...]] + * + * to delete all referencing tuples. + *--------- + */ + + /* + * Max : Cascade with UPDATE query i create update query that + * updates new key values in referenced tables + */ + + + else if (action == 'c') + { + if (is_update == 1) + { + int fn; + char *nv; + int k; + + snprintf(sql, sizeof(sql), "update %s set ", relname); + for (k = 1; k <= nkeys; k++) + { + int is_char_type = 0; + char *type; + + fn = SPI_fnumber(tupdesc, args_temp[k - 1]); + nv = SPI_getvalue(newtuple, tupdesc, fn); + type = SPI_gettype(tupdesc, fn); + + if ((strcmp(type, "text") && strcmp(type, "varchar") && + strcmp(type, "char") && strcmp(type, "bpchar") && + strcmp(type, "date") && strcmp(type, "timestamp")) == 0) + is_char_type = 1; +#ifdef DEBUG_QUERY + elog(DEBUG4, "check_foreign_key Debug value %s type %s %d", + nv, type, is_char_type); +#endif + + /* + * is_char_type =1 i set ' ' for define a new value + */ + snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), + " %s = %s%s%s %s ", + args2[k], (is_char_type > 0) ? "'" : "", + nv, (is_char_type > 0) ? "'" : "", (k < nkeys) ? ", " : ""); + is_char_type = 0; + } + strcat(sql, " where "); + + } + else + /* DELETE */ + snprintf(sql, sizeof(sql), "delete from %s where ", relname); + + } + + /* + * For 'S'etnull action we construct UPDATE query - UPDATE + * _referencing_relation_ SET Fkey1 null [, Fkey2 null [...]] + * WHERE Fkey1 = $1 [AND Fkey2 = $2 [...]] - to set key columns in + * all referencing tuples to NULL. + */ + else if (action == 's') + { + snprintf(sql, sizeof(sql), "update %s set ", relname); + for (i = 1; i <= nkeys; i++) + { + snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), + "%s = null%s", + args2[i], (i < nkeys) ? ", " : ""); + } + strcat(sql, " where "); + } + + /* Construct WHERE qual */ + for (i = 1; i <= nkeys; i++) + { + snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%s = $%d %s", + args2[i], i, (i < nkeys) ? "and " : ""); + } + + /* Prepare plan for query */ + pplan = SPI_prepare(sql, nkeys, argtypes); + if (pplan == NULL) + /* internal error */ + elog(ERROR, "check_foreign_key: SPI_prepare returned %d", SPI_result); + + /* + * Remember that SPI_prepare places plan in current memory context + * - so, we have to save plan in Top memory context for latter + * use. + */ + pplan = SPI_saveplan(pplan); + if (pplan == NULL) + /* internal error */ + elog(ERROR, "check_foreign_key: SPI_saveplan returned %d", SPI_result); + + plan->splan[r] = pplan; + + args2 += nkeys + 1; /* to the next relation */ + } + plan->nplans = nrefs; +#ifdef DEBUG_QUERY + elog(DEBUG4, "check_foreign_key Debug Query is : %s ", sql); +#endif + } + + /* + * If UPDATE and key is not changed ... + */ + if (newtuple != NULL && isequal) + { + SPI_finish(); + return PointerGetDatum(newtuple); + } + + /* + * Ok, execute prepared plan(s). + */ + for (r = 0; r < nrefs; r++) + { + /* + * For 'R'estrict we may to execute plan for one tuple only, for other + * actions - for all tuples. + */ + int tcount = (action == 'r') ? 1 : 0; + + relname = args[0]; + + snprintf(ident, sizeof(ident), "%s$%u", trigger->tgname, rel->rd_id); + plan = find_plan(ident, &FPlans, &nFPlans); + ret = SPI_execp(plan->splan[r], kvals, NULL, tcount); + /* we have no NULLs - so we pass ^^^^ here */ + + if (ret < 0) + ereport(ERROR, + (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION), + errmsg("SPI_execp returned %d", ret))); + + /* If action is 'R'estrict ... */ + if (action == 'r') + { + /* If there is tuple returned by SELECT then ... */ + if (SPI_processed > 0) + ereport(ERROR, + (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION), + errmsg("\"%s\": tuple is referenced in \"%s\"", + trigger->tgname, relname))); + } + else + { +#ifdef REFINT_VERBOSE + elog(NOTICE, "%s: %d tuple(s) of %s are %s", + trigger->tgname, SPI_processed, relname, + (action == 'c') ? "deleted" : "set to null"); +#endif + } + args += nkeys + 1; /* to the next relation */ + } + + SPI_finish(); + + return PointerGetDatum((newtuple == NULL) ? trigtuple : newtuple); +} + + +static EPlan *find_plan(char *ident, EPlan ** eplan, int *nplans) +{ + EPlan *newp; + int i; + + if (*nplans > 0) + { + for (i = 0; i < *nplans; i++) + { + if (strcmp((*eplan)[i].ident, ident) == 0) + break; + } + if (i != *nplans) + return (*eplan + i); + *eplan = (EPlan *) realloc(*eplan, (i + 1) * sizeof(EPlan)); + newp = *eplan + i; + } + else + { + newp = *eplan = (EPlan *) malloc(sizeof(EPlan)); + (*nplans) = i = 0; + } + + newp->ident = (char *) malloc(strlen(ident) + 1); + strcpy(newp->ident, ident); + newp->nplans = 0; + newp->splan = NULL; + (*nplans)++; + + return (newp); +} + + + +/* autoinc */ +PG_FUNCTION_INFO_V1(autoinc); +Datum autoinc(PG_FUNCTION_ARGS) +{ + TriggerData *trigdata = (TriggerData *) fcinfo->context; + Trigger *trigger; /* to get trigger name */ + int nargs; /* # of arguments */ + int *chattrs; /* attnums of attributes to change */ + int chnattrs = 0; /* # of above */ + Datum *newvals; /* vals of above */ + char **args; /* arguments */ + char *relname; /* triggered relation name */ + Relation rel; /* triggered relation */ + HeapTuple rettuple = NULL; + TupleDesc tupdesc; /* tuple description */ + bool isnull; + int i; + + if (!CALLED_AS_TRIGGER(fcinfo)) + /* internal error */ + elog(ERROR, "not fired by trigger manager"); + if (TRIGGER_FIRED_FOR_STATEMENT(trigdata->tg_event)) + /* internal error */ + elog(ERROR, "can't process STATEMENT events"); + if (TRIGGER_FIRED_AFTER(trigdata->tg_event)) + /* internal error */ + elog(ERROR, "must be fired before event"); + + if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event)) + rettuple = trigdata->tg_trigtuple; + else if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event)) + rettuple = trigdata->tg_newtuple; + else + /* internal error */ + elog(ERROR, "can't process DELETE events"); + + rel = trigdata->tg_relation; + relname = SPI_getrelname(rel); + + trigger = trigdata->tg_trigger; + + nargs = trigger->tgnargs; + if (nargs <= 0 || nargs % 2 != 0) + /* internal error */ + elog(ERROR, "autoinc (%s): even number gt 0 of arguments was expected", relname); + + args = trigger->tgargs; + tupdesc = rel->rd_att; + + chattrs = (int *) palloc(nargs / 2 * sizeof(int)); + newvals = (Datum *) palloc(nargs / 2 * sizeof(Datum)); + + for (i = 0; i < nargs;) + { + int attnum = SPI_fnumber(tupdesc, args[i]); + int32 val; + Datum seqname; + + if (attnum < 0) + ereport(ERROR, + (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION), + errmsg("\"%s\" has no attribute \"%s\"", + relname, args[i]))); + + if (SPI_gettypeid(tupdesc, attnum) != INT4OID) + ereport(ERROR, + (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION), + errmsg("attribute \"%s\" of \"%s\" must be type INT4", + args[i], relname))); + + val = DatumGetInt32(SPI_getbinval(rettuple, tupdesc, attnum, &isnull)); + + if (!isnull && val != 0) + { + i += 2; + continue; + } + + i++; + chattrs[chnattrs] = attnum; + seqname = DirectFunctionCall1(textin, + CStringGetDatum(args[i])); + newvals[chnattrs] = DirectFunctionCall1(nextval, seqname); + /* nextval now returns int64; coerce down to int32 */ + newvals[chnattrs] = Int32GetDatum((int32) DatumGetInt64(newvals[chnattrs])); + if (DatumGetInt32(newvals[chnattrs]) == 0) + { + newvals[chnattrs] = DirectFunctionCall1(nextval, seqname); + newvals[chnattrs] = Int32GetDatum((int32) DatumGetInt64(newvals[chnattrs])); + } + pfree(DatumGetTextP(seqname)); + chnattrs++; + i++; + } + + if (chnattrs > 0) + { + rettuple = SPI_modifytuple(rel, rettuple, chnattrs, chattrs, newvals, NULL); + if (rettuple == NULL) + /* internal error */ + elog(ERROR, "autoinc (%s): %d returned by SPI_modifytuple", + relname, SPI_result); + } + + pfree(relname); + pfree(chattrs); + pfree(newvals); + + return PointerGetDatum(rettuple); +} + + + +/* funny_dup17 */ +static TransactionId fd17b_xid = InvalidTransactionId; +static TransactionId fd17a_xid = InvalidTransactionId; +static int fd17b_level = 0; +static int fd17a_level = 0; +static bool fd17b_recursion = true; +static bool fd17a_recursion = true; + +PG_FUNCTION_INFO_V1(funny_dup17); +Datum funny_dup17(PG_FUNCTION_ARGS) +{ + TriggerData *trigdata = (TriggerData *) fcinfo->context; + TransactionId *xid; + int *level; + bool *recursion; + Relation rel; + TupleDesc tupdesc; + HeapTuple tuple; + char *query, + *fieldval, + *fieldtype; + char *when; + int inserted; + int selected = 0; + int ret; + + if (!CALLED_AS_TRIGGER(fcinfo)) + elog(ERROR, "funny_dup17: not fired by trigger manager"); + + tuple = trigdata->tg_trigtuple; + rel = trigdata->tg_relation; + tupdesc = rel->rd_att; + if (TRIGGER_FIRED_BEFORE(trigdata->tg_event)) + { + xid = &fd17b_xid; + level = &fd17b_level; + recursion = &fd17b_recursion; + when = "BEFORE"; + } + else + { + xid = &fd17a_xid; + level = &fd17a_level; + recursion = &fd17a_recursion; + when = "AFTER "; + } + + if (!TransactionIdIsCurrentTransactionId(*xid)) + { + *xid = GetCurrentTransactionId(); + *level = 0; + *recursion = true; + } + + if (*level == 17) + { + *recursion = false; + return PointerGetDatum(tuple); + } + + if (!(*recursion)) + return PointerGetDatum(tuple); + + (*level)++; + + SPI_connect(); + + fieldval = SPI_getvalue(tuple, tupdesc, 1); + fieldtype = SPI_gettype(tupdesc, 1); + + query = (char *) palloc(100 + NAMEDATALEN * 3 + + strlen(fieldval) + strlen(fieldtype)); + + sprintf(query, "insert into %s select * from %s where %s = '%s'::%s", + SPI_getrelname(rel), SPI_getrelname(rel), + SPI_fname(tupdesc, 1), + fieldval, fieldtype); + + if ((ret = SPI_exec(query, 0)) < 0) + elog(ERROR, "funny_dup17 (fired %s) on level %3d: SPI_exec (insert ...) returned %d", + when, *level, ret); + + inserted = SPI_processed; + + sprintf(query, "select count (*) from %s where %s = '%s'::%s", + SPI_getrelname(rel), + SPI_fname(tupdesc, 1), + fieldval, fieldtype); + + if ((ret = SPI_exec(query, 0)) < 0) + elog(ERROR, "funny_dup17 (fired %s) on level %3d: SPI_exec (select ...) returned %d", + when, *level, ret); + + if (SPI_processed > 0) + { + selected = DatumGetInt32(DirectFunctionCall1(int4in, + CStringGetDatum(SPI_getvalue( + SPI_tuptable->vals[0], + SPI_tuptable->tupdesc, + 1 + )))); + } + + elog(DEBUG4, "funny_dup17 (fired %s) on level %3d: %d/%d tuples inserted/selected", + when, *level, inserted, selected); + + SPI_finish(); + + (*level)--; + + if (*level == 0) + *xid = InvalidTransactionId; + + return PointerGetDatum(tuple); +} + + + +/* ttdummy and set_ttdummy */ +static SPIPlanPtr splan = NULL; +static bool ttoff = false; + +PG_FUNCTION_INFO_V1(ttdummy); +Datum ttdummy(PG_FUNCTION_ARGS) +{ + TriggerData *trigdata = (TriggerData *) fcinfo->context; + Trigger *trigger; /* to get trigger name */ + char **args; /* arguments */ + int attnum[2]; /* fnumbers of start/stop columns */ + Datum oldon, + oldoff; + Datum newon, + newoff; + Datum *cvals; /* column values */ + char *cnulls; /* column nulls */ + char *relname; /* triggered relation name */ + Relation rel; /* triggered relation */ + HeapTuple trigtuple; + HeapTuple newtuple = NULL; + HeapTuple rettuple; + TupleDesc tupdesc; /* tuple description */ + int natts; /* # of attributes */ + bool isnull; /* to know is some column NULL or not */ + int ret; + int i; + + if (!CALLED_AS_TRIGGER(fcinfo)) + elog(ERROR, "ttdummy: not fired by trigger manager"); + if (TRIGGER_FIRED_FOR_STATEMENT(trigdata->tg_event)) + elog(ERROR, "ttdummy: cannot process STATEMENT events"); + if (TRIGGER_FIRED_AFTER(trigdata->tg_event)) + elog(ERROR, "ttdummy: must be fired before event"); + if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event)) + elog(ERROR, "ttdummy: cannot process INSERT event"); + if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event)) + newtuple = trigdata->tg_newtuple; + + trigtuple = trigdata->tg_trigtuple; + + rel = trigdata->tg_relation; + relname = SPI_getrelname(rel); + + /* check if TT is OFF for this relation */ + if (ttoff) /* OFF - nothing to do */ + { + pfree(relname); + return PointerGetDatum((newtuple != NULL) ? newtuple : trigtuple); + } + + trigger = trigdata->tg_trigger; + + if (trigger->tgnargs != 2) + elog(ERROR, "ttdummy (%s): invalid (!= 2) number of arguments %d", + relname, trigger->tgnargs); + + args = trigger->tgargs; + tupdesc = rel->rd_att; + natts = tupdesc->natts; + + for (i = 0; i < 2; i++) + { + attnum[i] = SPI_fnumber(tupdesc, args[i]); + if (attnum[i] < 0) + elog(ERROR, "ttdummy (%s): there is no attribute %s", relname, args[i]); + if (SPI_gettypeid(tupdesc, attnum[i]) != INT4OID) + elog(ERROR, "ttdummy (%s): attributes %s and %s must be of abstime type", + relname, args[0], args[1]); + } + + oldon = SPI_getbinval(trigtuple, tupdesc, attnum[0], &isnull); + if (isnull) + elog(ERROR, "ttdummy (%s): %s must be NOT NULL", relname, args[0]); + + oldoff = SPI_getbinval(trigtuple, tupdesc, attnum[1], &isnull); + if (isnull) + elog(ERROR, "ttdummy (%s): %s must be NOT NULL", relname, args[1]); + + if (newtuple != NULL) /* UPDATE */ + { + newon = SPI_getbinval(newtuple, tupdesc, attnum[0], &isnull); + if (isnull) + elog(ERROR, "ttdummy (%s): %s must be NOT NULL", relname, args[0]); + newoff = SPI_getbinval(newtuple, tupdesc, attnum[1], &isnull); + if (isnull) + elog(ERROR, "ttdummy (%s): %s must be NOT NULL", relname, args[1]); + + if (oldon != newon || oldoff != newoff) + elog(ERROR, "ttdummy (%s): you cannot change %s and/or %s columns (use set_ttdummy)", + relname, args[0], args[1]); + + if (newoff != TTDUMMY_INFINITY) + { + pfree(relname); /* allocated in upper executor context */ + return PointerGetDatum(NULL); + } + } + else if (oldoff != TTDUMMY_INFINITY) /* DELETE */ + { + pfree(relname); + return PointerGetDatum(NULL); + } + + newoff = DirectFunctionCall1(nextval, CStringGetTextDatum("ttdummy_seq")); + /* nextval now returns int64; coerce down to int32 */ + newoff = Int32GetDatum((int32) DatumGetInt64(newoff)); + + /* Connect to SPI manager */ + if ((ret = SPI_connect()) < 0) + elog(ERROR, "ttdummy (%s): SPI_connect returned %d", relname, ret); + + /* Fetch tuple values and nulls */ + cvals = (Datum *) palloc(natts * sizeof(Datum)); + cnulls = (char *) palloc(natts * sizeof(char)); + for (i = 0; i < natts; i++) + { + cvals[i] = SPI_getbinval((newtuple != NULL) ? newtuple : trigtuple, + tupdesc, i + 1, &isnull); + cnulls[i] = (isnull) ? 'n' : ' '; + } + + /* change date column(s) */ + if (newtuple) /* UPDATE */ + { + cvals[attnum[0] - 1] = newoff; /* start_date eq current date */ + cnulls[attnum[0] - 1] = ' '; + cvals[attnum[1] - 1] = TTDUMMY_INFINITY; /* stop_date eq INFINITY */ + cnulls[attnum[1] - 1] = ' '; + } + else + /* DELETE */ + { + cvals[attnum[1] - 1] = newoff; /* stop_date eq current date */ + cnulls[attnum[1] - 1] = ' '; + } + + /* if there is no plan ... */ + if (splan == NULL) + { + SPIPlanPtr pplan; + Oid *ctypes; + char *query; + + /* allocate space in preparation */ + ctypes = (Oid *) palloc(natts * sizeof(Oid)); + query = (char *) palloc(100 + 16 * natts); + + /* + * Construct query: INSERT INTO _relation_ VALUES ($1, ...) + */ + sprintf(query, "INSERT INTO %s VALUES (", relname); + for (i = 1; i <= natts; i++) + { + sprintf(query + strlen(query), "$%d%s", + i, (i < natts) ? ", " : ")"); + ctypes[i - 1] = SPI_gettypeid(tupdesc, i); + } + + /* Prepare plan for query */ + pplan = SPI_prepare(query, natts, ctypes); + if (pplan == NULL) + elog(ERROR, "ttdummy (%s): SPI_prepare returned %d", relname, SPI_result); + + pplan = SPI_saveplan(pplan); + if (pplan == NULL) + elog(ERROR, "ttdummy (%s): SPI_saveplan returned %d", relname, SPI_result); + + splan = pplan; + } + + ret = SPI_execp(splan, cvals, cnulls, 0); + + if (ret < 0) + elog(ERROR, "ttdummy (%s): SPI_execp returned %d", relname, ret); + + /* Tuple to return to upper Executor ... */ + if (newtuple) /* UPDATE */ + { + HeapTuple tmptuple; + + tmptuple = SPI_copytuple(trigtuple); + rettuple = SPI_modifytuple(rel, tmptuple, 1, &(attnum[1]), &newoff, NULL); + SPI_freetuple(tmptuple); + } + else + /* DELETE */ + rettuple = trigtuple; + + SPI_finish(); /* don't forget say Bye to SPI mgr */ + + pfree(relname); + + return PointerGetDatum(rettuple); +} + + +PG_FUNCTION_INFO_V1(set_ttdummy); +Datum set_ttdummy(PG_FUNCTION_ARGS) +{ + int32 on = PG_GETARG_INT32(0); + + if (ttoff) /* OFF currently */ + { + if (on == 0) + PG_RETURN_INT32(0); + + /* turn ON */ + ttoff = false; + PG_RETURN_INT32(0); + } + + /* ON currently */ + if (on != 0) + PG_RETURN_INT32(1); + + /* turn OFF */ + ttoff = true; + + PG_RETURN_INT32(1); +}
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/49fd529a/src/test/feature/UDF/sql/function_basics.sql ---------------------------------------------------------------------- diff --git a/src/test/feature/UDF/sql/function_basics.sql b/src/test/feature/UDF/sql/function_basics.sql new file mode 100755 index 0000000..a050b22 --- /dev/null +++ b/src/test/feature/UDF/sql/function_basics.sql @@ -0,0 +1,439 @@ +-- SETUP +DROP TABLE IF EXISTS foo; +CREATE TABLE foo AS SELECT * FROM generate_series(1, 10) x; +CREATE FUNCTION f(x INT) RETURNS INT AS $$ +BEGIN +RETURN x; +END +$$ LANGUAGE PLPGSQL; + + + +-- DDL, CREATE FUNCTION +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT; +SELECT proname FROM pg_proc WHERE proname = 'g'; +SELECT proname FROM gp_dist_random('pg_proc') WHERE proname = 'g'; +DROP FUNCTION g(int); + + + +-- DDL, CREATE OR REPLACE FUNCTION +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT; +SELECT proname FROM pg_proc WHERE proname = 'g'; +SELECT proname FROM gp_dist_random('pg_proc') WHERE proname = 'g'; +CREATE OR REPLACE FUNCTION g(x INT) RETURNS INT AS $$ +BEGIN +RETURN (-1) * x; +END +$$ LANGUAGE PLPGSQL; +SELECT proname, prosrc FROM pg_proc WHERE proname = 'g'; +SELECT proname, prosrc FROM gp_dist_random('pg_proc') WHERE proname = 'g'; +DROP FUNCTION g(int); + + + +-- DDL, DROP FUNCTION +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT; +DROP FUNCTION g(int); +SELECT oid, proname FROM pg_proc WHERE proname = 'g'; +SELECT oid, proname FROM gp_dist_random('pg_proc') WHERE proname = 'g'; + + + +-- DDL, DROP FUNCTION, NEGATIVE +DROP FUNCTION g(int); + + + +-- DDL, CREATE FUNCTION, RECORD +CREATE FUNCTION foo(int) RETURNS record AS 'SELECT * FROM foo WHERE x=$1' LANGUAGE SQL; +SELECT foo(5); +DROP FUNCTION foo(int); +CREATE FUNCTION foo(int) RETURNS foo AS 'SELECT * FROM foo WHERE x=$1' LANGUAGE SQL; +SELECT foo(5); +DROP FUNCTION foo(int); + + + +-- DDL, CREATE FUNCTION, SRF +CREATE FUNCTION g(x setof int) RETURNS INT + AS $$ SELECT 1 $$ LANGUAGE SQL; +DROP FUNCTION g(setof int); +CREATE FUNCTION g() RETURNS setof INT + AS $$ SELECT 1 $$ LANGUAGE SQL; +DROP FUNCTION g(); + + + +-- DDL, CREATE FUNCTION, TABLE, NEGATIVE +CREATE FUNCTION g() RETURNS TABLE(x int) + AS $$ SELECT * FROM foo $$ LANGUAGE SQL; +DROP FUNCTION g(); +CREATE FUNCTION g(anytable) RETURNS int + AS 'does_not_exist', 'does_not_exist' LANGUAGE C; + + + +-- DDL, CREATE FUNCTION, SECURITY DEFINER +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE SECURITY DEFINER; +DROP FUNCTION g(int); + + +-- DDL, ALTER FUNCTION +-- DDL, STRICT +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE; +SELECT g(NULL); +ALTER FUNCTION g(int) STRICT; +SELECT g(NULL); +DROP FUNCTION g(int); + + + +-- DDL, ALTER FUNCTION, OWNER +CREATE ROLE superuser SUPERUSER; +CREATE ROLE u1; +SET ROLE superuser; +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE; +SELECT a.rolname FROM pg_proc p, pg_authid a where p.proowner = a.oid and proname = 'g'; +ALTER FUNCTION g(int) OWNER TO u1; +SELECT a.rolname FROM pg_proc p, pg_authid a where p.proowner = a.oid and proname = 'g'; +DROP FUNCTION g(int); +RESET ROLE; +DROP ROLE u1; +DROP ROLE superuser; + + + +-- DDL, ALTER FUNCTION, RENAME +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE; +SELECT g(0); +ALTER FUNCTION g(int) RENAME TO h; +SELECT h(0); +DROP FUNCTION h(int); + + + +-- DDL, ALTER FUNCTION, SET SCHEMA +CREATE SCHEMA bar; +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE; +SELECT g(0); +ALTER FUNCTION g(int) SET SCHEMA bar; +SELECT bar.g(0); +DROP SCHEMA bar CASCADE; + + + +-- DDL, ALTER FUNCTION, SECURITY DEFINER +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE; +ALTER FUNCTION g(int) SECURITY DEFINER; +DROP FUNCTION g(int); + + + +-- DCL, GRANT/REVOKE +-- GRANT { EXECUTE | ALL [ PRIVILEGES ] } +-- ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...] +-- TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ] +-- REVOKE [ GRANT OPTION FOR ] +-- { EXECUTE | ALL [ PRIVILEGES ] } +-- ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...] +-- FROM { username | GROUP groupname | PUBLIC } [, ...] +-- [ CASCADE | RESTRICT ] + +-- DCL, GRANT/REVOKE, EXECUTE +CREATE ROLE superuser SUPERUSER; +SET ROLE superuser; +CREATE ROLE u1; +GRANT SELECT ON TABLE foo TO u1; +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT; +SELECT proacl FROM pg_proc where proname = 'g'; +REVOKE ALL ON FUNCTION g(int) FROM PUBLIC; +SELECT proacl FROM pg_proc where proname = 'g'; +SELECT g(1); +SELECT count(g(x)) FROM foo; +SET ROLE u1; +SELECT g(1); +SELECT count(g(x)) FROM foo; +SET ROLE superuser; +GRANT EXECUTE ON FUNCTION g(int) TO u1; +SELECT proacl FROM pg_proc where proname = 'g'; +SET ROLE u1; +SELECT g(1); +SELECT count(g(x)) FROM foo; +SET ROLE superuser; +REVOKE EXECUTE ON FUNCTION g(int) FROM u1; +SELECT proacl FROM pg_proc where proname = 'g'; +SET ROLE u1; +SELECT g(1); +SELECT count(g(x)) FROM foo; +RESET ROLE; +DROP FUNCTION g(int); +REVOKE SELECT ON TABLE foo FROM u1; +DROP ROLE u1; +DROP ROLE superuser; + + + +-- DCL, GRANT/REVOKE, PUBLIC +CREATE ROLE superuser SUPERUSER; +SET ROLE superuser; +CREATE ROLE u1; +GRANT SELECT ON TABLE foo TO u1; +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT; +SELECT proacl FROM pg_proc where proname = 'g'; +REVOKE ALL ON FUNCTION g(int) FROM PUBLIC; +SELECT proacl FROM pg_proc where proname = 'g'; +SELECT g(1); +SELECT count(g(x)) FROM foo; +SET ROLE u1; +SELECT g(1); +SELECT count(g(x)) FROM foo; +SET ROLE superuser; +GRANT EXECUTE ON FUNCTION g(int) TO PUBLIC; +SELECT proacl FROM pg_proc where proname = 'g'; +SET ROLE u1; +SELECT g(1); +SELECT count(g(x)) FROM foo; +SET ROLE superuser; +REVOKE EXECUTE ON FUNCTION g(int) FROM PUBLIC; +SELECT proacl FROM pg_proc where proname = 'g'; +SET ROLE u1; +SELECT g(1); +SELECT count(g(x)) FROM foo; +RESET ROLE; +DROP FUNCTION g(int); +REVOKE SELECT ON TABLE foo FROM u1; +DROP ROLE u1; +DROP ROLE superuser; + + + +-- DCL, GRANT/REVOKE, Groups +CREATE ROLE superuser SUPERUSER; +SET ROLE superuser; +CREATE ROLE u1; +CREATE ROLE u2 IN GROUP u1; +GRANT SELECT ON TABLE foo TO u1; +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT; +SELECT proacl FROM pg_proc where proname = 'g'; +REVOKE ALL ON FUNCTION g(int) FROM PUBLIC; +SELECT proacl FROM pg_proc where proname = 'g'; +SELECT g(1); +SELECT count(g(x)) FROM foo; +SET ROLE u2; +SELECT g(1); +SELECT count(g(x)) FROM foo; +SET ROLE superuser; +GRANT EXECUTE ON FUNCTION g(int) TO u1; +SELECT proacl FROM pg_proc where proname = 'g'; +SET ROLE u2; +SELECT g(1); +SELECT count(g(x)) FROM foo; +SET ROLE superuser; +REVOKE EXECUTE ON FUNCTION g(int) FROM u1; +SELECT proacl FROM pg_proc where proname = 'g'; +SET ROLE u2; +SELECT g(1); +SELECT count(g(x)) FROM foo; +RESET ROLE; +DROP FUNCTION g(int); +REVOKE SELECT ON TABLE foo FROM u1; +DROP ROLE u1; +DROP ROLE u2; +DROP ROLE superuser; + + + +-- DCL, GRANT/REVOKE, WITH GRANT OPTION +CREATE ROLE superuser SUPERUSER; +SET ROLE superuser; +CREATE ROLE u1; +CREATE ROLE u2; +GRANT SELECT ON TABLE foo TO PUBLIC; +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT; +SELECT proacl FROM pg_proc where proname = 'g'; +REVOKE ALL ON FUNCTION g(int) FROM PUBLIC; +SELECT proacl FROM pg_proc where proname = 'g'; +SELECT g(1); +SELECT count(g(x)) FROM foo; +SET ROLE u2; +SELECT g(1); +SELECT count(g(x)) FROM foo; +SET ROLE superuser; +GRANT ALL ON FUNCTION g(int) TO u1 WITH GRANT OPTION; +SET ROLE u1; +GRANT ALL ON FUNCTION g(int) TO u2; +SELECT proacl FROM pg_proc where proname = 'g'; +SET ROLE u1; +SELECT g(1); +SELECT count(g(x)) FROM foo; +SET ROLE u2; +SELECT g(1); +SELECT count(g(x)) FROM foo; +SET ROLE superuser; +REVOKE ALL ON FUNCTION g(int) FROM u1 CASCADE; +SELECT proacl FROM pg_proc where proname = 'g'; +SET ROLE u1; +SELECT g(1); +SELECT count(g(x)) FROM foo; +SET ROLE u2; +SELECT g(1); +SELECT count(g(x)) FROM foo; +RESET ROLE; +DROP FUNCTION g(int); +REVOKE SELECT ON TABLE foo FROM PUBLIC; +DROP ROLE u1; +DROP ROLE u2; +DROP ROLE superuser; + + + +-- DML, CaseExpr +SELECT CASE WHEN x % 2 = 0 THEN f(x) ELSE 0 END FROM foo ORDER BY x; + + + +-- DML, OpExpr +SELECT f(x) + f(x) FROM foo ORDER BY x; +SELECT f(x) + f(x) + f(x) FROM foo ORDER BY x; +SELECT f(x) + f(x) - f(x) FROM foo ORDER BY x; + + + +-- DML, FuncExpr +CREATE FUNCTION g(x INT) RETURNS INT AS $$ +BEGIN +RETURN x; +END +$$ LANGUAGE PLPGSQL; +SELECT g(f(x)) FROM foo ORDER BY x; +DROP FUNCTION g(int); + +-- DML, BoolExpr +SELECT x % 2 = 0 AND f(x) % 2 = 1 FROM foo ORDER BY x; + + + +-- DML, DistinctExpr +SELECT x IS DISTINCT FROM f(x) from foo ORDER BY x; + + + +-- DML, PercentileExpr +SELECT MEDIAN(f(x)) FROM foo; + + + +-- DML, Complex Expression +CREATE FUNCTION g(x INT) RETURNS INT AS $$ +BEGIN +RETURN x; +END +$$ LANGUAGE PLPGSQL; +SELECT CASE + WHEN x % 2 = 0 THEN g(g(x)) + g(g(x)) + WHEN f(x) % 2 = 1 THEN g(g(x)) - g(g(x)) + END FROM foo ORDER BY x; +DROP FUNCTION g(int); + + + +-- DML, Qual +SELECT x FROM foo WHERE f(x) % 2 = 0 ORDER BY x; + + + +-- DML, FROM +SELECT * FROM f(5); + + + +-- DML, Grouping +SELECT DISTINCT f(x) FROM foo ORDER BY f(x); +SELECT f(x) FROM foo GROUP BY f(x) ORDER BY f(x); + + + +-- DML, Join +SELECT a.x FROM foo a, foo b WHERE f(a.x) = f(b.x) ORDER BY x; +SELECT a.x FROM foo a JOIN foo b ON f(a.x) = f(b.x) ORDER BY x; + + + +-- DML, Windowing +SELECT avg(x) OVER (PARTITION BY f(x)) FROM foo ORDER BY x; + + + +-- DML, CTE +WITH t AS (SELECT x from foo) + SELECT f(x) from t ORDER BY x; + + + +-- DML, InitPlan +SELECT UNNEST(ARRAY(SELECT x FROM foo)) ORDER BY 1; +SELECT UNNEST(ARRAY(SELECT f(1))); + + + +-- PROPERTIES, VOLATILITY, IMMUTABLE +CREATE FUNCTION g() RETURNS float AS 'SELECT random();' LANGUAGE SQL IMMUTABLE; +SELECT COUNT(DISTINCT(g())) > 1 FROM foo; +DROP FUNCTION g(); + + + +-- PROPERTIES, VOLATILITY, STABLE +CREATE FUNCTION g() RETURNS float AS 'SELECT random();' LANGUAGE SQL STABLE; +SELECT COUNT(DISTINCT(g())) > 1 FROM foo; +DROP FUNCTION g(); + + + +-- PROPERTIES, VOLATILITY, VOLATILE +CREATE FUNCTION g() RETURNS float AS 'SELECT random();' LANGUAGE SQL VOLATILE; +SELECT COUNT(DISTINCT(g())) > 1 FROM foo; +DROP FUNCTION g(); + +----------------- +-- NEGATIVE TESTS +----------------- +SELECT h(1); +-- DML, InitPlan +SELECT UNNEST(ARRAY(SELECT f(x) from foo)); + +-- LANGUAGES not yet supported +-- CREATE LANGUAGE plr; +-- CREATE LANGUAGE plpython; +-- CREATE LANGUAGE pljava; +-- CREATE LANGUAGE plperl; + +-- NESTED FUNCTION +CREATE FUNCTION inner(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE; +CREATE FUNCTION outer(x INT) RETURNS INT AS $$ +BEGIN +RETURN inner(x); +END +$$ LANGUAGE PLPGSQL; +SELECT outer(0); +SELECT outer(0) FROM foo; +DROP FUNCTION outer(int); +DROP FUNCTION inner(int); + + + +-- TEARDOWN +DROP TABLE foo; + + + +-- HAWQ-510 +drop table if exists testEntryDB; +create table testEntryDB(key int, value int) distributed randomly; +insert into testEntryDB values(1, 0); +select t2.key, t2.value +from (select key, value from testEntryDB where value = 0) as t1, + (select generate_series(1,2)::int as key, 0::int as value) as t2 +where t1.value=t2.value; +drop table testEntryDB; http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/49fd529a/src/test/feature/UDF/sql/function_creation.sql.source ---------------------------------------------------------------------- diff --git a/src/test/feature/UDF/sql/function_creation.sql.source b/src/test/feature/UDF/sql/function_creation.sql.source new file mode 100755 index 0000000..226044e --- /dev/null +++ b/src/test/feature/UDF/sql/function_creation.sql.source @@ -0,0 +1,79 @@ +-- +-- CREATE_FUNCTION +-- + +CREATE FUNCTION widget_in(cstring) + RETURNS widget + AS '@SHARE_LIBRARY_PATH@' + LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION widget_out(widget) + RETURNS cstring + AS '@SHARE_LIBRARY_PATH@' + LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION int44in(cstring) + RETURNS city_budget + AS '@SHARE_LIBRARY_PATH@' + LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION int44out(city_budget) + RETURNS cstring + AS '@SHARE_LIBRARY_PATH@' + LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION check_primary_key () + RETURNS trigger + AS '@SHARE_LIBRARY_PATH@' + LANGUAGE C; + +CREATE FUNCTION check_foreign_key () + RETURNS trigger + AS '@SHARE_LIBRARY_PATH@' + LANGUAGE C; + +CREATE FUNCTION autoinc () + RETURNS trigger + AS '@SHARE_LIBRARY_PATH@' + LANGUAGE C; + +CREATE FUNCTION funny_dup17 () + RETURNS trigger + AS '@SHARE_LIBRARY_PATH@' + LANGUAGE C; + +CREATE FUNCTION ttdummy () + RETURNS trigger + AS '@SHARE_LIBRARY_PATH@' + LANGUAGE C; + +CREATE FUNCTION set_ttdummy (int4) + RETURNS int4 + AS '@SHARE_LIBRARY_PATH@' + LANGUAGE C STRICT; + +-- Things that shouldn't work: + +CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL + AS 'SELECT ''not an integer'';'; + +CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL + AS 'not even SQL'; + +CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL + AS 'SELECT 1, 2, 3;'; + +CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL + AS 'SELECT $2;'; + +CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL + AS 'a', 'b'; + +CREATE FUNCTION test1 (int) RETURNS int LANGUAGE C + AS 'nosuchfile'; + +CREATE FUNCTION test1 (int) RETURNS int LANGUAGE C + AS '@SHARE_LIBRARY_PATH@', 'nosuchsymbol'; + +CREATE FUNCTION test1 (int) RETURNS int LANGUAGE internal + AS 'nosuch'; http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/49fd529a/src/test/feature/UDF/sql/function_extension.sql ---------------------------------------------------------------------- diff --git a/src/test/feature/UDF/sql/function_extension.sql b/src/test/feature/UDF/sql/function_extension.sql new file mode 100755 index 0000000..d3d2abc --- /dev/null +++ b/src/test/feature/UDF/sql/function_extension.sql @@ -0,0 +1,123 @@ +-- ----------------------------------------------------------------- +-- Test extensions to functions (MPP-16060) +-- 1. data access indicators +-- ----------------------------------------------------------------- + +-- test prodataaccess +create function func1(int, int) returns int as +$$ + select $1 + $2; +$$ language sql immutable contains sql; + +-- check prodataaccess column in pg_proc +select proname, prodataaccess from pg_proc where proname = 'func1'; + +-- check prodataaccess in pg_attribute +select relname, attname, attlen from pg_class c, pg_attribute +where attname = 'prodataaccess' and attrelid = c.oid and c.relname = 'pg_proc'; + +create function func2(a anyelement, b anyelement, flag bool) +returns anyelement as +$$ + select $1 + $2; +$$ language sql reads sql data; + +-- check prodataaccess column in pg_proc +select proname, prodataaccess from pg_proc where proname = 'func2'; + +create function func3() returns oid as +$$ + select oid from pg_class where relname = 'pg_type'; +$$ language sql modifies sql data volatile; + +-- check prodataaccess column in pg_proc +select proname, prodataaccess from pg_proc where proname = 'func3'; + +-- check default value of prodataaccess +drop function func1(int, int); +create function func1(int, int) returns varchar as $$ +declare + v_name varchar(20) DEFAULT 'zzzzz'; +begin + select relname from pg_class into v_name where oid=$1; + return v_name; +end; +$$ language plpgsql; + +select proname, proargnames, prodataaccess from pg_proc where proname = 'func1'; + +create function func4(int, int) returns int as +$$ + select $1 + $2; +$$ language sql; + +-- check prodataaccess column +select proname, proargnames, prodataaccess from pg_proc where proname = 'func4'; + +-- change prodataaccess option +create or replace function func4(int, int) returns int as +$$ + select $1 + $2; +$$ language sql modifies sql data; + +select proname, proargnames, prodataaccess from pg_proc where proname = 'func4'; + +-- upper case language name +create or replace function func5(int) returns int as +$$ + select $1; +$$ language "SQL"; + +-- check prodataaccess column +select proname, proargnames, prodataaccess from pg_proc where proname = 'func5'; + +-- alter function with data access +alter function func5(int) reads sql data; + +-- check prodataaccess column +select proname, proargnames, prodataaccess from pg_proc where proname = 'func5'; + +-- alter function with data access +alter function func5(int) modifies sql data; + +-- check prodataaccess column +select proname, proargnames, prodataaccess from pg_proc where proname = 'func5'; + +-- alter function with data access +alter function func5(int) no sql; + +-- alter function with data access +alter function func5(int) volatile contains sql; + +alter function func5(int) immutable reads sql data; +alter function func5(int) immutable modifies sql data; + +-- data_access indicators for plpgsql +drop function func1(int, int); +create or replace function func1(int, int) returns varchar as $$ +declare + v_name varchar(20) DEFAULT 'zzzzz'; +begin + select relname from pg_class into v_name where oid=$1; + return v_name; +end; +$$ language plpgsql reads sql data; + +select proname, proargnames, prodataaccess from pg_proc where proname = 'func1'; + +-- check conflicts +drop function func1(int, int); +create function func1(int, int) returns int as +$$ + select $1 + $2; +$$ language sql immutable no sql; + +create function func1(int, int) returns int as +$$ + select $1 + $2; +$$ language sql immutable reads sql data; + +drop function func2(anyelement, anyelement, bool); +drop function func3(); +drop function func4(int, int); +drop function func5(int); http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/49fd529a/src/test/feature/UDF/sql/function_set_returning.sql ---------------------------------------------------------------------- diff --git a/src/test/feature/UDF/sql/function_set_returning.sql b/src/test/feature/UDF/sql/function_set_returning.sql new file mode 100755 index 0000000..3beb31f --- /dev/null +++ b/src/test/feature/UDF/sql/function_set_returning.sql @@ -0,0 +1,93 @@ +DROP LANGUAGE IF EXISTS plpythonu CASCADE; +CREATE LANGUAGE plpythonu; + +CREATE TABLE foo2(fooid int, f2 int); +INSERT INTO foo2 VALUES(1, 11); +INSERT INTO foo2 VALUES(2, 22); +INSERT INTO foo2 VALUES(1, 111); + +CREATE FUNCTION foot(int) returns setof foo2 as 'SELECT * FROM foo2 WHERE fooid = $1;' LANGUAGE SQL; +select foot.fooid, foot.f2 from foot(sin(pi()/2)::int) ORDER BY 1,2; + +CREATE TABLE foo (fooid int, foosubid int, fooname text); +INSERT INTO foo VALUES(1,1,'Joe'); +INSERT INTO foo VALUES(1,2,'Ed'); +INSERT INTO foo VALUES(2,1,'Mary'); + +CREATE FUNCTION getfoo(int) RETURNS setof int AS 'SELECT fooid FROM foo WHERE fooid = $1;' LANGUAGE SQL; +SELECT * FROM getfoo(1) AS t1; +CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1); +SELECT * FROM vw_getfoo; + +DROP VIEW vw_getfoo; +DROP FUNCTION getfoo(int); +DROP FUNCTION foot(int); +DROP TABLE foo2; +DROP TABLE foo; + +-- setof as a paramater -- +CREATE TYPE numtype as (i int, j int); + +CREATE FUNCTION g_numtype(x setof numtype) RETURNS setof numtype AS $$ select $1; $$ LANGUAGE SQL; + +DROP FUNCTION g_numtype(x setof numtype); +DROP TYPE numtype; + +-- +-- Set functions samples from Madlib +-- +create function combination(s text) returns setof text[] as $$ +x = s.split(',') + +def subset(myset, N): + left = [] + right = [] + for i in range(0, len(myset)): + if ((1 << i) & N) > 0: + left.append(myset[i]) + else: + right.append(myset[i]) + return (', '.join(left), ', '.join(right)) + +for i in range(1, (1 << len(x)) - 2): + yield subset(x, i) +$$ language plpythonu strict; + +select x[1] || ' => ' || x[2] from combination('a,b,c,d') x; + +CREATE TABLE rules(rule text) distributed by (rule); +insert into rules values('a,b,c'); +insert into rules values('d,e'); +insert into rules values('f,g,h,i,j'); +insert into rules values('k,l,m'); + +SELECT rule, combination(rule) from rules order by 1,2; + +DROP TABLE IF EXISTS foo; +CREATE TABLE foo AS SELECT rule, combination(rule) from rules distributed by (rule); + + +-- UDT as argument/return type of set returning UDF +CREATE TYPE r_type as (a int, b text); + +CREATE FUNCTION f1(x r_type) returns setof text as $$ SELECT $1.b from generate_series(1, $1.a) $$ language sql; +CREATE FUNCTION f2(x int) returns setof r_type as $$ SELECT i, 'hello'::text from generate_series(1, $1) i $$ language sql; +CREATE FUNCTION f3(x r_type) returns setof r_type as $$ SELECT $1 from generate_series(1, $1.a) $$ language sql; + +SELECT f1(row(2, 'hello')); +SELECT f2(2); +SELECT f3(row(2,'hello')); + +SELECT * FROM f1(row(2,'hello')); +SELECT * FROM f2(2); +SELECT * FROM f3(row(2,'hello')); + +CREATE TABLE t1 as SELECT i from generate_series(1,5) i distributed by (i); + +SELECT i, f1(row(i, 'hello')) from t1; +SELECT i, f2(i) from t1; +SELECT i, f3(row(i,'hello')) from t1; + +CREATE TABLE o1 as SELECT f1(row(i, 'hello')) from t1; +CREATE TABLE o2 as SELECT f2(i) from t1; +CREATE TABLE o3 as SELECT f3(row(i,'hello')) from t1; http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/49fd529a/src/test/feature/udf/TestUDF.cpp ---------------------------------------------------------------------- diff --git a/src/test/feature/udf/TestUDF.cpp b/src/test/feature/udf/TestUDF.cpp deleted file mode 100755 index fcd33ea..0000000 --- a/src/test/feature/udf/TestUDF.cpp +++ /dev/null @@ -1,32 +0,0 @@ -#include "gtest/gtest.h" - -#include "lib/sql_util.h" - - -class TestUDF: public ::testing::Test -{ - public: - TestUDF() {} - ~TestUDF() {} -}; - -TEST_F(TestUDF, TestUDFBasics) -{ - hawq::test::SQLUtility util; - util.execSQLFile("udf/sql/function_basics.sql", - "udf/ans/function_basics.ans"); -} - -TEST_F(TestUDF, TestUDFSetReturning) -{ - hawq::test::SQLUtility util; - util.execSQLFile("udf/sql/function_set_returning.sql", - "udf/ans/function_set_returning.ans"); -} - -TEST_F(TestUDF, TestUDFExtension) -{ - hawq::test::SQLUtility util; - util.execSQLFile("udf/sql/function_extension.sql", - "udf/ans/function_extension.ans"); -} http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/49fd529a/src/test/feature/udf/ans/function_basics.ans ---------------------------------------------------------------------- diff --git a/src/test/feature/udf/ans/function_basics.ans b/src/test/feature/udf/ans/function_basics.ans deleted file mode 100755 index ff45af2..0000000 --- a/src/test/feature/udf/ans/function_basics.ans +++ /dev/null @@ -1,1088 +0,0 @@ --- start_ignore -SET SEARCH_PATH=TestUDF_TestUDFBasics; -SET --- end_ignore --- SETUP -DROP TABLE IF EXISTS foo; -psql:/tmp/TestUDF_TestUDFBasics.sql:5: NOTICE: table "foo" does not exist, skipping -DROP TABLE -CREATE TABLE foo AS SELECT * FROM generate_series(1, 10) x; -SELECT 10 -CREATE FUNCTION f(x INT) RETURNS INT AS $$ -BEGIN -RETURN x; -END -$$ LANGUAGE PLPGSQL; -CREATE FUNCTION --- DDL, CREATE FUNCTION -CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT; -CREATE FUNCTION -SELECT proname FROM pg_proc WHERE proname = 'g'; - proname ---------- - g -(1 row) - -SELECT proname FROM gp_dist_random('pg_proc') WHERE proname = 'g'; - proname ---------- -(0 rows) - -DROP FUNCTION g(int); -DROP FUNCTION --- DDL, CREATE OR REPLACE FUNCTION -CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT; -CREATE FUNCTION -SELECT proname FROM pg_proc WHERE proname = 'g'; - proname ---------- - g -(1 row) - -SELECT proname FROM gp_dist_random('pg_proc') WHERE proname = 'g'; - proname ---------- -(0 rows) - -CREATE OR REPLACE FUNCTION g(x INT) RETURNS INT AS $$ -BEGIN -RETURN (-1) * x; -END -$$ LANGUAGE PLPGSQL; -CREATE FUNCTION -SELECT proname, prosrc FROM pg_proc WHERE proname = 'g'; - proname | prosrc ----------+------------------ - g | - : BEGIN - : RETURN (-1) * x; - : END - : -(1 row) - -SELECT proname, prosrc FROM gp_dist_random('pg_proc') WHERE proname = 'g'; - proname | prosrc ----------+-------- -(0 rows) - -DROP FUNCTION g(int); -DROP FUNCTION --- DDL, DROP FUNCTION -CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT; -CREATE FUNCTION -DROP FUNCTION g(int); -DROP FUNCTION -SELECT oid, proname FROM pg_proc WHERE proname = 'g'; - oid | proname ------+--------- -(0 rows) - -SELECT oid, proname FROM gp_dist_random('pg_proc') WHERE proname = 'g'; - oid | proname ------+--------- -(0 rows) - --- DDL, DROP FUNCTION, NEGATIVE -DROP FUNCTION g(int); -psql:/tmp/TestUDF_TestUDFBasics.sql:47: ERROR: function g(integer) does not exist --- DDL, CREATE FUNCTION, RECORD -CREATE FUNCTION foo(int) RETURNS record AS 'SELECT * FROM foo WHERE x=$1' LANGUAGE SQL; -CREATE FUNCTION -SELECT foo(5); - foo ------ - (5) -(1 row) - -DROP FUNCTION foo(int); -DROP FUNCTION -CREATE FUNCTION foo(int) RETURNS foo AS 'SELECT * FROM foo WHERE x=$1' LANGUAGE SQL; -CREATE FUNCTION -SELECT foo(5); - foo ------ - (5) -(1 row) - -DROP FUNCTION foo(int); -DROP FUNCTION --- DDL, CREATE FUNCTION, SRF -CREATE FUNCTION g(x setof int) RETURNS INT - AS $$ SELECT 1 $$ LANGUAGE SQL; -CREATE FUNCTION -DROP FUNCTION g(setof int); -DROP FUNCTION -CREATE FUNCTION g() RETURNS setof INT - AS $$ SELECT 1 $$ LANGUAGE SQL; -CREATE FUNCTION -DROP FUNCTION g(); -DROP FUNCTION --- DDL, CREATE FUNCTION, TABLE, NEGATIVE -CREATE FUNCTION g() RETURNS TABLE(x int) - AS $$ SELECT * FROM foo $$ LANGUAGE SQL; -CREATE FUNCTION -DROP FUNCTION g(); -DROP FUNCTION -CREATE FUNCTION g(anytable) RETURNS int - AS 'does_not_exist', 'does_not_exist' LANGUAGE C; -psql:/tmp/TestUDF_TestUDFBasics.sql:76: ERROR: TABLE functions not supported --- DDL, CREATE FUNCTION, SECURITY DEFINER -CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE SECURITY DEFINER; -CREATE FUNCTION -DROP FUNCTION g(int); -DROP FUNCTION --- DDL, ALTER FUNCTION --- DDL, STRICT -CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE; -CREATE FUNCTION -SELECT g(NULL); - g ---- - 1 -(1 row) - -ALTER FUNCTION g(int) STRICT; -ALTER FUNCTION -SELECT g(NULL); - g ---- - -(1 row) - -DROP FUNCTION g(int); -DROP FUNCTION --- DDL, ALTER FUNCTION, OWNER -CREATE ROLE superuser SUPERUSER; -CREATE ROLE -CREATE ROLE u1; -psql:/tmp/TestUDF_TestUDFBasics.sql:97: NOTICE: resource queue required -- using default resource queue "pg_default" -CREATE ROLE -SET ROLE superuser; -SET -CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE; -CREATE FUNCTION -SELECT a.rolname FROM pg_proc p, pg_authid a where p.proowner = a.oid and proname = 'g'; - rolname ------------ - superuser -(1 row) - -ALTER FUNCTION g(int) OWNER TO u1; -ALTER FUNCTION -SELECT a.rolname FROM pg_proc p, pg_authid a where p.proowner = a.oid and proname = 'g'; - rolname ---------- - u1 -(1 row) - -DROP FUNCTION g(int); -DROP FUNCTION -RESET ROLE; -RESET -DROP ROLE u1; -DROP ROLE -DROP ROLE superuser; -DROP ROLE --- DDL, ALTER FUNCTION, RENAME -CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE; -CREATE FUNCTION -SELECT g(0); - g ---- - 1 -(1 row) - -ALTER FUNCTION g(int) RENAME TO h; -ALTER FUNCTION -SELECT h(0); - h ---- - 1 -(1 row) - -DROP FUNCTION h(int); -DROP FUNCTION --- DDL, ALTER FUNCTION, SET SCHEMA -CREATE SCHEMA bar; -CREATE SCHEMA -CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE; -CREATE FUNCTION -SELECT g(0); - g ---- - 1 -(1 row) - -ALTER FUNCTION g(int) SET SCHEMA bar; -ALTER FUNCTION -SELECT bar.g(0); - g ---- - 1 -(1 row) - -DROP SCHEMA bar CASCADE; -psql:/tmp/TestUDF_TestUDFBasics.sql:125: NOTICE: drop cascades to function bar.g(integer) -DROP SCHEMA --- DDL, ALTER FUNCTION, SECURITY DEFINER -CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE; -CREATE FUNCTION -ALTER FUNCTION g(int) SECURITY DEFINER; -ALTER FUNCTION -DROP FUNCTION g(int); -DROP FUNCTION --- DCL, GRANT/REVOKE --- GRANT { EXECUTE | ALL [ PRIVILEGES ] } --- ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...] --- TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ] --- REVOKE [ GRANT OPTION FOR ] --- { EXECUTE | ALL [ PRIVILEGES ] } --- ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...] --- FROM { username | GROUP groupname | PUBLIC } [, ...] --- [ CASCADE | RESTRICT ] --- DCL, GRANT/REVOKE, EXECUTE -CREATE ROLE superuser SUPERUSER; -CREATE ROLE -SET ROLE superuser; -SET -CREATE ROLE u1; -psql:/tmp/TestUDF_TestUDFBasics.sql:149: NOTICE: resource queue required -- using default resource queue "pg_default" -CREATE ROLE -GRANT SELECT ON TABLE foo TO u1; -GRANT -CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT; -CREATE FUNCTION -SELECT proacl FROM pg_proc where proname = 'g'; - proacl --------- - -(1 row) - -REVOKE ALL ON FUNCTION g(int) FROM PUBLIC; -REVOKE -SELECT proacl FROM pg_proc where proname = 'g'; - proacl -------------------------- - {superuser=X/superuser} -(1 row) - -SELECT g(1); - g ---- - 1 -(1 row) - -SELECT count(g(x)) FROM foo; - count -------- - 10 -(1 row) - -SET ROLE u1; -SET -SELECT g(1); -psql:/tmp/TestUDF_TestUDFBasics.sql:158: ERROR: function g(integer) does not exist -LINE 1: SELECT g(1); - ^ -HINT: No function matches the given name and argument types. You may need to add explicit type casts. -SELECT count(g(x)) FROM foo; -psql:/tmp/TestUDF_TestUDFBasics.sql:159: ERROR: relation "foo" does not exist -LINE 1: SELECT count(g(x)) FROM foo; - ^ -SET ROLE superuser; -SET -GRANT EXECUTE ON FUNCTION g(int) TO u1; -GRANT -SELECT proacl FROM pg_proc where proname = 'g'; - proacl ----------------------------------------- - {superuser=X/superuser,u1=X/superuser} -(1 row) - -SET ROLE u1; -SET -SELECT g(1); -psql:/tmp/TestUDF_TestUDFBasics.sql:164: ERROR: function g(integer) does not exist -LINE 1: SELECT g(1); - ^ -HINT: No function matches the given name and argument types. You may need to add explicit type casts. -SELECT count(g(x)) FROM foo; -psql:/tmp/TestUDF_TestUDFBasics.sql:165: ERROR: relation "foo" does not exist -LINE 1: SELECT count(g(x)) FROM foo; - ^ -SET ROLE superuser; -SET -REVOKE EXECUTE ON FUNCTION g(int) FROM u1; -REVOKE -SELECT proacl FROM pg_proc where proname = 'g'; - proacl -------------------------- - {superuser=X/superuser} -(1 row) - -SET ROLE u1; -SET -SELECT g(1); -psql:/tmp/TestUDF_TestUDFBasics.sql:170: ERROR: function g(integer) does not exist -LINE 1: SELECT g(1); - ^ -HINT: No function matches the given name and argument types. You may need to add explicit type casts. -SELECT count(g(x)) FROM foo; -psql:/tmp/TestUDF_TestUDFBasics.sql:171: ERROR: relation "foo" does not exist -LINE 1: SELECT count(g(x)) FROM foo; - ^ -RESET ROLE; -RESET -DROP FUNCTION g(int); -DROP FUNCTION -REVOKE SELECT ON TABLE foo FROM u1; -REVOKE -DROP ROLE u1; -DROP ROLE -DROP ROLE superuser; -DROP ROLE --- DCL, GRANT/REVOKE, PUBLIC -CREATE ROLE superuser SUPERUSER; -CREATE ROLE -SET ROLE superuser; -SET -CREATE ROLE u1; -psql:/tmp/TestUDF_TestUDFBasics.sql:183: NOTICE: resource queue required -- using default resource queue "pg_default" -CREATE ROLE -GRANT SELECT ON TABLE foo TO u1; -GRANT -CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT; -CREATE FUNCTION -SELECT proacl FROM pg_proc where proname = 'g'; - proacl --------- - -(1 row) - -REVOKE ALL ON FUNCTION g(int) FROM PUBLIC; -REVOKE -SELECT proacl FROM pg_proc where proname = 'g'; - proacl -------------------------- - {superuser=X/superuser} -(1 row) - -SELECT g(1); - g ---- - 1 -(1 row) - -SELECT count(g(x)) FROM foo; - count -------- - 10 -(1 row) - -SET ROLE u1; -SET -SELECT g(1); -psql:/tmp/TestUDF_TestUDFBasics.sql:192: ERROR: function g(integer) does not exist -LINE 1: SELECT g(1); - ^ -HINT: No function matches the given name and argument types. You may need to add explicit type casts. -SELECT count(g(x)) FROM foo; -psql:/tmp/TestUDF_TestUDFBasics.sql:193: ERROR: relation "foo" does not exist -LINE 1: SELECT count(g(x)) FROM foo; - ^ -SET ROLE superuser; -SET -GRANT EXECUTE ON FUNCTION g(int) TO PUBLIC; -GRANT -SELECT proacl FROM pg_proc where proname = 'g'; - proacl --------------------------------------- - {superuser=X/superuser,=X/superuser} -(1 row) - -SET ROLE u1; -SET -SELECT g(1); -psql:/tmp/TestUDF_TestUDFBasics.sql:198: ERROR: function g(integer) does not exist -LINE 1: SELECT g(1); - ^ -HINT: No function matches the given name and argument types. You may need to add explicit type casts. -SELECT count(g(x)) FROM foo; -psql:/tmp/TestUDF_TestUDFBasics.sql:199: ERROR: relation "foo" does not exist -LINE 1: SELECT count(g(x)) FROM foo; - ^ -SET ROLE superuser; -SET -REVOKE EXECUTE ON FUNCTION g(int) FROM PUBLIC; -REVOKE -SELECT proacl FROM pg_proc where proname = 'g'; - proacl -------------------------- - {superuser=X/superuser} -(1 row) - -SET ROLE u1; -SET -SELECT g(1); -psql:/tmp/TestUDF_TestUDFBasics.sql:204: ERROR: function g(integer) does not exist -LINE 1: SELECT g(1); - ^ -HINT: No function matches the given name and argument types. You may need to add explicit type casts. -SELECT count(g(x)) FROM foo; -psql:/tmp/TestUDF_TestUDFBasics.sql:205: ERROR: relation "foo" does not exist -LINE 1: SELECT count(g(x)) FROM foo; - ^ -RESET ROLE; -RESET -DROP FUNCTION g(int); -DROP FUNCTION -REVOKE SELECT ON TABLE foo FROM u1; -REVOKE -DROP ROLE u1; -DROP ROLE -DROP ROLE superuser; -DROP ROLE --- DCL, GRANT/REVOKE, Groups -CREATE ROLE superuser SUPERUSER; -CREATE ROLE -SET ROLE superuser; -SET -CREATE ROLE u1; -psql:/tmp/TestUDF_TestUDFBasics.sql:217: NOTICE: resource queue required -- using default resource queue "pg_default" -CREATE ROLE -CREATE ROLE u2 IN GROUP u1; -psql:/tmp/TestUDF_TestUDFBasics.sql:218: NOTICE: resource queue required -- using default resource queue "pg_default" -CREATE ROLE -GRANT SELECT ON TABLE foo TO u1; -GRANT -CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT; -CREATE FUNCTION -SELECT proacl FROM pg_proc where proname = 'g'; - proacl --------- - -(1 row) - -REVOKE ALL ON FUNCTION g(int) FROM PUBLIC; -REVOKE -SELECT proacl FROM pg_proc where proname = 'g'; - proacl -------------------------- - {superuser=X/superuser} -(1 row) - -SELECT g(1); - g ---- - 1 -(1 row) - -SELECT count(g(x)) FROM foo; - count -------- - 10 -(1 row) - -SET ROLE u2; -SET -SELECT g(1); -psql:/tmp/TestUDF_TestUDFBasics.sql:227: ERROR: function g(integer) does not exist -LINE 1: SELECT g(1); - ^ -HINT: No function matches the given name and argument types. You may need to add explicit type casts. -SELECT count(g(x)) FROM foo; -psql:/tmp/TestUDF_TestUDFBasics.sql:228: ERROR: relation "foo" does not exist -LINE 1: SELECT count(g(x)) FROM foo; - ^ -SET ROLE superuser; -SET -GRANT EXECUTE ON FUNCTION g(int) TO u1; -GRANT -SELECT proacl FROM pg_proc where proname = 'g'; - proacl ----------------------------------------- - {superuser=X/superuser,u1=X/superuser} -(1 row) - -SET ROLE u2; -SET -SELECT g(1); -psql:/tmp/TestUDF_TestUDFBasics.sql:233: ERROR: function g(integer) does not exist -LINE 1: SELECT g(1); - ^ -HINT: No function matches the given name and argument types. You may need to add explicit type casts. -SELECT count(g(x)) FROM foo; -psql:/tmp/TestUDF_TestUDFBasics.sql:234: ERROR: relation "foo" does not exist -LINE 1: SELECT count(g(x)) FROM foo; - ^ -SET ROLE superuser; -SET -REVOKE EXECUTE ON FUNCTION g(int) FROM u1; -REVOKE -SELECT proacl FROM pg_proc where proname = 'g'; - proacl -------------------------- - {superuser=X/superuser} -(1 row) - -SET ROLE u2; -SET -SELECT g(1); -psql:/tmp/TestUDF_TestUDFBasics.sql:239: ERROR: function g(integer) does not exist -LINE 1: SELECT g(1); - ^ -HINT: No function matches the given name and argument types. You may need to add explicit type casts. -SELECT count(g(x)) FROM foo; -psql:/tmp/TestUDF_TestUDFBasics.sql:240: ERROR: relation "foo" does not exist -LINE 1: SELECT count(g(x)) FROM foo; - ^ -RESET ROLE; -RESET -DROP FUNCTION g(int); -DROP FUNCTION -REVOKE SELECT ON TABLE foo FROM u1; -REVOKE -DROP ROLE u1; -DROP ROLE -DROP ROLE u2; -DROP ROLE -DROP ROLE superuser; -DROP ROLE --- DCL, GRANT/REVOKE, WITH GRANT OPTION -CREATE ROLE superuser SUPERUSER; -CREATE ROLE -SET ROLE superuser; -SET -CREATE ROLE u1; -psql:/tmp/TestUDF_TestUDFBasics.sql:253: NOTICE: resource queue required -- using default resource queue "pg_default" -CREATE ROLE -CREATE ROLE u2; -psql:/tmp/TestUDF_TestUDFBasics.sql:254: NOTICE: resource queue required -- using default resource queue "pg_default" -CREATE ROLE -GRANT SELECT ON TABLE foo TO PUBLIC; -GRANT -CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT; -CREATE FUNCTION -SELECT proacl FROM pg_proc where proname = 'g'; - proacl --------- - -(1 row) - -REVOKE ALL ON FUNCTION g(int) FROM PUBLIC; -REVOKE -SELECT proacl FROM pg_proc where proname = 'g'; - proacl -------------------------- - {superuser=X/superuser} -(1 row) - -SELECT g(1); - g ---- - 1 -(1 row) - -SELECT count(g(x)) FROM foo; - count -------- - 10 -(1 row) - -SET ROLE u2; -SET -SELECT g(1); -psql:/tmp/TestUDF_TestUDFBasics.sql:263: ERROR: function g(integer) does not exist -LINE 1: SELECT g(1); - ^ -HINT: No function matches the given name and argument types. You may need to add explicit type casts. -SELECT count(g(x)) FROM foo; -psql:/tmp/TestUDF_TestUDFBasics.sql:264: ERROR: relation "foo" does not exist -LINE 1: SELECT count(g(x)) FROM foo; - ^ -SET ROLE superuser; -SET -GRANT ALL ON FUNCTION g(int) TO u1 WITH GRANT OPTION; -GRANT -SET ROLE u1; -SET -GRANT ALL ON FUNCTION g(int) TO u2; -psql:/tmp/TestUDF_TestUDFBasics.sql:268: ERROR: function g(integer) does not exist -SELECT proacl FROM pg_proc where proname = 'g'; - proacl ------------------------------------------ - {superuser=X/superuser,u1=X*/superuser} -(1 row) - -SET ROLE u1; -SET -SELECT g(1); -psql:/tmp/TestUDF_TestUDFBasics.sql:271: ERROR: function g(integer) does not exist -LINE 1: SELECT g(1); - ^ -HINT: No function matches the given name and argument types. You may need to add explicit type casts. -SELECT count(g(x)) FROM foo; -psql:/tmp/TestUDF_TestUDFBasics.sql:272: ERROR: relation "foo" does not exist -LINE 1: SELECT count(g(x)) FROM foo; - ^ -SET ROLE u2; -SET -SELECT g(1); -psql:/tmp/TestUDF_TestUDFBasics.sql:274: ERROR: function g(integer) does not exist -LINE 1: SELECT g(1); - ^ -HINT: No function matches the given name and argument types. You may need to add explicit type casts. -SELECT count(g(x)) FROM foo; -psql:/tmp/TestUDF_TestUDFBasics.sql:275: ERROR: relation "foo" does not exist -LINE 1: SELECT count(g(x)) FROM foo; - ^ -SET ROLE superuser; -SET -REVOKE ALL ON FUNCTION g(int) FROM u1 CASCADE; -REVOKE -SELECT proacl FROM pg_proc where proname = 'g'; - proacl -------------------------- - {superuser=X/superuser} -(1 row) - -SET ROLE u1; -SET -SELECT g(1); -psql:/tmp/TestUDF_TestUDFBasics.sql:280: ERROR: function g(integer) does not exist -LINE 1: SELECT g(1); - ^ -HINT: No function matches the given name and argument types. You may need to add explicit type casts. -SELECT count(g(x)) FROM foo; -psql:/tmp/TestUDF_TestUDFBasics.sql:281: ERROR: relation "foo" does not exist -LINE 1: SELECT count(g(x)) FROM foo; - ^ -SET ROLE u2; -SET -SELECT g(1); -psql:/tmp/TestUDF_TestUDFBasics.sql:283: ERROR: function g(integer) does not exist -LINE 1: SELECT g(1); - ^ -HINT: No function matches the given name and argument types. You may need to add explicit type casts. -SELECT count(g(x)) FROM foo; -psql:/tmp/TestUDF_TestUDFBasics.sql:284: ERROR: relation "foo" does not exist -LINE 1: SELECT count(g(x)) FROM foo; - ^ -RESET ROLE; -RESET -DROP FUNCTION g(int); -DROP FUNCTION -REVOKE SELECT ON TABLE foo FROM PUBLIC; -REVOKE -DROP ROLE u1; -DROP ROLE -DROP ROLE u2; -DROP ROLE -DROP ROLE superuser; -DROP ROLE --- DML, CaseExpr -SELECT CASE WHEN x % 2 = 0 THEN f(x) ELSE 0 END FROM foo ORDER BY x; - case ------- - 0 - 2 - 0 - 4 - 0 - 6 - 0 - 8 - 0 - 10 -(10 rows) - --- DML, OpExpr -SELECT f(x) + f(x) FROM foo ORDER BY x; - ?column? ----------- - 2 - 4 - 6 - 8 - 10 - 12 - 14 - 16 - 18 - 20 -(10 rows) - -SELECT f(x) + f(x) + f(x) FROM foo ORDER BY x; - ?column? ----------- - 3 - 6 - 9 - 12 - 15 - 18 - 21 - 24 - 27 - 30 -(10 rows) - -SELECT f(x) + f(x) - f(x) FROM foo ORDER BY x; - ?column? ----------- - 1 - 2 - 3 - 4 - 5 - 6 - 7 - 8 - 9 - 10 -(10 rows) - --- DML, FuncExpr -CREATE FUNCTION g(x INT) RETURNS INT AS $$ -BEGIN -RETURN x; -END -$$ LANGUAGE PLPGSQL; -CREATE FUNCTION -SELECT g(f(x)) FROM foo ORDER BY x; - g ----- - 1 - 2 - 3 - 4 - 5 - 6 - 7 - 8 - 9 - 10 -(10 rows) - -DROP FUNCTION g(int); -DROP FUNCTION --- DML, BoolExpr -SELECT x % 2 = 0 AND f(x) % 2 = 1 FROM foo ORDER BY x; - ?column? ----------- - f - f - f - f - f - f - f - f - f - f -(10 rows) - --- DML, DistinctExpr -SELECT x IS DISTINCT FROM f(x) from foo ORDER BY x; - ?column? ----------- - f - f - f - f - f - f - f - f - f - f -(10 rows) - --- DML, PercentileExpr -SELECT MEDIAN(f(x)) FROM foo; - median --------- - 5.5 -(1 row) - --- DML, Complex Expression -CREATE FUNCTION g(x INT) RETURNS INT AS $$ -BEGIN -RETURN x; -END -$$ LANGUAGE PLPGSQL; -CREATE FUNCTION -SELECT CASE - WHEN x % 2 = 0 THEN g(g(x)) + g(g(x)) - WHEN f(x) % 2 = 1 THEN g(g(x)) - g(g(x)) - END FROM foo ORDER BY x; - case ------- - 0 - 4 - 0 - 8 - 0 - 12 - 0 - 16 - 0 - 20 -(10 rows) - -DROP FUNCTION g(int); -DROP FUNCTION --- DML, Qual -SELECT x FROM foo WHERE f(x) % 2 = 0 ORDER BY x; - x ----- - 2 - 4 - 6 - 8 - 10 -(5 rows) - --- DML, FROM -SELECT * FROM f(5); - f ---- - 5 -(1 row) - --- DML, Grouping -SELECT DISTINCT f(x) FROM foo ORDER BY f(x); - f ----- - 1 - 2 - 3 - 4 - 5 - 6 - 7 - 8 - 9 - 10 -(10 rows) - -SELECT f(x) FROM foo GROUP BY f(x) ORDER BY f(x); - f ----- - 1 - 2 - 3 - 4 - 5 - 6 - 7 - 8 - 9 - 10 -(10 rows) - --- DML, Join -SELECT a.x FROM foo a, foo b WHERE f(a.x) = f(b.x) ORDER BY x; - x ----- - 1 - 2 - 3 - 4 - 5 - 6 - 7 - 8 - 9 - 10 -(10 rows) - -SELECT a.x FROM foo a JOIN foo b ON f(a.x) = f(b.x) ORDER BY x; - x ----- - 1 - 2 - 3 - 4 - 5 - 6 - 7 - 8 - 9 - 10 -(10 rows) - --- DML, Windowing -SELECT avg(x) OVER (PARTITION BY f(x)) FROM foo ORDER BY x; - avg ------ - 1 - 2 - 3 - 4 - 5 - 6 - 7 - 8 - 9 - 10 -(10 rows) - --- DML, CTE -WITH t AS (SELECT x from foo) - SELECT f(x) from t ORDER BY x; - f ----- - 1 - 2 - 3 - 4 - 5 - 6 - 7 - 8 - 9 - 10 -(10 rows) - --- DML, InitPlan -SELECT UNNEST(ARRAY(SELECT x FROM foo)) ORDER BY 1; - unnest --------- - 1 - 2 - 3 - 4 - 5 - 6 - 7 - 8 - 9 - 10 -(10 rows) - -SELECT UNNEST(ARRAY(SELECT f(1))); - unnest --------- - 1 -(1 row) - --- PROPERTIES, VOLATILITY, IMMUTABLE -CREATE FUNCTION g() RETURNS float AS 'SELECT random();' LANGUAGE SQL IMMUTABLE; -CREATE FUNCTION -SELECT COUNT(DISTINCT(g())) > 1 FROM foo; - ?column? ----------- - f -(1 row) - -DROP FUNCTION g(); -DROP FUNCTION --- PROPERTIES, VOLATILITY, STABLE -CREATE FUNCTION g() RETURNS float AS 'SELECT random();' LANGUAGE SQL STABLE; -CREATE FUNCTION -SELECT COUNT(DISTINCT(g())) > 1 FROM foo; - ?column? ----------- - f -(1 row) - -DROP FUNCTION g(); -DROP FUNCTION --- PROPERTIES, VOLATILITY, VOLATILE -CREATE FUNCTION g() RETURNS float AS 'SELECT random();' LANGUAGE SQL VOLATILE; -CREATE FUNCTION -SELECT COUNT(DISTINCT(g())) > 1 FROM foo; - ?column? ----------- - t -(1 row) - -DROP FUNCTION g(); -DROP FUNCTION ------------------ --- NEGATIVE TESTS ------------------ -SELECT h(1); -psql:/tmp/TestUDF_TestUDFBasics.sql:405: ERROR: function h(integer) does not exist -LINE 1: SELECT h(1); - ^ -HINT: No function matches the given name and argument types. You may need to add explicit type casts. --- DML, InitPlan -SELECT UNNEST(ARRAY(SELECT f(x) from foo)); - unnest --------- - 1 - 2 - 3 - 4 - 5 - 6 - 7 - 8 - 9 - 10 -(10 rows) - --- LANGUAGES not yet supported --- CREATE LANGUAGE plr; --- CREATE LANGUAGE plpython; --- CREATE LANGUAGE pljava; --- CREATE LANGUAGE plperl; --- NESTED FUNCTION -CREATE FUNCTION inner(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE; -CREATE FUNCTION -CREATE FUNCTION outer(x INT) RETURNS INT AS $$ -BEGIN -RETURN inner(x); -END -$$ LANGUAGE PLPGSQL; -CREATE FUNCTION -SELECT outer(0); - outer -------- - 1 -(1 row) - -SELECT outer(0) FROM foo; - outer -------- - 1 - 1 - 1 - 1 - 1 - 1 - 1 - 1 - 1 - 1 -(10 rows) - -DROP FUNCTION outer(int); -DROP FUNCTION -DROP FUNCTION inner(int); -DROP FUNCTION --- TEARDOWN -DROP TABLE foo; -DROP TABLE --- HAWQ-510 -drop table if exists testEntryDB; -psql:/tmp/TestUDF_TestUDFBasics.sql:435: NOTICE: table "testentrydb" does not exist, skipping -DROP TABLE -create table testEntryDB(key int, value int) distributed randomly; -CREATE TABLE -insert into testEntryDB values(1, 0); -INSERT 0 1 -select t2.key, t2.value -from (select key, value from testEntryDB where value = 0) as t1, - (select generate_series(1,2)::int as key, 0::int as value) as t2 -where t1.value=t2.value; - key | value ------+------- - 1 | 0 - 2 | 0 -(2 rows) - -drop table testEntryDB; -DROP TABLE http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/49fd529a/src/test/feature/udf/ans/function_extension.ans ---------------------------------------------------------------------- diff --git a/src/test/feature/udf/ans/function_extension.ans b/src/test/feature/udf/ans/function_extension.ans deleted file mode 100755 index e1d14f4..0000000 --- a/src/test/feature/udf/ans/function_extension.ans +++ /dev/null @@ -1,183 +0,0 @@ --- start_ignore -SET SEARCH_PATH=TestUDF_TestUDFExtension; -SET --- end_ignore --- ----------------------------------------------------------------- --- Test extensions to functions (MPP-16060) --- 1. data access indicators --- ----------------------------------------------------------------- --- test prodataaccess -create function func1(int, int) returns int as -$$ - select $1 + $2; -$$ language sql immutable contains sql; -CREATE FUNCTION --- check prodataaccess column in pg_proc -select proname, prodataaccess from pg_proc where proname = 'func1'; - proname | prodataaccess ----------+--------------- - func1 | c -(1 row) - --- check prodataaccess in pg_attribute -select relname, attname, attlen from pg_class c, pg_attribute -where attname = 'prodataaccess' and attrelid = c.oid and c.relname = 'pg_proc'; - relname | attname | attlen ----------+---------------+-------- - pg_proc | prodataaccess | 1 -(1 row) - -create function func2(a anyelement, b anyelement, flag bool) -returns anyelement as -$$ - select $1 + $2; -$$ language sql reads sql data; -CREATE FUNCTION --- check prodataaccess column in pg_proc -select proname, prodataaccess from pg_proc where proname = 'func2'; - proname | prodataaccess ----------+--------------- - func2 | r -(1 row) - -create function func3() returns oid as -$$ - select oid from pg_class where relname = 'pg_type'; -$$ language sql modifies sql data volatile; -CREATE FUNCTION --- check prodataaccess column in pg_proc -select proname, prodataaccess from pg_proc where proname = 'func3'; - proname | prodataaccess ----------+--------------- - func3 | m -(1 row) - --- check default value of prodataaccess -drop function func1(int, int); -DROP FUNCTION -create function func1(int, int) returns varchar as $$ -declare - v_name varchar(20) DEFAULT 'zzzzz'; -begin - select relname from pg_class into v_name where oid=$1; - return v_name; -end; -$$ language plpgsql; -CREATE FUNCTION -select proname, proargnames, prodataaccess from pg_proc where proname = 'func1'; - proname | proargnames | prodataaccess ----------+-------------+--------------- - func1 | | n -(1 row) - -create function func4(int, int) returns int as -$$ - select $1 + $2; -$$ language sql; -CREATE FUNCTION --- check prodataaccess column -select proname, proargnames, prodataaccess from pg_proc where proname = 'func4'; - proname | proargnames | prodataaccess ----------+-------------+--------------- - func4 | | c -(1 row) - --- change prodataaccess option -create or replace function func4(int, int) returns int as -$$ - select $1 + $2; -$$ language sql modifies sql data; -CREATE FUNCTION -select proname, proargnames, prodataaccess from pg_proc where proname = 'func4'; - proname | proargnames | prodataaccess ----------+-------------+--------------- - func4 | | m -(1 row) - --- upper case language name -create or replace function func5(int) returns int as -$$ - select $1; -$$ language "SQL"; -CREATE FUNCTION --- check prodataaccess column -select proname, proargnames, prodataaccess from pg_proc where proname = 'func5'; - proname | proargnames | prodataaccess ----------+-------------+--------------- - func5 | | c -(1 row) - --- alter function with data access -alter function func5(int) reads sql data; -ALTER FUNCTION --- check prodataaccess column -select proname, proargnames, prodataaccess from pg_proc where proname = 'func5'; - proname | proargnames | prodataaccess ----------+-------------+--------------- - func5 | | r -(1 row) - --- alter function with data access -alter function func5(int) modifies sql data; -ALTER FUNCTION --- check prodataaccess column -select proname, proargnames, prodataaccess from pg_proc where proname = 'func5'; - proname | proargnames | prodataaccess ----------+-------------+--------------- - func5 | | m -(1 row) - --- alter function with data access -alter function func5(int) no sql; -psql:/tmp/TestUDF_TestUDFExtension.sql:90: ERROR: conflicting options -HINT: A SQL function cannot specify NO SQL. --- alter function with data access -alter function func5(int) volatile contains sql; -ALTER FUNCTION -alter function func5(int) immutable reads sql data; -psql:/tmp/TestUDF_TestUDFExtension.sql:95: ERROR: conflicting options -HINT: IMMUTABLE conflicts with READS SQL DATA. -alter function func5(int) immutable modifies sql data; -psql:/tmp/TestUDF_TestUDFExtension.sql:96: ERROR: conflicting options -HINT: IMMUTABLE conflicts with MODIFIES SQL DATA. --- data_access indicators for plpgsql -drop function func1(int, int); -DROP FUNCTION -create or replace function func1(int, int) returns varchar as $$ -declare - v_name varchar(20) DEFAULT 'zzzzz'; -begin - select relname from pg_class into v_name where oid=$1; - return v_name; -end; -$$ language plpgsql reads sql data; -CREATE FUNCTION -select proname, proargnames, prodataaccess from pg_proc where proname = 'func1'; - proname | proargnames | prodataaccess ----------+-------------+--------------- - func1 | | r -(1 row) - --- check conflicts -drop function func1(int, int); -DROP FUNCTION -create function func1(int, int) returns int as -$$ - select $1 + $2; -$$ language sql immutable no sql; -psql:/tmp/TestUDF_TestUDFExtension.sql:116: ERROR: conflicting options -HINT: A SQL function cannot specify NO SQL. -create function func1(int, int) returns int as -$$ - select $1 + $2; -$$ language sql immutable reads sql data; -psql:/tmp/TestUDF_TestUDFExtension.sql:121: ERROR: conflicting options -HINT: IMMUTABLE conflicts with READS SQL DATA. -drop function func2(anyelement, anyelement, bool); -DROP FUNCTION -drop function func3(); -DROP FUNCTION -drop function func4(int, int); -DROP FUNCTION -drop function func5(int); -DROP FUNCTION
