how to write a Postgres user definded type with array

Question

I'm writing a user defined type in Postgres called personname:

#define FLXIBLE_ARRAY_MEMBER 0

PG_MODULE_MAGIC;

typedef struct personname{
    int familyLen;
    int givenLen;
    int givenStart;
    char pname[FLXIBLE_ARRAY_MEMBER];
}personname;

I write my personname_in and personname_out function roughly like this:

PG_FUNCTION_INFO_V1(pname_in);

Datum
pname_in(PG_FUNCTION_ARGS){
    char* str = PG_GETARG_CSTRING(0);
    personname *name;
    ...
    name = (personname*) palloc(sizeof(personname) + strlen(str) + 1);
    name->familyLen = familyNameLen;
    name->givenLen = givenNameLen;
    name->givenStart = givenNameStart;

    strcpy(name->pname, str);

    PG_RETURN_POINTER(name);
}

PG_FUNCTION_INFO_V1(pname_out);

Datum
pname_out(PG_FUNCTION_ARGS){
    personname *name = (personname*) PG_GETARG_POINTER(0);

    char* family = getFamily(name);
    char* given = getGiven(name);
    char* nameStr;   
    nameStr = psprintf("%s,%s", family, given);

    pfree(family);
    pfree(given);

    PG_RETURN_CSTRING(nameStr);
}

And my sql is like this:

CREATE FUNCTION pname_in(cstring)
   RETURNS personname
   AS '_OBJWD_/pname'
   LANGUAGE C IMMUTABLE STRICT;

CREATE FUNCTION pname_out(personname)
   RETURNS cstring
   AS '_OBJWD_/pname'
   LANGUAGE C IMMUTABLE STRICT;

CREATE TYPE personname (
   internallength = 12,
   input = pname_in,
   output = pname_out
);

Now my code can correctly respond with select "NAME" :: personname;, and when I insert and select, it can correctly access to all arguments in personname except the pname array.

I create a table called users which contains the pname array, when I type select * from users; it shows this:

However, when I copy and paste my personname_in and personname_out code in another c file, replace palloc with malloc and test it with some input string from terminal, it can print correct pname value.

Could someone please tell me where did I do wrong, or what's the correct way to create a new type in PostgreSQL with array?

Why do you use C for that? You can create such a type with plain SQL, no need to write a C extension — user330315
– user330315, Commented Mar 11, 2020 at 9:23
Yes, I write in and out functions in a C file, and I also need some other operator functions so I have to use C. — Andrew Zhang
– Andrew Zhang, Commented Mar 11, 2020 at 9:35

Laurenz Albe · Accepted Answer · 2020-03-11 11:02:53Z

The CREATE TYPE statement does not fit the code, and the 4-byte varlena header is missing.

Qoth the documentation:

While the details of the new type's internal representation are only known to the I/O functions and other functions you create to work with the type, there are several properties of the internal representation that must be declared to PostgreSQL. Foremost of these is internallength. Base data types can be fixed-length, in which case internallength is a positive integer, or variable-length, indicated by setting internallength to VARIABLE. (Internally, this is represented by setting typlen to -1.) The internal representation of all variable-length types must start with a 4-byte integer giving the total length of this value of the type. (Note that the length field is often encoded, as described in Section 68.2; it's unwise to access it directly.)

You must define the type with

INTERNALLENGTH = VARIABLE

and the struct has to start with a 4-byte integer.

I didn't check for others errors.

Collectives™ on Stack Overflow

how to write a Postgres user definded type with array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related