色んな方法でINSERT

Makefile

# pg_insert_test/Makefile

MODULE_big = pg_insert_test
DATA = pg_insert_test--1.0.sql
OBJS = pg_insert_test.o

EXTENSION = pg_insert_test

PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

pg_insert_test--1.0.sql

/* pg_insert_test */

-- complain if script is sourced in psql, rather than via CREATE EXTENSION
\echo Use "CREATE EXTENSION pg_insert_test" to load this file. \quit

create function insert_spi()
returns void as 'MODULE_PATHNAME'
language c strict parallel unsafe;

create function insert_executor()
returns void as 'MODULE_PATHNAME'
language c strict parallel unsafe;

create function insert_heap()
returns void as 'MODULE_PATHNAME'
language c strict parallel unsafe;

create function insert_page()
returns void as 'MODULE_PATHNAME'
language c strict parallel unsafe;

pg_insert_test.c

#include "postgres.h"

#include "access/heapam.h"
#include "access/hio.h"
#include "access/heapam_xlog.h"
#include "access/htup_details.h"
#include "access/table.h"
#include "access/xact.h"
#include "access/xloginsert.h"
#include "catalog/namespace.h"
#include "executor/executor.h"
#include "executor/spi.h"
#include "fmgr.h"
#include "funcapi.h"
#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "nodes/nodes.h"
#include "storage/bufmgr.h"
#include "storage/bufpage.h"
#include "utils/builtins.h"
#include "utils/catcache.h"
#include "utils/lsyscache.h"
#include "utils/memutils.h"
#include "utils/rel.h"
#include "utils/syscache.h"

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(insert_spi);
PG_FUNCTION_INFO_V1(insert_executor);
PG_FUNCTION_INFO_V1(insert_heap);
PG_FUNCTION_INFO_V1(insert_page);

Datum
insert_spi(PG_FUNCTION_ARGS)
{
	SPI_connect();

	SPI_execute("INSERT INTO test VALUES (1, 'alice');", false, 1);

	SPI_finish();

	PG_RETURN_VOID();
}

Datum
insert_executor(PG_FUNCTION_ARGS)
{
	Relation rel;
	Oid nspid, relid;
	EState *estate;
	ResultRelInfo *relinfo;
	RangeTblEntry *rte;
	TupleTableSlot *slot;

	/* Open table "test" */
	nspid = get_namespace_oid("public", false);
	relid = get_relname_relid("test", nspid);
	if (!OidIsValid(relid))
		elog(ERROR, "table \"%s\" does not exist", "test");
	rel = table_open(relid, RowExclusiveLock);

	/* Set up executor state */
	estate = CreateExecutorState();
	rte = makeNode(RangeTblEntry);
	rte->rtekind = RTE_RELATION;
	rte->relid = relid;
	rte->relkind = rel->rd_rel->relkind;
	rte->rellockmode = AccessShareLock;
	ExecInitRangeTable(estate, list_make1(rte));
	relinfo = makeNode(ResultRelInfo);
	InitResultRelInfo(relinfo, rel, 1, NULL, 0);
	estate->es_opened_result_relations =
		lappend(estate->es_opened_result_relations, relinfo);
	estate->es_output_cid = GetCurrentCommandId(false);

	/* fill input data to tuple slot */
	slot = ExecInitExtraTupleSlot(estate,
								  RelationGetDescr(rel),
								  &TTSOpsVirtual);
	ExecClearTuple(slot);
	slot->tts_values[0] = Int32GetDatum(1);
	slot->tts_isnull[0] = false;
 	slot->tts_values[1] = CStringGetTextDatum("alice");
	slot->tts_isnull[1] = false;
	ExecStoreVirtualTuple(slot);

	/* Execute insertion */
	ExecOpenIndices(relinfo, false);
	ExecSimpleRelationInsert(relinfo, estate, slot);
	ExecCloseIndices(relinfo);

	/* Clean up */
	ExecResetTupleTable(estate->es_tupleTable, false);
	FreeExecutorState(estate);
	table_close(rel, NoLock);

	PG_RETURN_VOID();
}

Datum
insert_heap(PG_FUNCTION_ARGS)
{
	Oid relid, nspid;
	Relation rel;
	TupleDesc tupdesc;
	Datum values[2];
	bool isnull[2];
	HeapTuple tuple;

	/* Open table "test" */
	nspid = get_namespace_oid("public", false);
	relid = get_relname_relid("test", nspid);
	if (!OidIsValid(relid))
		elog(ERROR, "table \"%s\" does not exist", "test");
	rel = table_open(relid, RowExclusiveLock);
	tupdesc = RelationGetDescr(rel);

	values[0] = Int32GetDatum(1);
	isnull[0] = false;
	values[1] = CStringGetTextDatum("alice");
	isnull[1] = false;

	tuple = heap_form_tuple(tupdesc, values, isnull);

	simple_heap_insert(rel, tuple);

	table_close(rel, NoLock);

	PG_RETURN_VOID();
}

Datum
insert_page(PG_FUNCTION_ARGS)
{
	Oid relid, nspid;
	Relation rel;
	TupleDesc tupdesc;
	Datum values[2];
	bool isnull[2];
	HeapTuple tuple;
	Buffer buffer;
	Page page;
	OffsetNumber offnum;

	/* Open table "test" */
	nspid = get_namespace_oid("public", false);
	relid = get_relname_relid("test", nspid);
	if (!OidIsValid(relid))
		elog(ERROR, "table \"%s\" does not exist", "test");
	rel = table_open(relid, RowExclusiveLock);
	tupdesc = RelationGetDescr(rel);

	/* Create a heap tuple */
	values[0] = Int32GetDatum(1);
	isnull[0] = false;
	values[1] = CStringGetTextDatum("alice");
	isnull[1] = false;
	tuple = heap_form_tuple(tupdesc, values, isnull);

	/* Fill the tuple header */
	tuple->t_data->t_infomask &= ~(HEAP_XACT_MASK);
	tuple->t_data->t_infomask2 &= ~(HEAP2_XACT_MASK);
	tuple->t_data->t_infomask = HEAP_XMAX_INVALID;
	HeapTupleHeaderSetXmin(tuple->t_data, GetTopTransactionId());
	HeapTupleHeaderSetCmin(tuple->t_data, GetCurrentCommandId(true));
	tuple->t_tableOid = relid;

	/* Get the buffer to insert */
	buffer = RelationGetBufferForTuple(rel, tuple->t_len, InvalidBuffer,
									   0, NULL, NULL, NULL);
	START_CRIT_SECTION();

	/* Put the tuple in the page */
	page = BufferGetPage(buffer);
	offnum = PageAddItem(page, (Item) tuple->t_data,
						 tuple->t_len, InvalidOffsetNumber, false, true);
	ItemPointerSet(&(tuple->t_self), BufferGetBlockNumber(buffer), offnum);

	MarkBufferDirty(buffer);

	/* Write WAL record */
	if (RelationNeedsWAL(rel))
	{
		xl_heap_insert xlrec;
		xl_heap_header xlhdr;
		XLogRecPtr recptr;
		uint8	info = XLOG_HEAP_INSERT;

		page = BufferGetPage(buffer);

		if (ItemPointerGetOffsetNumber(&(tuple->t_self)) == FirstOffsetNumber &&
			PageGetMaxOffsetNumber(page) == FirstOffsetNumber)
			info |= XLOG_HEAP_INIT_PAGE;

		xlrec.offnum = ItemPointerGetOffsetNumber(&tuple->t_self);
		xlrec.flags = 0;

		XLogBeginInsert();
		XLogRegisterData((char *) &xlrec, SizeOfHeapInsert);

		XLogRegisterBuffer(0, buffer, REGBUF_STANDARD);
		XLogRegisterBufData(0, (char *) &xlhdr, SizeOfHeapHeader);
		XLogRegisterBufData(0,
						   (char *) tuple->t_data + SizeofHeapTupleHeader,
						   tuple->t_len - SizeofHeapTupleHeader);

		recptr = XLogInsert(RM_HEAP_ID, info);

		PageSetLSN(page, recptr);
	}

	END_CRIT_SECTION();

	UnlockReleaseBuffer(buffer);
	table_close(rel, NoLock);

	PG_RETURN_VOID();
}

pg_insert_test.control

# pg_insert_test extension
comment = 'test variaous ways to insert a tuple'
default_version = '1.0'
module_pathname = '$libdir/pg_insert_test'
relocatable = true