Is it possible to create a user defined type with plpgsql or SQL?

Question

Distinct from a DOMAIN I was wanting to know if it was possible to create a user-defined type without C.

Specifically I wanted to create a type like this

CREATE TYPE name (
    INPUT = input_function,
    OUTPUT = output_function
    [ , RECEIVE = receive_function ]
    [ , SEND = send_function ]
    [ , TYPMOD_IN = type_modifier_input_function ]
    [ , TYPMOD_OUT = type_modifier_output_function ]
    [ , ANALYZE = analyze_function ]
    [ , INTERNALLENGTH = { internallength | VARIABLE } ]
    [ , PASSEDBYVALUE ]
    [ , ALIGNMENT = alignment ]
    [ , STORAGE = storage ]
    [ , LIKE = like_type ]
    [ , CATEGORY = category ]
    [ , PREFERRED = preferred ]
    [ , DEFAULT = default ]
    [ , ELEMENT = element ]
    [ , DELIMITER = delimiter ]
    [ , COLLATABLE = collatable ]
)

input_function and output_function both say,

The input_function converts the type's external textual representation to the internal representation used by the operators and functions defined for the type. output_function performs the reverse transformation. The input function can be declared as taking one argument of type cstring, or as taking three arguments of types cstring, oid, integer. The first argument is the input text as a C string, the second argument is the type's own OID (except for array types, which instead receive their element type's OID), and the third is the typmod of the destination column, if known (-1 will be passed if not). The input function must return a value of the data type itself. Usually, an input function should be declared STRICT; if it is not, it will be called with a NULL first parameter when reading a NULL input value. The function must still return NULL in this case, unless it raises an error. (This case is mainly meant to support domain input functions, which might need to reject NULL inputs.) The output function must be declared as taking one argument of the new data type. The output function must return type cstring. Output functions are not invoked for NULL values.

In both cases, a cstring is called for, but in either case here, when I try to create it I get

ERROR: PL/pgSQL functions cannot accept type cstring

Or,

ERROR: SQL functions cannot have arguments of type cstring

Can this be done without C?

Answer 1

In PostgreSQL, it is not possible to create a user-defined type without using C. The CREATE TYPE statement is designed to create complex data types that require low-level implementation and manipulation, which typically involves writing C functions.

The error message you received indicates that PL/pgSQL functions (functions written in the PostgreSQL procedural language) cannot accept or have arguments of type cstring. Additionally, SQL functions (functions written in SQL) also cannot have arguments of type cstring.

If you want to create a user-defined type with custom input and output functions, you would need to implement those functions in C using the PostgreSQL extension API. The input function would handle the conversion of the type's external textual representation to the internal representation, and the output function would perform the reverse transformation.

Here's an example of how a user-defined type with custom input and output functions can be created using C:

#include "postgres.h"
#include "fmgr.h"
#include "utils/builtins.h"

PG_MODULE_MAGIC;

typedef struct
{
    /* Define your type's internal representation here */
    /* ... */
} MyType;

PG_FUNCTION_INFO_V1(mytype_in);

Datum mytype_in(PG_FUNCTION_ARGS)
{
    char* str = PG_GETARG_CSTRING(0);
    /* Implement the conversion logic from external textual representation to internal representation */
    /* ... */
    PG_RETURN_POINTER(result);
}

PG_FUNCTION_INFO_V1(mytype_out);

Datum mytype_out(PG_FUNCTION_ARGS)
{
    MyType* mytype = (MyType*)PG_GETARG_POINTER(0);
    /* Implement the conversion logic from internal representation to external textual representation */
    /* ... */
    PG_RETURN_CSTRING(result);
}

/* Register the input and output functions */

void _PG_init(void)
{
    /* Define the user-defined type here using the appropriate API functions */
    /* ... */
}

Please note that creating user-defined types with custom input and output functions using C requires familiarity with C programming and the PostgreSQL extension development process. You will need to compile the C code into a shared library (extension) and install it into your PostgreSQL server.