Thread

1. Concatenating bytea types...

   Marko Rihtar <rihtar.marko@gmail.com> — 2013-02-28T10:21:04Z

   Hi all,
   
   i have a little problem.
   I'm trying to rewrite one procedure from mysql that involves bytes
   concatenation.
   This is my snippet from postgres code:
   ...
   cv1 bytea;
   ...
   cv1 := E'\\000'::bytea;
   ...
   cv1 := CONCAT(cv1, DECODE(TO_HEX(11), 'escape'));
   ...
   this third line throws following error:
   invalid hexadecimal digit: "\"
   
   I run it through the debugger and saw that after assigning the zero byte
   value to cv1 variable, postgres automatically converts it to \x00.
   And then inside CONCAT it brakes with above error.
   
   Inside select it works fine
   select CONCAT(E'\\000'::bytea, DECODE(TO_HEX(11), 'escape'))
   select CONCAT('\x00'::bytea, DECODE(TO_HEX(11), 'escape'))
   
   Is there a way to solve this somehow?
   
   thanks for help,
   
   Marko
   

2. Re: Concatenating bytea types...

   Richard Huxton <dev@archonet.com> — 2013-03-01T14:32:07Z

   On 28/02/13 10:21, Marko Rihtar wrote:
   > Hi all,
   >
   > i have a little problem.
   > I'm trying to rewrite one procedure from mysql that involves bytes
   > concatenation.
   > This is my snippet from postgres code:
   
   You seem to be mixing up escape and hex literal formatting along with 
   decode(). The following should help.
   
   BEGIN;
   
   CREATE FUNCTION to_hex_pair(int) RETURNS text AS $$
        SELECT right('0' || to_hex($1), 2);
   $$ LANGUAGE sql;
   
   CREATE FUNCTION f_concat_bytea1(bytea, bytea) RETURNS text AS $$
   BEGIN
        RETURN $1 || $2;
   END;
   $$ LANGUAGE plpgsql;
   
   CREATE FUNCTION f_concat_bytea2(bytea, bytea) RETURNS bytea AS $$
   DECLARE
        cv1 bytea;
   BEGIN
        cv1 := '\x01'::bytea;
        RETURN $1 || cv1 || $2;
   END;
   $$ LANGUAGE plpgsql;
   
   CREATE FUNCTION f_concat_bytea3(text, text) RETURNS bytea AS $$
   DECLARE
        cv1 text;
   BEGIN
        cv1 := '01';
        RETURN decode($1 || cv1 || $2, 'hex');
   END;
   $$ LANGUAGE plpgsql;
   
   SELECT '\x000b'::bytea             AS want_this,
           decode('000b','hex')        AS or_this1,
           decode('\000\013','escape') AS or_this2;
   SELECT f_concat_bytea1('\x00', '\x0b');
   SELECT f_concat_bytea2('\x00', '\x0b');
   SELECT f_concat_bytea3('00', '0b');
   
   ROLLBACK;
   
   
   
   -- 
      Richard Huxton
      Archonet Ltd
   
   
   

3. Re: Concatenating bytea types...

   Jasen Betts <jasen@xnet.co.nz> — 2013-03-02T05:07:58Z

   On 2013-02-28, Marko Rihtar <rihtar.marko@gmail.com> wrote:
   > --047d7b603fca8e330f04d6c63f7b
   > Content-Type: text/plain; charset=ISO-8859-1
   >
   > Hi all,
   >
   > i have a little problem.
   
   > cv1 := CONCAT(cv1, DECODE(TO_HEX(11), 'escape'));
   
   what's that supposed to do? if I were to fix it how would I know?
   
   
   
   
   
   -- 
   ⚂⚃ 100% natural