How to test a TYPE's send and receive functions in regresssion test in PostgreSQL

user_10744017 0
Viewed 26

I create a new type mytype in PostgreSQL according to https://www.postgresql.org/docs/current/sql-createtype.html with send and receive functions as mytype_send and mytype_receive.

However, I don't know how to make sure I implemented those functions correctly. I'd like to add some functions to test them in my regression test, or at least cover them in my coverage test.

postgresql
1 Answers

COPY ... (FORMAT binary) uses the type's send/receive functions to serialise/deserialise data.

For example, running this command:

COPY (SELECT '!!!'::text) TO '/tmp/test.bin' (FORMAT binary)

...will produce this output:

enter image description here

You can use this to check that the send output is correct.

You can also load this data back into the server to test the receive function, e.g.:

CREATE TABLE tmp (val text);
COPY tmp FROM '/tmp/test.bin' (FORMAT binary);
SELECT * FROM t WHERE val = '!!!';

Note that COPY ... TO '<filename>' assumes you have access to the server's file system. If you're running your tests from a client application, you probably want to use COPY ... TO STDOUT and access the data stream through your driver (e.g. PGConnection.getCopyAPI() in pgJDBC).

It's worth mentioning that there is a much simpler way of testing the send function. As it returns a bytea value, it can be called directly from SQL:

SELECT textsend('!!!'::text) = '\x212121'::bytea

Unfortunately I don't think there is any way to invoke the receive function directly, due to its use of the internal pseudo-type.

user_11049790
Related