How to correctly use Custom Types

[SennaSanzo]

Hi everyone,

I'm starting to learn pgx and Go, and I'm trying to use a simple custom type in my project. I've found many discussions about composite types, custom types, how to register them, load them, etc. But nothing helped me... My only problem for now is how to match a composite type from PostgreSQL to my Go struct. Let's take this example:
SQL:

CREATE TYPE agreement_type AS (
    contract_id UUID,
    apartment_id UUID,
    first_rent INTEGER,
    first_charges INTEGER,
    entry_date DATE,
    release_date DATE,
    created_at TIMESTAMPTZ,
    updated_at TIMESTAMPTZ
);

CREATE TYPE user_type AS (
    user_id UUID,
    email TEXT,
    first_name TEXT,
    last_name TEXT,
    thumbnail_id UUID,
    password TEXT,
    phone TEXT,
    gender TEXT,
    company TEXT,
    is_active BOOLEAN,
    permissions TEXT[],
    created_at TIMESTAMPTZ,
    updated_at TIMESTAMPTZ,
    agreements agreement_type[]
);

GO:

type User struct {
	ID       uuid.UUID
	Email    string 
	FirstName string
	LastName string
	ThumbnailID uuid.UUID
	Password string
	Phone string
	Gender string
	Company string
	//TenantDetails
	IsActive bool
	IsOwner bool
	Permissions []UserPermission
	CreatedAt time.Time
	UpdatedAt time.Time
	Agreements []Contract
}

I registered the type using the code provided in the pgtype documentation, but my issue is how to pass a Go User struct instance with the nested struct slice to a PostgreSQL function. I mean I think I understand the way pgx is scanning a query result into a golang struct, but how to do the opposite when you need to insert or update data? I don't see any examples in the docs about that. Currently, pgx is not matching the composite type to my User struct. I'm doing something like this:

conn.Exec( `SELECT create_dummy_user($1::user_type)`, user)

But I get an error like:

"unable to encode models.User{ID:uuid.UUID (...) into text format for unknown type (OID 191201): cannot find encode plan"

This error seems to tell me that golang struct is not mapped to the PostgreSQL CT which is expected. I'm also a bit confused about binary and texts formats. Is binary format not the default in pgx?

This example is pretty simple and there is no real need for composite types and mapping here. But in my project I will have more relations and nested objects and my goal is to deliver a unique golang object as parameter of a plsql function and let the PostgreSQL do the loops and treatments of update/insert/delete.

If someone can help, it would be greatly appreciated. Thanks!

EDIT: I let the previous code for history but it is full of mistakes. Look at the answer for a good implementation.

[SennaSanzo]

I finally found my mistake, and everything is now working perfectly. I'm sharing the solution here in case someone else encounters the same issue:

In the function responsible for registering custom types, I simply forgot to register the default array type corresponding to my custom type. Although I had seen this particular example in the pgtype documentation:

func RegisterDataTypes(ctx context.Context, conn *pgx.Conn) error {
  dataTypeNames := []string{
    "foo",
    "_foo",
    "bar",
    "_bar",
  }

  for _, typeName := range dataTypeNames {
    dataType, err := conn.LoadType(ctx, typeName)
    if err != nil {
      return err
    }
    conn.TypeMap().RegisterType(dataType)
  }

  return nil
}

Source

... I didn't realize that an _arraytype is automatically created when you define a composite type in PostgreSQL. Unfortunately, the pgx documentation provides limited information on this topic which is more PostgreSQL related I must admit, so my bad. I put the link here of the page talking about it if someone is interested : Look in the Array Types section

Once I added _customtype to my registration list, everything started to work as expected. It might be helpful to include examples of such scenarios in the documentation for newcomers like me. This seems to be a common requirement, especially when dealing with nested struct slices. If this is already explained somewhere and I missed it, please feel free to share the link to help others like me.

Thanks again for this wonderful package, @jackc!

New example implementation which is working:

CREATE TYPE pet AS (
    id UUID
    name TEXT,
    age NUMERIC
);

CREATE TYPE dummy AS (
    id UUID,
    name TEXT,    
    pets pet[]
);

CREATE TABLE IF NOT EXISTS dummies(
    dummy_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT
);

CREATE TABLE IF NOT EXISTS dummy_pets(
    pet_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    dummy_id UUID REFERENCES dummies(dummy_id),
    name TEXT,
    age NUMERIC
);

CREATE OR REPLACE FUNCTION create_dummy_user(p_user dummy)
RETURNS VOID AS $$
DECLARE
    apet pet;
    v_dummy_id UUID;
BEGIN
  INSERT INTO dummies(name) VALUES(p_user.name) RETURNING dummy_id INTO v_dummy_id;

  FOREACH apet IN ARRAY p_user.pets LOOP
    INSERT INTO dummy_pets(dummy_id, name, age) VALUES(v_dummy_id, apet.name, apet.age);
  END LOOP;
END;
$$ LANGUAGE plpgsql;

type pet struct {
                ID uuid.UUID
		Name string
		Age int
	}

type dummy struct {
		ID uuid.UUID
		Name string
		Pets []pet
	}

dummyUser := dummy {
		Name: "dummy",
		Pets: []pet{
			{
				Name: "Fido",
				Age: 3,
			},
			{
				Name: "Rex",
				Age: 5,
			},
		},
	}

//Change the way you exec your request as needed here
conn.Exec(ctx,`SELECT create_dummy_user($1::dummy)`, dummyUser)

and use the LoadType and RegisterType seen above with the following type names and order (which is important)

dataTypeNames := []string{"pet", "_pet", "dummy"}

[SennaSanzo]

In addition of what I did on my previous answer with inserts, here is the other side of the process if you need to retrieve multiple objects with nested arrays. Let's say we have such a PostgreSQL function which is building the objects for us :

CREATE OR REPLACE FUNCTION get_all_dummies_with_pets()
RETURNS SETOF dummy AS $$
BEGIN
    RETURN QUERY
    SELECT
        d.dummy_id AS id,
        d.name,
        COALESCE(
            ARRAY(
                SELECT ROW(p.pet_id, p.name, p.age)::pet
                FROM dummy_pets p
                WHERE p.dummy_id = d.dummy_id
                ORDER BY p.pet_id
            ),
            ARRAY[]::pet[]
        ) AS pets
    FROM dummies d;
END;
$$ LANGUAGE plpgsql;

Then this is easy with pgx to retrieve all in one like this:

rows, err := conn.Query(ctx, `SELECT get_all_dummies_with_pets()`)

var dummies []dummy
	dum := &dummy{}
	pgx.ForEachRow(rows, []any{ dum }, func() error {
		dummies = append(dummies, *dum)
		return nil
	})

Keep in mind that you still need to load and register your types before querying the DB.
Again, I’m sharing this here in case it can help someone. I don’t know if I’m doing everything right, but it is now pretty straightforward for me to modify my entities and retrieve them in a very simple manner. Don’t hesitate to comment if I’m completely wrong or if there are better approaches to do this.

Thanks.