pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Concatenating bytea types...
3+ messages / 3 participants
[nested] [flat]

* Concatenating bytea types...
@ 2013-02-28 10:21 Marko Rihtar <rihtar.marko@gmail.com>
  2013-03-01 14:32 ` Re: Concatenating bytea types... Richard Huxton <dev@archonet.com>
  2013-03-02 05:07 ` Re: Concatenating bytea types... Jasen Betts <jasen@xnet.co.nz>
  0 siblings, 2 replies; 3+ messages in thread

From: Marko Rihtar @ 2013-02-28 10:21 UTC (permalink / raw)
  To: pgsql-sql

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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Concatenating bytea types...
  2013-02-28 10:21 Concatenating bytea types... Marko Rihtar <rihtar.marko@gmail.com>
@ 2013-03-01 14:32 ` Richard Huxton <dev@archonet.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Richard Huxton @ 2013-03-01 14:32 UTC (permalink / raw)
  To: Marko Rihtar <rihtar.marko@gmail.com>; +Cc: pgsql-sql

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


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Concatenating bytea types...
  2013-02-28 10:21 Concatenating bytea types... Marko Rihtar <rihtar.marko@gmail.com>
@ 2013-03-02 05:07 ` Jasen Betts <jasen@xnet.co.nz>
  1 sibling, 0 replies; 3+ messages in thread

From: Jasen Betts @ 2013-03-02 05:07 UTC (permalink / raw)
  To: pgsql-sql

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



-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2013-03-02 05:07 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-02-28 10:21 Concatenating bytea types... Marko Rihtar <rihtar.marko@gmail.com>
2013-03-01 14:32 ` Richard Huxton <dev@archonet.com>
2013-03-02 05:07 ` Jasen Betts <jasen@xnet.co.nz>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox