How to send a SQL command inside a C-language function on a PostgreSQL extension (Apache AGE)?

Question

I’m working on a PostgreSQL extension (Apache AGE) and need to execute a SQL command from within a C-language function. How can I achieve this?

For context, I’m developing an extension that modifies some tables in a database, and I need to run some ALTER TABLE commands to add or remove columns, rename columns, or change column types, depending on certain conditions. I already have a C function that performs the necessary computations, but I don’t know how to execute a SQL command from within it.

Here’s a simplified version of my code:

PG_FUNCTION_INFO_V1(my_function);

Datum
my_function(PG_FUNCTION_ARGS)
{
    // Perform some computations...

    // Execute a SQL command (not sure how to do this)...

    PG_RETURN_NULL();
}

Can anyone show me how to execute a SQL command inside a C function in a PostgreSQL extension? Any help would be appreciated!

score 1 · Answer 1 · answered Apr 03 '23 at 18:00

You can use SPI (Server Programming Interface) for that.

The Server Programming Interface (SPI) gives writers of user-defined C functions the ability to run SQL commands inside their functions or procedures. SPI is a set of interface functions to simplify access to the parser, planner, and executor. SPI also does some memory management.

Example: https://www.postgresql.org/docs/current/spi-examples.html

score 1 · Accepted Answer · answered Apr 03 '23 at 18:21

You can use the SPI (Server Programming Interface) provided by postgreSQL for extension developers.

It is a set of C-language functions and macros that provide a way to execute SQL commands and queries within the PostgreSQL server.

Using the SPI interface, C code can:

Prepare and execute SQL statements
Retrieve and process query results
Access tables and indexes
Handle errors and exceptions

In this context, your ALTER TABLE function may look like this:

#include "postgres.h"
#include "fmgr.h"
#include "utils/elog.h"
#include "commands/trigger.h"
#include "access/xact.h"
#include "executor/spi.h"

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(alter_table_func);

Datum alter_table_func(PG_FUNCTION_ARGS)
{
    int ret;
    char *sql = "ALTER TABLE my_table ADD COLUMN new_column INTEGER";

    SPI_connect();
    ret = SPI_execute(sql, true, 0);
    SPI_finish();

    if (ret != SPI_OK_UTILITY)
        elog(ERROR, "ALTER TABLE failed");
    PG_RETURN_VOID();
}

You can learn more here: SPI | SPI Examples

score 1 · Answer 3 · answered Apr 03 '23 at 19:21

You can achieve This one way is to using Server Programming Interface provide by postgreSQL

The Server Programming Interface give use way to user defined C functions the ability to run SQL commands inside their functions or procedures.source: Postgres Document official

first you have to include required header's #include "executor/spi.h" then inside function call SPI_connect()

score 0 · Answer 4 · answered Apr 03 '23 at 18:31

0

You can run sql statements inside you C function code by using Server Programming Interface (SPI). I suggest you read more about it here to see exactly what you need to make your code work.

answered Apr 03 '23 at 18:31

Panagiotis Foliadis

254
1
1
8

How to send a SQL command inside a C-language function on a PostgreSQL extension (Apache AGE)?

4 Answers4