0

I need help with write array[] bytea using libpq PQexecParams

I'v done a simple version where i'm write a single binary data in a single bytea arg using PQexecParams like this solution Insert Binary Large Object (BLOB) in PostgreSQL using libpq from remote machine

But I have problems with write array[] bytea like this

select * func(array[3, 4], array[bytea, bytea])

Ieshir
  • 31
  • 7
  • Does this answer your question? [Insert Binary Large Object (BLOB) in PostgreSQL using libpq from remote machine](https://stackoverflow.com/questions/8994702/insert-binary-large-object-blob-in-postgresql-using-libpq-from-remote-machine) – Pavel Korotkevich Feb 15 '23 at 15:21
  • What exactly is the problem? – Laurenz Albe Feb 15 '23 at 15:35
  • @PavelKorotkevich No, this is insert single blob. API in database with i work requeired array[bytea, bytea...] array[int,int ...] etc – Ieshir Feb 15 '23 at 16:39
  • @LaurenzAlbe I don't understand how to make correct params for function PQexecParams. With single object that what easy and work fine – Ieshir Feb 15 '23 at 16:40
  • A `bytea` is just a single object. It is no different than other data. – Laurenz Albe Feb 15 '23 at 16:53
  • @LaurenzAlbe Edited the question for some details – Ieshir Feb 15 '23 at 17:14

1 Answers1

1

Unless you want to read the PostgreSQL source to figure out the binary format for arrays, you will have to use the text format, e.g.

{\\xDEADBEEF,\\x00010203}

The binary format for arrays is defined in array_send in src/backend/utils/adt/arrayfuncs.c. Have a look at the somments and definitions in src/include/utils/array.h as well. Consider also that all integers are sent in “network bit order”.

One way you can examine the binary output format and saving yourself a lot of trouble experimenting is to use binary copy, e.g.

COPY (SELECT ARRAY['\xDEADBEEF'::bytea,'\x00010203'::bytea])
   TO '/tmp/file' (FORMAT 'binary');
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • I thied this, but libpq reject my query with different reason -> invalid utf-8, slashed, quotes, null symbol etc. hex work, but convert string to \ from hex representation symbol by symbol is slow. Some more info my binary string is a protobuf serialization result.. Maybe has a good variant for escaping this? Pgescaping is not a good choice, but my variants was rejected by libpq – Ieshir Feb 15 '23 at 17:34
  • I forgot to escape the backslashes, and I have added some pointers to the source. – Laurenz Albe Feb 15 '23 at 19:35
  • escape the backslashes is not a big problem, i already write that tried to use different way(escaping, hex, base64, E\, $$ tokens erc). PQExec work only with text format - it not good for me. I want send my data with native bytes... gone to read libpq sources :( – Ieshir Feb 15 '23 at 20:26
  • You don't need libpq sources, you need PostgreSQL server sources, as indicated in my answer. – Laurenz Albe Feb 16 '23 at 03:28