A postgres C extension demos how to use SPI - Server Programming Interface

pg_cext.c

#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);
 }