/* adapted from SPI_modifytuple */ static HeapTuple luaP_copytuple (luaP_Tuple *t) { HeapTuple tuple = heap_form_tuple(t->desc, t->value, t->null); /* copy identification info */ tuple->t_data->t_ctid = t->tuple->t_data->t_ctid; tuple->t_self = t->tuple->t_self; tuple->t_tableOid = t->tuple->t_tableOid; if (t->desc->tdhasoid) HeapTupleSetOid(tuple, HeapTupleGetOid(t->tuple)); return SPI_copytuple(tuple); /* in upper mem context */ }
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_ROW(trigdata->tg_event)) elog(ERROR, "ttdummy: must be fired for row"); if (!TRIGGER_FIRED_BEFORE(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); if (SPI_keepplan(pplan)) elog(ERROR, "ttdummy (%s): SPI_keepplan failed", relname); 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); }
Datum exec_sql_using(PG_FUNCTION_ARGS) { HeapTuple procedureTuple = SearchSysCache1(PROCOID, ObjectIdGetDatum(fcinfo->flinfo->fn_oid)); if (!HeapTupleIsValid(procedureTuple)) ereport(ERROR, (errmsg("cache lookup failed for function %u", fcinfo->flinfo->fn_oid))); Oid* types = NULL; char** names = NULL; char* modes = NULL; int nargs = get_func_arg_info(procedureTuple, &types, &names, &modes); Oid resultTypeOid; TupleDesc tupleDesc; TypeFuncClass resultType = get_call_result_type(fcinfo, &resultTypeOid, &tupleDesc); bool returnTypeIsByValue; int16 returnTypeLen; get_typlenbyval(resultTypeOid, &returnTypeLen, &returnTypeIsByValue); if (resultType != TYPEFUNC_SCALAR && resultType != TYPEFUNC_COMPOSITE) ereport(ERROR, ( errmsg("function \"%s\" has indeterminable result type", format_procedure(fcinfo->flinfo->fn_oid)) )); bool returnVoid = resultTypeOid == VOIDOID; ReleaseSysCache(procedureTuple); if (nargs < 2) ereport(ERROR, ( errmsg("function \"%s\" has less than 2 arguments", format_procedure(fcinfo->flinfo->fn_oid)) )); else if (modes != NULL) for (int i = 0; i < nargs; i++) { if (modes[i] != PROARGMODE_IN) ereport(ERROR, ( errmsg("function \"%s\" has non-IN arguments", format_procedure(fcinfo->flinfo->fn_oid)) )); } else if (PG_ARGISNULL(0)) ereport(ERROR, ( errmsg("function \"%s\" called with NULL as first argument", format_procedure(fcinfo->flinfo->fn_oid)) )); char* stmt = NULL; if (types[0] == TEXTOID) stmt = DatumGetCString( DirectFunctionCall1(textout, PG_GETARG_DATUM(0))); else if (types[0] == VARCHAROID) stmt = DatumGetCString( DirectFunctionCall1(varcharout, PG_GETARG_DATUM(0))); else ereport(ERROR, ( errmsg("function \"%s\" does not have a leading VARCHAR/TEXT " "argument", format_procedure(fcinfo->flinfo->fn_oid)) )); char* nulls = NULL; for (int i = 1; i < nargs; i++) if (PG_ARGISNULL(i)) { if (nulls == NULL) { nulls = palloc0(sizeof(char) * (nargs - 1)); memset(nulls, ' ', nargs - 1); } nulls[i - 1] = 'n'; } SPI_connect(); SPIPlanPtr plan = SPI_prepare(stmt, nargs - 1, &types[1]); if (plan == NULL) ereport(ERROR, ( errmsg("function \"%s\" could not obtain execution plan for " "SQL statement", format_procedure(fcinfo->flinfo->fn_oid)) )); int result = SPI_execute_plan(plan, &fcinfo->arg[1], nulls, false, returnVoid ? 0 : 1); Datum returnValue = 0; bool returnNull = false; if (!returnVoid) { if (result != SPI_OK_SELECT && result != SPI_OK_INSERT_RETURNING && result != SPI_OK_DELETE_RETURNING && result == SPI_OK_UPDATE_RETURNING) ereport(ERROR, ( errmsg("function \"%s\" could not obtain result from query", format_procedure(fcinfo->flinfo->fn_oid)) )); else if (SPI_tuptable->tupdesc->natts != 1) ereport(ERROR, ( errmsg("function \"%s\" retrieved more than one column from " "query", format_procedure(fcinfo->flinfo->fn_oid)) )); else if (resultTypeOid != SPI_gettypeid(SPI_tuptable->tupdesc, 1)) ereport(ERROR, ( errmsg("function \"%s\" has different return type OID than " "what query returned", format_procedure(fcinfo->flinfo->fn_oid)) )); /* It is important to copy the value into the upper executor context, * i.e., the memory context that was current when SPI_connect was * called */ returnValue = SPI_getbinval(SPI_copytuple(SPI_tuptable->vals[0]), SPI_tuptable->tupdesc, 1, &returnNull); } SPI_freeplan(plan); if (nulls) pfree(nulls); SPI_finish(); if (result < 0) ereport(ERROR, ( errmsg("function \"%s\" encountered error %d during SQL execution", format_procedure(fcinfo->flinfo->fn_oid), result) )); if (returnVoid) PG_RETURN_VOID(); else if (returnNull) PG_RETURN_NULL(); else return returnValue; }