Created
November 27, 2023 23:03
-
-
Save viggy28/150af6301c793e923b02d29888f3678d to your computer and use it in GitHub Desktop.
A postgres C extension demos how to use SPI - Server Programming Interface
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#include "postgres.h" | |
#include "fmgr.h" | |
#include "executor/spi.h" | |
#include "inttypes.h" | |
PG_MODULE_MAGIC; | |
PG_FUNCTION_INFO_V1(add_nums); | |
Datum add_nums(PG_FUNCTION_ARGS) | |
{ | |
int32 arg1 = PG_GETARG_INT32(0); | |
int32 arg2 = PG_GETARG_INT32(1); | |
int sum = arg1 + arg2; | |
elog(INFO, "sum: %d", sum); | |
int result = SPI_connect(); | |
int bonus; | |
if (result == SPI_ERROR_CONNECT) { | |
elog(INFO, "error connecting to the database"); | |
SPI_finish(); | |
PG_RETURN_INT32(0); | |
} | |
if (result == SPI_OK_CONNECT) { | |
elog(INFO, "successfully connected to the database"); | |
} | |
// any value >0 tell that the command is success | |
int return_value = SPI_execute("select * from spi_demo", true, 0); | |
// SPI_tuptable global pointer gets populated, if return_value is > 0 | |
if (return_value > 0 && SPI_tuptable != NULL) { | |
// SPI_processed is an integer that gets populated with the number of rows processed | |
elog(INFO, "number of rows processed: %" PRIu64, SPI_processed); | |
// assign the value of global variable SPI_tuptable to local C variable tuptable | |
SPITupleTable *tuptable = SPI_tuptable; | |
TupleDesc tupdesc = tuptable->tupdesc; | |
char buf[8192]; | |
uint64 j; | |
for (j = 0; j < tuptable->numvals; j++) | |
{ | |
HeapTuple tuple = tuptable->vals[j]; | |
int i; | |
for (i = 1, buf[0] = 0; i <= tupdesc->natts; i++) { | |
snprintf(buf + strlen(buf), sizeof(buf) - strlen(buf), " %s%s", | |
SPI_getvalue(tuple, tupdesc, i), | |
(i == tupdesc->natts) ? " " : " |"); | |
elog(INFO, "EXECQ: %s", buf); | |
elog(INFO, "i = %d", i); | |
bonus = atoi(SPI_getvalue(tuple, tupdesc, i)); | |
} | |
} | |
SPI_finish(); | |
PG_RETURN_INT32(sum + bonus); | |
} | |
SPI_finish(); | |
PG_RETURN_INT32(0); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment