CODEWITHSUNDEEP
cpostgresqlpostgresql-extensions
Rahma Begag
Rahma Begag

Reputation: 89

how to check table's column's data type of postgresql using c?

I am working on creating a postgresql extension, till now I only wanted to test if one of table's columns is bytea type to store the table's name , but when I test it with :

CREATE TABLE test_table (
    id SERIAL PRIMARY KEY,
    data bytea
);

it says that no bytea columns found in table...

here's my c code :

#include "postgres.h"
#include "fmgr.h"
#include "commands/event_trigger.h"
#include "parser/parse_node.h"
#include "executor/spi.h"
#include "utils/builtins.h"
#include "catalog/pg_type.h"
#include "nodes/pg_list.h"

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(log_table_creation);

Datum
log_table_creation(PG_FUNCTION_ARGS)
{
    EventTriggerData *trigdata;
    const char *tag;
    int ret;

    if (!CALLED_AS_EVENT_TRIGGER(fcinfo))
        elog(ERROR, "not fired by event trigger manager");

    trigdata = (EventTriggerData *) fcinfo->context;
    tag = GetCommandTagName(trigdata->tag);

    if (strcmp(tag, "CREATE TABLE") != 0)
        PG_RETURN_NULL();

    // Cast parsetree to CreateStmt to access the table structure
    CreateStmt *createStmt = (CreateStmt *) trigdata->parsetree;
    RangeVar *relation = createStmt->relation;

    // Check if any column has type `bytea`
    bool has_bytea_column = false;
    ListCell *cell;

    foreach(cell, createStmt->tableElts)
    {
        ColumnDef *colDef = (ColumnDef *) lfirst(cell);

        // Check for type name "bytea" explicitly
        if (list_length(colDef->typeName->names) == 2) // {"pg_catalog", "bytea"}
        {
            // Extract schema and type as strings
            const char *schema = strVal(linitial(colDef->typeName->names));
            const char *type = strVal(lsecond(colDef->typeName->names));

            if (strcmp(schema, "pg_catalog") == 0 && strcmp(type, "bytea") == 0)
            {
                has_bytea_column = true;
                break;
            }
        }
    }

    // Only log the table if it has a `bytea` column
    if (has_bytea_column)
    {
        // Prepare and execute the insertion into table_log
        SPI_connect();
        char *query = psprintf("INSERT INTO table_log (table_name) VALUES ('%s')", relation->relname);
        ret = SPI_execute(query, false, 0);
        SPI_finish();

        if (ret != SPI_OK_INSERT)
            elog(ERROR, "Failed to insert into table_log");
    }
    else
    {
        elog(NOTICE, "No BYTEA columns found in table %s", relation->relname);
    }

    PG_RETURN_NULL();
}

and her's the sql script:

CREATE TABLE table_log (
id SERIAL PRIMARY KEY,
table_name TEXT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE FUNCTION log_table_creation()
RETURNS event_trigger
LANGUAGE c
AS 'MODULE_PATHNAME', 'log_table_creation';

CREATE EVENT TRIGGER table_creation_logger
ON ddl_command_end
WHEN TAG IN ('CREATE TABLE')
EXECUTE FUNCTION log_table_creation();

it all new to me,so if anyone could help me with it, and if there's tutorials it would be the best. thank you

Upvotes: 0

Views: 49

Answers (1)

Rahma Begag
Rahma Begag

Reputation: 89

this is how i fixed it

// Event trigger function to log tables created with a bytea column
Datum
log_table_creation(PG_FUNCTION_ARGS)
{
EventTriggerData *trigdata;
const char *tag;
int ret;

if (!CALLED_AS_EVENT_TRIGGER(fcinfo))
    elog(ERROR, "not fired by event trigger manager");

trigdata = (EventTriggerData *) fcinfo->context;
tag = GetCommandTagName(trigdata->tag);

if (strcmp(tag, "CREATE TABLE") != 0)
    PG_RETURN_NULL();

// Cast parsetree to CreateStmt to access the table structure
CreateStmt *createStmt = (CreateStmt *) trigdata->parsetree;
RangeVar *relation = createStmt->relation;

// Check if any column has type `bytea`
bool has_bytea_column = false;
ListCell *cell;

foreach(cell, createStmt->tableElts)
{
    ColumnDef *colDef = (ColumnDef *) lfirst(cell);

    // Retrieve the type OID directly
    Oid typeOid = typenameTypeId(NULL, colDef->typeName);

    // Log the OID of each column's type for debugging
    elog(INFO, "Column %s has type OID: %u", colDef->colname, typeOid);

    // Check if the type OID matches `BYTEAOID`
    if (typeOid == BYTEAOID)
    {
        has_bytea_column = true;
        break;
    }
}

// Only log the table if it has a `bytea` column
if (has_bytea_column)
{
    SPI_connect();
    char *query = psprintf("INSERT INTO table_log (table_name) VALUES ('%s')", relation->relname);
    ret = SPI_execute(query, false, 0);
    SPI_finish();

    if (ret != SPI_OK_INSERT)
        elog(ERROR, "Failed to insert into table_log");
    else
        elog(INFO, "Table %s with BYTEA column logged successfully", relation->relname);
}
else
{
    elog(NOTICE, "No BYTEA columns found in table %s", relation->relname);
}

PG_RETURN_NULL();
}

Upvotes: 0

Related Questions