C++ SPI_prepare函数代码示例

static void init_pkey_plan(void)
{
	Oid types[1] = { OIDOID };
	pkey_plan = SPI_saveplan(SPI_prepare(pkey_sql, 1, types));
	if (pkey_plan == NULL)
		elog(ERROR, "pgq_triggers: SPI_prepare() failed");
}

/*
 * Prepare utility plans and plan cache.
 */
static void
init_cache(void)
{
	static int init_done = 0;
	Oid types[1] = { TEXTOID };
	HASHCTL     ctl;
	int         flags;
	int         max_queues = 128;

	if (init_done)
		return;

	/*
	 * Init plans.
	 */
	queue_plan = SPI_saveplan(SPI_prepare(QUEUE_SQL, 1, types));
	if (queue_plan == NULL)
		elog(ERROR, "pgq_insert: SPI_prepare() failed");

	/*
	 * init insert plan cache.
	 */
	MemSet(&ctl, 0, sizeof(ctl));
	ctl.keysize = sizeof(Oid);
	ctl.entrysize = sizeof(struct InsertCacheEntry);
	ctl.hash = oid_hash;
	flags = HASH_ELEM | HASH_FUNCTION;
	insert_cache = hash_create("pgq_insert_raw plans cache", max_queues, &ctl, flags);

	init_done = 1;
}

/*
 * helper for queue insertion.
 *
 * does not support NULL arguments.
 */
void pgq_simple_insert(const char *queue_name, Datum ev_type, Datum ev_data,
		       Datum ev_extra1, Datum ev_extra2, Datum ev_extra3, Datum ev_extra4)
{
	Datum values[7];
	char nulls[7];
	static void *plan = NULL;
	int res;

	if (!plan) {
		const char *sql;
		Oid   types[7] = { TEXTOID, TEXTOID, TEXTOID, TEXTOID, TEXTOID, TEXTOID, TEXTOID };

		sql = "select pgq.insert_event($1, $2, $3, $4, $5, $6, $7)";
		plan = SPI_saveplan(SPI_prepare(sql, 7, types));
		if (plan == NULL)
			elog(ERROR, "logtriga: SPI_prepare() failed");
	}
	values[0] = DirectFunctionCall1(textin, (Datum)queue_name);
	values[1] = ev_type;
	values[2] = ev_data;
	values[3] = ev_extra1;
	values[4] = ev_extra2;
	values[5] = ev_extra3;
	values[6] = ev_extra4;
	nulls[0] = ' ';
	nulls[1] = ev_type ? ' ' : 'n';
	nulls[2] = ev_data ? ' ' : 'n';
	nulls[3] = ev_extra1 ? ' ' : 'n';
	nulls[4] = ev_extra2 ? ' ' : 'n';
	nulls[5] = ev_extra3 ? ' ' : 'n';
	nulls[6] = ev_extra4 ? ' ' : 'n';
	res = SPI_execute_plan(plan, values, nulls, false, 0);
	if (res != SPI_OK_SELECT)
		elog(ERROR, "call of pgq.insert_event failed");
}

/* prepare plan */
static SPIPlanPtr
reorg_prepare(const char *src, int nargs, Oid *argtypes)
{
    SPIPlanPtr	plan = SPI_prepare(src, nargs, argtypes);
    if (plan == NULL)
        elog(ERROR, "pg_reorg: reorg_prepare failed (code=%d, query=%s)", SPI_result, src);
    return plan;
}

static PyObject *
PLy_cursor_query(const char *query)
{
	PLyCursorObject *cursor;
	volatile MemoryContext oldcontext;
	volatile ResourceOwner oldowner;

	if ((cursor = PyObject_New(PLyCursorObject, &PLy_CursorType)) == NULL)
		return NULL;
	cursor->portalname = NULL;
	cursor->closed = false;
	cursor->mcxt = AllocSetContextCreate(TopMemoryContext,
										 "PL/Python cursor context",
										 ALLOCSET_DEFAULT_MINSIZE,
										 ALLOCSET_DEFAULT_INITSIZE,
										 ALLOCSET_DEFAULT_MAXSIZE);
	PLy_typeinfo_init(&cursor->result, cursor->mcxt);

	oldcontext = CurrentMemoryContext;
	oldowner = CurrentResourceOwner;

	PLy_spi_subtransaction_begin(oldcontext, oldowner);

	PG_TRY();
	{
		PLyExecutionContext *exec_ctx = PLy_current_execution_context();
		SPIPlanPtr	plan;
		Portal		portal;

		pg_verifymbstr(query, strlen(query), false);

		plan = SPI_prepare(query, 0, NULL);
		if (plan == NULL)
			elog(ERROR, "SPI_prepare failed: %s",
				 SPI_result_code_string(SPI_result));

		portal = SPI_cursor_open(NULL, plan, NULL, NULL,
								 exec_ctx->curr_proc->fn_readonly);
		SPI_freeplan(plan);

		if (portal == NULL)
			elog(ERROR, "SPI_cursor_open() failed: %s",
				 SPI_result_code_string(SPI_result));

		cursor->portalname = MemoryContextStrdup(cursor->mcxt, portal->name);

		PLy_spi_subtransaction_commit(oldcontext, oldowner);
	}
	PG_CATCH();
	{
		PLy_spi_subtransaction_abort(oldcontext, oldowner);
		return NULL;
	}
	PG_END_TRY();

	Assert(cursor->portalname != NULL);
	return (PyObject *) cursor;
}

SPIPlanPtr
pgr_SPI_prepare(char* sql) {
    PGR_DBG("Preparing Plan");
    SPIPlanPtr SPIplan;
    SPIplan = SPI_prepare(sql, 0, NULL);
    if (SPIplan  == NULL) {
        elog(ERROR, "Couldn't create query plan via SPI: %s", sql);
    }
    return SPIplan;
}

Datum
dbms_alert_signal(PG_FUNCTION_ARGS)
{
	void *plan;
	Oid argtypes[] = {TEXTOID, TEXTOID};
	Datum values[2];
	char nulls[2] = {' ',' '};

	if (PG_ARGISNULL(0))
		ereport(ERROR,
    			(errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
        		 errmsg("event name is NULL"),
			 errdetail("Eventname may not be NULL.")));

	if (PG_ARGISNULL(1))
		nulls[1] = 'n';

	values[0] = PG_GETARG_DATUM(0);
	values[1] = PG_GETARG_DATUM(1);


	if (SPI_connect() < 0)
		ereport(ERROR,
			(errcode(ERRCODE_INTERNAL_ERROR),
			 errmsg("SPI_connect failed")));

	SPI_EXEC("SELECT 1 FROM pg_catalog.pg_class c "
                    "WHERE pg_catalog.pg_table_is_visible(c.oid) "
                            "AND c.relkind='r' AND c.relname = 'ora_alerts'", SELECT);
	if (0 == SPI_processed)
	{
		SPI_EXEC("CREATE TEMP TABLE ora_alerts(event text, message text)", UTILITY);
		SPI_EXEC("REVOKE ALL ON TABLE ora_alerts FROM PUBLIC", UTILITY);
		SPI_EXEC("CREATE CONSTRAINT TRIGGER ora_alert_signal AFTER INSERT ON ora_alerts "
    		    "INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE dbms_alert.defered_signal()", UTILITY);
	}


	if (!(plan = SPI_prepare(
			"INSERT INTO ora_alerts(event,message) VALUES($1, $2)",
			2,
			argtypes)))
			ereport(ERROR,
				(errcode(ERRCODE_INTERNAL_ERROR),
				 errmsg("SPI_prepare failed")));

	if (SPI_OK_INSERT != SPI_execute_plan(plan, values, nulls, false, 1))
			ereport(ERROR,
				(errcode(ERRCODE_INTERNAL_ERROR),
				errmsg("can't execute sql")));


	SPI_finish();
	PG_RETURN_VOID();
}

/*
 * utl_file_dir security .. is solved with aux. table.
 *
 * Raise exception if don't find string in table.
 */
static void
check_secure_locality(const char *path)
{
	static SPIPlanPtr	plan = NULL;

	Oid		argtypes[] = {TEXTOID};
	Datum	values[1];
	char	nulls[1] = {' '};

	/* hack for availbility regress test */
	if (strcmp(path, "/tmp/regress_orafce") == 0)
		return;

	values[0] = CStringGetTextDatum(path);

	/*
	 * SELECT 1 FROM utl_file.utl_file_dir
	 *   WHERE substring($1, 1, length(dir) + 1) = dir || '/'
	 */

	if (SPI_connect() < 0)
		ereport(ERROR,
			(errcode(ERRCODE_INTERNAL_ERROR),
			 errmsg("SPI_connect failed")));

	if (!plan)
	{
		/* Don't use LIKE not to escape '_' and '%' */
		SPIPlanPtr p = SPI_prepare(
		    "SELECT 1 FROM utl_file.utl_file_dir"
			" WHERE substring($1, 1, length(dir) + 1) = dir || '/'",
		    1, argtypes);

		if (p == NULL || (plan = SPI_saveplan(p)) == NULL)
			ereport(ERROR,
				(errcode(ERRCODE_INTERNAL_ERROR),
				errmsg("SPI_prepare_failed")));
	}

	if (SPI_OK_SELECT != SPI_execute_plan(plan, values, nulls, false, 1))
		ereport(ERROR,
			(errcode(ERRCODE_INTERNAL_ERROR),
			 errmsg("can't execute sql")));

	if (SPI_processed == 0)
		ereport(ERROR,
			(errcode(ERRCODE_RAISE_EXCEPTION),
			 errmsg(INVALID_PATH),
			 errdetail("you cannot access locality"),
			 errhint("locality is not found in utl_file_dir table")));
	SPI_finish();
}

/*
 * spi_exec_query
 *   Execute given SQL command via SPI.
 *   The plan will be cached by SPI_prepare if it hasn't been.
 */
static void
spi_exec_query(const char *query, int nargs, Oid *argtypes, SPIPlanPtr *plan,
				Datum *values, const char *nulls, int result)
{
	int	ret;

	if (*plan == NULL)
		*plan = SPI_prepare(query, nargs, argtypes);

	ret = SPI_execute_plan(*plan, values, nulls, false, 0);
	if (ret != result)
		elog(ERROR, "pg_dbms_stats: SPI_execute_plan => %d", ret);
}

int
storeKeyInfo(char *cpTableName, HeapTuple tTupleData,
			 TupleDesc tTupleDesc, Oid tableOid)
{

	Oid			saPlanArgTypes[1] = {NAMEOID};
	char	   *insQuery = "INSERT INTO dbmirror_pendingdata (SeqId,IsKey,Data) VALUES(currval('dbmirror_pending_seqid_seq'),'t',$1)";
	void	   *pplan;
	Datum		saPlanData[1];
	char	   *cpKeyData;
	int			iRetCode;

	pplan = SPI_prepare(insQuery, 1, saPlanArgTypes);
	if (pplan == NULL)
	{
		elog(NOTICE, "could not prepare INSERT plan");
		return -1;
	}

	/* pplan = SPI_saveplan(pplan); */
	cpKeyData = packageData(tTupleData, tTupleDesc, tableOid, PRIMARY);
	if (cpKeyData == NULL)
		ereport(ERROR,
				(errcode(ERRCODE_UNDEFINED_OBJECT),
		/* cpTableName already contains quotes... */
				 errmsg("there is no PRIMARY KEY for table %s",
						cpTableName)));


	debug_msg2("dbmirror:storeKeyInfo key data: %s", cpKeyData);

	saPlanData[0] = PointerGetDatum(cpKeyData);

	iRetCode = SPI_execp(pplan, saPlanData, NULL, 1);

	if (cpKeyData != NULL)
		SPI_pfree(cpKeyData);

	if (iRetCode != SPI_OK_INSERT)
		ereport(ERROR,
				(errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION),
				 errmsg("error inserting row in pendingDelete")));

	debug_msg("insert successful");

	return 0;

}

Oid
name2id_cfg(text *name)
{
    Oid			arg[1];
    bool		isnull;
    Datum		pars[1];
    int			stat;
    Oid			id = findSNMap_t(&(CList.name2id_map), name);
    void	   *plan;
    char	   *nsp;
    char		buf[1024];

    arg[0] = TEXTOID;
    pars[0] = PointerGetDatum(name);

    if (id)
        return id;

    nsp = get_namespace(TSNSP_FunctionOid);
    SPI_connect();
    sprintf(buf, "select oid from %s.pg_ts_cfg where ts_name = $1", nsp);
    plan = SPI_prepare(buf, 1, arg);
    if (!plan)
        /* internal error */
        elog(ERROR, "SPI_prepare() failed");

    stat = SPI_execp(plan, pars, " ", 1);
    if (stat < 0)
        /* internal error */
        elog(ERROR, "SPI_execp return %d", stat);
    if (SPI_processed > 0)
    {
        id = DatumGetObjectId(SPI_getbinval(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1, &isnull));
        if (isnull)
            ereport(ERROR,
                    (errcode(ERRCODE_CONFIG_FILE_ERROR),
                     errmsg("null id for tsearch config")));
    }
    else
        ereport(ERROR,
                (errcode(ERRCODE_CONFIG_FILE_ERROR),
                 errmsg("no tsearch config")));

    SPI_freeplan(plan);
    SPI_finish();
    addSNMap_t(&(CList.name2id_map), name, id);
    return id;
}

int
SPI_execute_with_args(const char *src,
					  int nargs, Oid *argtypes,
					  Datum *values, const char *nulls,
					  bool read_only, long tcount)
{
	SPIPlanPtr	plan;
	int			ret;

	plan = SPI_prepare(src, nargs, argtypes);
	if (plan == NULL)
		return SPI_result;
	ret = SPI_execute_plan(plan, values, nulls, read_only, tcount);
	SPI_freeplan(plan);
	return ret;
}

void
init_dict(Oid id, DictInfo * dict)
{
	Oid			arg[1];
	bool		isnull;
	Datum		pars[1];
	int			stat;
	void	   *plan;
	char		buf[1024];
	char	   *nsp = get_namespace(TSNSP_FunctionOid);

	arg[0] = OIDOID;
	pars[0] = ObjectIdGetDatum(id);

	memset(dict, 0, sizeof(DictInfo));
	SPI_connect();
	sprintf(buf, "select dict_init, dict_initoption, dict_lexize from %s.pg_ts_dict where oid = $1", nsp);
	pfree(nsp);
	plan = SPI_prepare(buf, 1, arg);
	if (!plan)
		ts_error(ERROR, "SPI_prepare() failed");

	stat = SPI_execp(plan, pars, " ", 1);
	if (stat < 0)
		ts_error(ERROR, "SPI_execp return %d", stat);
	if (SPI_processed > 0)
	{
		Datum		opt;
		Oid			oid = InvalidOid;

		oid = DatumGetObjectId(SPI_getbinval(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1, &isnull));
		if (!(isnull || oid == InvalidOid))
		{
			opt = SPI_getbinval(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 2, &isnull);
			dict->dictionary = (void *) DatumGetPointer(OidFunctionCall1(oid, opt));
		}
		oid = DatumGetObjectId(SPI_getbinval(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 3, &isnull));
		if (isnull || oid == InvalidOid)
			ts_error(ERROR, "Null dict_lexize for dictonary %d", id);
		fmgr_info_cxt(oid, &(dict->lexize_info), TopMemoryContext);
		dict->dict_id = id;
	}
	else
		ts_error(ERROR, "No dictionary with id %d", id);
	SPI_freeplan(plan);
	SPI_finish();
}

void
init_prs(Oid id, WParserInfo * prs)
{
	Oid			arg[1];
	bool		isnull;
	Datum		pars[1];
	int			stat;
	void	   *plan;
	char		buf[1024],
			   *nsp;

	arg[0] = OIDOID;
	pars[0] = ObjectIdGetDatum(id);

	memset(prs, 0, sizeof(WParserInfo));
	SPI_connect();
	nsp = get_namespace(TSNSP_FunctionOid);
	sprintf(buf, "select prs_start, prs_nexttoken, prs_end, prs_lextype, prs_headline from %s.pg_ts_parser where oid = $1", nsp);
	pfree(nsp);
	plan = SPI_prepare(buf, 1, arg);
	if (!plan)
		ts_error(ERROR, "SPI_prepare() failed");

	stat = SPI_execp(plan, pars, " ", 1);
	if (stat < 0)
		ts_error(ERROR, "SPI_execp return %d", stat);
	if (SPI_processed > 0)
	{
		Oid			oid = InvalidOid;

		oid = DatumGetObjectId(SPI_getbinval(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1, &isnull));
		fmgr_info_cxt(oid, &(prs->start_info), TopMemoryContext);
		oid = DatumGetObjectId(SPI_getbinval(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 2, &isnull));
		fmgr_info_cxt(oid, &(prs->getlexeme_info), TopMemoryContext);
		oid = DatumGetObjectId(SPI_getbinval(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 3, &isnull));
		fmgr_info_cxt(oid, &(prs->end_info), TopMemoryContext);
		prs->lextype = DatumGetObjectId(SPI_getbinval(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 4, &isnull));
		oid = DatumGetObjectId(SPI_getbinval(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 5, &isnull));
		fmgr_info_cxt(oid, &(prs->headline_info), TopMemoryContext);
		prs->prs_id = id;
	}
	else
		ts_error(ERROR, "No parser with id %d", id);
	SPI_freeplan(plan);
	SPI_finish();
}

/******************************************************************************
 * Stores a copy of the non-key data for the row.
 *****************************************************************************/
int
storeData(char *cpTableName, HeapTuple tTupleData,
		  TupleDesc tTupleDesc, Oid tableOid, int iIncludeKeyData)
{

	Oid			planArgTypes[1] = {NAMEOID};
	char	   *insQuery = "INSERT INTO dbmirror_pendingdata (SeqId,IsKey,Data) VALUES(currval('dbmirror_pending_seqid_seq'),'f',$1)";
	void	   *pplan;
	Datum		planData[1];
	char	   *cpKeyData;
	int			iRetValue;

	pplan = SPI_prepare(insQuery, 1, planArgTypes);
	if (pplan == NULL)
	{
		elog(NOTICE, "could not prepare INSERT plan");
		return -1;
	}

	/* pplan = SPI_saveplan(pplan); */
	if (iIncludeKeyData == 0)
		cpKeyData = packageData(tTupleData, tTupleDesc,
								tableOid, NONPRIMARY);
	else
		cpKeyData = packageData(tTupleData, tTupleDesc, tableOid, ALL);

	planData[0] = PointerGetDatum(cpKeyData);
	iRetValue = SPI_execp(pplan, planData, NULL, 1);

	if (cpKeyData != 0)
		SPI_pfree(cpKeyData);

	if (iRetValue != SPI_OK_INSERT)
	{
		elog(NOTICE, "error inserting row in pendingDelete");
		return -1;
	}

	debug_msg("dbmirror:storeKeyData insert successful");


	return 0;

}