Binary Input/Output Functions

0 views
Skip to first unread message

David E. Wheeler

unread,
Oct 19, 2021, 8:11:29 PM10/19/21
to pgxn-...@googlegroups.com
Hello PGXNers,

I recently got a request to add binary in/out parameters to the semver data type:

https://github.com/theory/pg-semver/issues/59

Seems like it’d probably be pretty straightforward, but I’m not familiar with binary in/out functions. Could anyone point me at an example/precedent? I mean semver_in and semver_out return cstrings:

https://github.com/theory/pg-semver/blob/f480d475a655ec9fe88e22ac66e23fc263769e4a/src/semver.c#L321-L340

Might it be as simple as passing them to the RECEIVE and SEND params to CREATE TYPE? And how does one test them, anyway?

Thanks,

David

signature.asc

Daniele Varrazzo

unread,
Oct 20, 2021, 7:02:48 AM10/20/21
to PGXN Users
Hi David,

On Wed, 20 Oct 2021 at 02:11, David E. Wheeler <da...@justatheory.com> wrote:
>
> Hello PGXNers,
>
> I recently got a request to add binary in/out parameters to the semver data type:
>
> https://github.com/theory/pg-semver/issues/59

That's a pretty opinionated driver :D Many extension libraries don't
have a binary format (ltree, for instance).


> Seems like it’d probably be pretty straightforward, but I’m not familiar with binary in/out functions. Could anyone point me at an example/precedent? I mean semver_in and semver_out return cstrings:
>
> https://github.com/theory/pg-semver/blob/f480d475a655ec9fe88e22ac66e23fc263769e4a/src/semver.c#L321-L340
>
> Might it be as simple as passing them to the RECEIVE and SEND params to CREATE TYPE? And how does one test them, anyway?

Usually in send/receive you can take the binary format of your struct
and convert it in some hardware-independent way. Your datum seems to
be:

typedef struct semver {
int32 vl_len_; /* varlena header */
vernum numbers[3];
char prerel[]; /* pre-release, including the null byte for
convenience */
} semver;

so you might send out and receive a buffer with the three hton'd
numbers and the string as length/data.

I think the best source of examples is the postgres source itself, as
pretty much all data types have a binary format. They should be all in
the `src/backend/utils/adt/` directory. For instance this is int2
https://github.com/postgres/postgres/blob/41f30ecc29c89285d3eecd435906c4e9cb048be4/src/backend/utils/adt/int.c#L86-L106

Things you might want to consider is whether to version the binary format.

As for testing, I am not aware of a way to do so from sql, as you
can't access the "internal" data type in SQL. Maybe you can cheat and
CREATE FUNCTION a test function with the same C function but different
types to make them available to SQL. Another way would be to test
using Psycopg 3, which can use binary parameters and results
(https://www.psycopg.org/psycopg3/docs/basic/params.html#binary-data).

Hope this helps

-- Daniele

David E. Wheeler

unread,
Oct 30, 2021, 2:16:40 PM10/30/21
to pgxn-...@googlegroups.com
On Oct 20, 2021, at 07:02, Daniele Varrazzo <daniele....@gmail.com> wrote:

> Hi David,

HI Daniele. This is super helpful, thank you.

> That's a pretty opinionated driver :D Many extension libraries don't
> have a binary format (ltree, for instance).

Will likely tell them that pull requests are welcome.

> Usually in send/receive you can take the binary format of your struct
> and convert it in some hardware-independent way. Your datum seems to
> be:
>
> typedef struct semver {
> int32 vl_len_; /* varlena header */
> vernum numbers[3];
> char prerel[]; /* pre-release, including the null byte for
> convenience */
> } semver;
>
> so you might send out and receive a buffer with the three hton'd
> numbers and the string as length/data.
>
> I think the best source of examples is the postgres source itself, as
> pretty much all data types have a binary format. They should be all in
> the `src/backend/utils/adt/` directory. For instance this is int2
> https://github.com/postgres/postgres/blob/41f30ecc29c89285d3eecd435906c4e9cb048be4/src/backend/utils/adt/int.c#L86-L106

Thanks, just what I needed to know.

> Things you might want to consider is whether to version the binary format.
>
> As for testing, I am not aware of a way to do so from sql, as you
> can't access the "internal" data type in SQL. Maybe you can cheat and
> CREATE FUNCTION a test function with the same C function but different
> types to make them available to SQL. Another way would be to test
> using Psycopg 3, which can use binary parameters and results
> (https://www.psycopg.org/psycopg3/docs/basic/params.html#binary-data).

Yeah, might be able to CREATE FUNCTIONs that outputs hex or something.

Best,

David

signature.asc
Reply all
Reply to author
Forward
0 new messages