Pass struct as Custom type argument for PSQL function

[Jk1484]

Hi All,

Is there any way of passing struct as argument for psql function?
Lets say we have this struct:

type User struct {
	ID   int    `db:"id"`  
	Name string `db:"name"`  
	Age  int    `db:"age"`  
}

And this table, type and PSQL function:

create type user_type AS (
	id int,
	name text,
	age int
);

create table users (
id serial primary key,
name text,
age int
);

CREATE OR REPLACE FUNCTION public.create_user(
    request_user public.user_type
)
RETURNS public.user_type AS $$
DECLARE
    new_user public.user_type;
BEGIN
	INSERT INTO users (name, age)
	VALUES (request_user.name, request_user.age)
	RETURNING id, name, age
    INTO new_user;

    RETURN new_user;
END;
$$ LANGUAGE plpgsql;

Is there a way to do something like this?

query = `
SELECT *
FROM public.create_user(($1)::public.user_type)
`
u := User{
Name: "name3",
Age:  3,
}
var ur User

err = conn.QueryRow(ctx, query, u).Scan(&ur.ID, &ur.Name, &ur.Age)

The working SQL query is:

select * from public.create((1, 'name', 1));

[jackc]

Yes, that should be possible. The custom type needs to be registered with pgx.

See https://pkg.go.dev/github.com/jackc/pgx/[email protected]/pgtype#hdr-New_PostgreSQL_Type_Support and https://github.com/jackc/pgx/blob/master/pgtype/composite_test.go.

[Jk1484]

I had an issue where I was running my tests with dockertest. Spinning up postgres image in a docker container and running migrations and tests inside of it.
I was running multiple test cases where I needed to reset the state of the database. So I wanted to use transactions to rollback the changes after the tests. But I had custom types to register.

I ended up setting up my pgxpool like this:

func NewPgxPool(connString string) (*pgxpool.Pool, error) {
	config, err := pgxpool.ParseConfig(connString)
	if err != nil {
		return nil, fmt.Errorf("unable to parse config: %v", err)
	}

	config.BeforeAcquire = func(ctx context.Context, conn *pgx.Conn) bool {
		dataTypeNames := []string{
			"my_type",
		}

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

		return true
	}

	conn, err := pgxpool.NewWithConfig(context.Background(), config)
	if err != nil {
		return nil, err
	}

	return conn, nil
}

If someone is interested, rollback part of my tests would look something like this:

for _, scenario := range scenarios {
	t.Run(scenario.name, func(t *testing.T) {
		tx, err := conn.Begin(ctx)
		require.NoError(t, err)

		defer tx.Rollback(ctx)

		cabinets, err := scenario.setup(tx)
		if scenario.expectedErr {
			assert.Error(t, err)
		} else {
			assert.NoError(t, err)
		}

		assert.Len(t, cabinets, scenario.expectedLen)
	})
}