Claude generated get_table_ddl function

Claude generated get_table_ddl function

This is Claude's description of the code:
=============================================================================================================================

This C function creates a PostgreSQL extension function called get_table_ddl that generates the CREATE TABLE DDL for a given table without using SPI. Here's what it does:

Key Features:

    Takes a table name as input (supports schema.table format)
    Returns the complete CREATE TABLE statement as text
    Handles column definitions with proper data types and type modifiers
    Includes NOT NULL constraints and DEFAULT values
    Adds table constraints (PRIMARY KEY, UNIQUE, CHECK)
    Uses direct catalog access instead of SPI

How it works:

    Table Resolution: Uses RangeVarGetRelid() to resolve the table name to an OID
    Catalog Access: Opens the relation and accesses system catalogs directly using syscache functions
    Column Processing: Iterates through table attributes, formats type names with modifiers
    Constraint Handling: Scans pg_constraint to find table constraints
    DDL Generation: Builds the complete CREATE TABLE statement

To compile and install:
sql

-- Create the function in PostgreSQL
CREATE OR REPLACE FUNCTION get_table_ddl(table_name text)
RETURNS text
AS '$libdir/your_extension', 'get_table_ddl'
LANGUAGE C STRICT;

Usage example:
sql

SELECT get_table_ddl('public.users');

The function avoids SPI by using PostgreSQL's backend catalog access functions like SearchSysCache, systable_beginscan, and direct relation access through relation_open. This approach is more efficient than SPI for this type of metadata query and gives you direct access to the catalog structures.

get_table_ddl.c

#include "postgres.h"
#include "fmgr.h"
#include "utils/builtins.h"
#include "utils/lsyscache.h"
#include "utils/syscache.h"
#include "catalog/pg_class.h"
#include "catalog/pg_attribute.h"
#include "catalog/pg_type.h"
#include "catalog/pg_constraint.h"
#include "catalog/pg_index.h"
#include "catalog/pg_namespace.h"
#include "catalog/indexing.h"
#include "access/htup_details.h"
#include "utils/rel.h"
#include "utils/relcache.h"
#include "commands/defrem.h"
#include "parser/parse_type.h"

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(get_table_ddl);

static void append_column_definition(StringInfo ddl, Relation rel, int attnum);
static void append_constraints(StringInfo ddl, Oid relid, const char *table_name);
static char *get_type_name_with_typemod(Oid typid, int32 typmod);

Datum
get_table_ddl(PG_FUNCTION_ARGS)
{
    text *table_name_text = PG_GETARG_TEXT_PP(0);
    char *table_name = text_to_cstring(table_name_text);
    
    RangeVar *rv;
    Oid relid;
    Relation rel;
    TupleDesc tupdesc;
    StringInfoData ddl;
    int i;
    bool first_column = true;
    
    /* Parse the table name (handle schema.table format) */
    rv = makeRangeVarFromNameList(textToQualifiedNameList(table_name_text));
    
    /* Get the relation OID */
    relid = RangeVarGetRelid(rv, NoLock, false);
    
    /* Open the relation */
    rel = relation_open(relid, AccessShareLock);
    
    /* Verify it's a table */
    if (rel->rd_rel->relkind != RELKIND_RELATION)
    {
        relation_close(rel, AccessShareLock);
        ereport(ERROR,
                (errcode(ERRCODE_WRONG_OBJECT_TYPE),
                 errmsg("\"%s\" is not a table", table_name)));
    }
    
    tupdesc = RelationGetDescr(rel);
    
    /* Initialize the DDL string */
    initStringInfo(&ddl);
    
    /* Start building the CREATE TABLE statement */
    appendStringInfo(&ddl, "CREATE TABLE %s.%s (\n",
                     quote_identifier(get_namespace_name(rel->rd_rel->relnamespace)),
                     quote_identifier(RelationGetRelationName(rel)));
    
    /* Add column definitions */
    for (i = 0; i < tupdesc->natts; i++)
    {
        Form_pg_attribute attr = TupleDescAttr(tupdesc, i);
        
        /* Skip dropped columns */
        if (attr->attisdropped)
            continue;
            
        if (!first_column)
            appendStringInfoString(&ddl, ",\n");
        else
            first_column = false;
            
        append_column_definition(&ddl, rel, attr->attnum);
    }
    
    /* Add table constraints */
    append_constraints(&ddl, relid, RelationGetRelationName(rel));
    
    appendStringInfoString(&ddl, "\n);");
    
    /* Close the relation */
    relation_close(rel, AccessShareLock);
    
    PG_RETURN_TEXT_P(cstring_to_text(ddl.data));
}

static void
append_column_definition(StringInfo ddl, Relation rel, int attnum)
{
    HeapTuple tuple;
    Form_pg_attribute attr;
    char *type_name;
    bool has_default = false;
    char *default_expr = NULL;
    
    /* Get attribute info from syscache */
    tuple = SearchSysCache2(ATTNAME,
                           ObjectIdGetDatum(RelationGetRelid(rel)),
                           Int16GetDatum(attnum));
    
    if (!HeapTupleIsValid(tuple))
        elog(ERROR, "cache lookup failed for attribute %d of relation %u",
             attnum, RelationGetRelid(rel));
             
    attr = (Form_pg_attribute) GETSTRUCT(tuple);
    
    /* Column name */
    appendStringInfo(ddl, "    %s ", quote_identifier(NameStr(attr->attname)));
    
    /* Column type */
    type_name = get_type_name_with_typemod(attr->atttypid, attr->atttypmod);
    appendStringInfoString(ddl, type_name);
    pfree(type_name);
    
    /* NOT NULL constraint */
    if (attr->attnotnull)
        appendStringInfoString(ddl, " NOT NULL");
    
    /* Default value */
    if (attr->atthasdef)
    {
        HeapTuple def_tuple;
        Form_pg_attrdef def_struct;
        
        def_tuple = SearchSysCache2(ATTRDEFAULT,
                                   ObjectIdGetDatum(RelationGetRelid(rel)),
                                   Int16GetDatum(attnum));
        
        if (HeapTupleIsValid(def_tuple))
        {
            Datum def_datum;
            bool isnull;
            
            def_struct = (Form_pg_attrdef) GETSTRUCT(def_tuple);
            def_datum = SysCacheGetAttr(ATTRDEFAULT, def_tuple,
                                       Anum_pg_attrdef_adbin, &isnull);
            
            if (!isnull)
            {
                default_expr = TextDatumGetCString(def_datum);
                appendStringInfo(ddl, " DEFAULT %s", default_expr);
                pfree(default_expr);
            }
            
            ReleaseSysCache(def_tuple);
        }
    }
    
    ReleaseSysCache(tuple);
}

static void
append_constraints(StringInfo ddl, Oid relid, const char *table_name)
{
    ScanKeyData skey[1];
    SysScanDesc scan;
    HeapTuple tuple;
    Relation conrel;
    bool first_constraint = true;
    
    /* Open pg_constraint catalog */
    conrel = table_open(ConstraintRelationId, AccessShareLock);
    
    /* Set up scan key for this table */
    ScanKeyInit(&skey[0],
                Anum_pg_constraint_conrelid,
                BTEqualStrategyNumber, F_OIDEQ,
                ObjectIdGetDatum(relid));
    
    scan = systable_beginscan(conrel, ConstraintRelidTypidNameIndexId, true,
                             NULL, 1, skey);
    
    while (HeapTupleIsValid(tuple = systable_getnext(scan)))
    {
        Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(tuple);
        
        /* Skip constraints that aren't table constraints */
        if (con->contype != CONSTRAINT_PRIMARY &&
            con->contype != CONSTRAINT_UNIQUE &&
            con->contype != CONSTRAINT_CHECK)
            continue;
            
        appendStringInfoString(ddl, ",\n");
        
        /* Constraint name */
        appendStringInfo(ddl, "    CONSTRAINT %s ",
                        quote_identifier(NameStr(con->conname)));
        
        /* Constraint type and definition */
        switch (con->contype)
        {
            case CONSTRAINT_PRIMARY:
                appendStringInfoString(ddl, "PRIMARY KEY");
                break;
            case CONSTRAINT_UNIQUE:
                appendStringInfoString(ddl, "UNIQUE");
                break;
            case CONSTRAINT_CHECK:
                {
                    Datum consrc_datum;
                    bool isnull;
                    char *consrc;
                    
                    consrc_datum = SysCacheGetAttr(CONSTROID, tuple,
                                                  Anum_pg_constraint_consrc,
                                                  &isnull);
                    if (!isnull)
                    {
                        consrc = TextDatumGetCString(consrc_datum);
                        appendStringInfo(ddl, "CHECK (%s)", consrc);
                        pfree(consrc);
                    }
                }
                break;
        }
        
        /* For PRIMARY KEY and UNIQUE, add column list */
        if (con->contype == CONSTRAINT_PRIMARY || con->contype == CONSTRAINT_UNIQUE)
        {
            Datum conkey_datum;
            bool isnull;
            ArrayType *conkey_array;
            int16 *conkey;
            int nkeys;
            int i;
            
            conkey_datum = SysCacheGetAttr(CONSTROID, tuple,
                                          Anum_pg_constraint_conkey,
                                          &isnull);
            if (!isnull)
            {
                conkey_array = DatumGetArrayTypeP(conkey_datum);
                nkeys = ARR_DIMS(conkey_array)[0];
                conkey = (int16 *) ARR_DATA_PTR(conkey_array);
                
                appendStringInfoString(ddl, " (");
                for (i = 0; i < nkeys; i++)
                {
                    char *attname;
                    
                    if (i > 0)
                        appendStringInfoString(ddl, ", ");
                        
                    attname = get_attname(relid, conkey[i], false);
                    appendStringInfoString(ddl, quote_identifier(attname));
                    pfree(attname);
                }
                appendStringInfoString(ddl, ")");
            }
        }
    }
    
    systable_endscan(scan);
    table_close(conrel, AccessShareLock);
}

static char *
get_type_name_with_typemod(Oid typid, int32 typmod)
{
    HeapTuple tuple;
    Form_pg_type typeForm;
    char *typname;
    char *nspname;
    char *result;
    
    tuple = SearchSysCache1(TYPEOID, ObjectIdGetDatum(typid));
    if (!HeapTupleIsValid(tuple))
        elog(ERROR, "cache lookup failed for type %u", typid);
        
    typeForm = (Form_pg_type) GETSTRUCT(tuple);
    typname = NameStr(typeForm->typname);
    nspname = get_namespace_name(typeForm->typnamespace);
    
    /* Format type name with modifiers */
    if (typmod >= 0)
    {
        switch (typid)
        {
            case VARCHAROID:
            case BPCHAROID:
                result = psprintf("%s(%d)", typname, typmod - VARHDRSZ);
                break;
            case NUMERICOID:
                {
                    int precision = ((typmod - VARHDRSZ) >> 16) & 0xffff;
                    int scale = (typmod - VARHDRSZ) & 0xffff;
                    if (scale > 0)
                        result = psprintf("%s(%d,%d)", typname, precision, scale);
                    else
                        result = psprintf("%s(%d)", typname, precision);
                }
                break;
            default:
                result = pstrdup(typname);
                break;
        }
    }
    else
    {
        result = pstrdup(typname);
    }
    
    ReleaseSysCache(tuple);
    return result;
}