Sum bytea in postgres

Question

I have stored procedure in postgres with a variable like

DECLARE 
    totLen  BYTEA;
BEGIN 
     totLen = E'\\x000034';
     ....

totLen must be exactly 3 bytes an I have to sum other value like

totLen = totLen + 1;

I try totLen = totLen + E'\x01' but doesn't work. What's the correct solution?

What are you trying to achieve? Why is the length of a PL/pgSQL variable important for you? — Laurenz Albe, Jan 13 '17 at 12:10
This is not the point, but you want to know I'm writing BUFR (https://en.wikipedia.org/wiki/BUFR) message. — elia_c, Jan 13 '17 at 13:38
The question remains: why do you care about the length of a *variable*? You cannot do arithmetic with `bytea` values. You can very likely achieve what you want to do in a much simpler fashion. If only you would share the big picture of what you are trying to do. — Laurenz Albe, Jan 13 '17 at 13:55
Because I'm writing something that require to use fixed bytes length, so, for example, totLen must be exactly 3 bytes, and it's dynamically construct, so I need to do arithmetic with it. If you have a easy way to do, please explain me, thanks. — elia_c, Jan 13 '17 at 14:09
If you don't actually use how a 3 byte arithmetic would overflow, there is no point to stick to it. You could just use `int` (aka. `int4`, which has 4 bytes and proper arithmetics) within your function & truncate its value when you are done. Possibly with [bit strings](https://www.postgresql.org/docs/current/static/functions-bitstring.html). — pozs, Jan 13 '17 at 14:41

score 0 · Answer 1 · answered Jan 13 '17 at 15:05

This is a function that treats the three bytes from offset offs of the bytea value b as three-byte big-endian integer and adds i to that number:

CREATE OR REPLACE FUNCTION add(b bytea, offs integer, i integer) RETURNS bytea
   LANGUAGE plpgsql IMMUTABLE STRICT AS
$$DECLARE
   result integer := get_byte(b, offs) * 65536 +
                     get_byte(b, offs + 1) * 256 +
                     get_byte(b, offs + 2) +
                     i;
BEGIN
   IF result > 16777215 THEN
      RAISE EXCEPTION 'addition overflows';
   END IF;

   RETURN set_byte(
             set_byte(
                set_byte(
                   b,
                   offs,
                   result / 65536
                ),
                offs + 1,
                (result % 65536) / 256
             ),
             offs + 2,
             result % 256
          );
END;$$;

Sum bytea in postgres

1 Answers1