Percent-encoding conversion to binary, %C2%A9 = ©

Hi

I have a little trouble with the chr() function.

I have a string like this:
"Copyright+%C2%A9+1856+Na%C3%AFve+retros%C2%AE"
which should be converted to binary string like:
"Copyright © 1856 Naïve retros®"

Is there an easy way to do this conversion?

I have tried to do it with a function, but it fails. The script is at
the end of the mail.

Description of percent encoding:
http://en.wikipedia.org/wiki/Percent-encoding

/hans
---------------------------------------------------------------

drop function percent2bin(text);
create function percent2bin(text) returns text as $Q$
declare
intxt alias for $1;
res text;
r text;
idx int;
c text;
n1 int;
begin
RAISE NOTICE 'Input: %',intxt;
r := regexp_replace(intxt, E'\\+', ' ', 'g');
idx := 1;
res := '';
while idx <= length(r) loop
c := substring(r from idx for 1);
if c = '%' and idx+2<=length(r) then
n1 := asciihex2bin(substring(r from idx+1 for 2));
RAISE NOTICE 'Char: %, %',substring(r from idx+1 for 2),n1;
if 0 <= n1 and n1 <= 255 then
c := chr(n1); -- HERE IT GOES WRONG
idx := idx + 2;
end if;
end if;
res := res || c;
idx := idx + 1;
end loop;
return res;
end;
$Q$ language PLpgSQL;

rop function asciihex2bin(text);
create function asciihex2bin(text) returns int as $Q$
declare
intxt alias for $1;
n1 int;
n2 int;
idx int;
begin
n1 := 0;
for idx in 1..2 loop
-- get char and convert to binary nul, '0' to '9' will be 0 to 9
n2 := ascii(upper(substring(intxt from idx for 1)))-48;
-- if input was 'A' to 'F', subtract 7
if n2 > 9 then
n2 := n2 - 7;
end if;
-- if out of range, fail
if n2 < 0 or n2 > 15 then
RAISE NOTICE 'Input fault expected 0..15, got: % "%"', n2, intxt;
n2 := 1000;
end if;
if idx = 1 then
n1 := n2 * 16;
else
n1 := n1 + n2;
end if;
end loop;
return n1;
end;
$Q$ language PLpgSQL;

select asciihex2bin('00');
select asciihex2bin('FF');

select percent2bin('Copyright+%C2%A9+1856+Na%C3%AFve+retros%C2%AE');
>From pgsql-general-owner(at)postgresql(dot)org Thu Mar 13 12:20:13 2008
Received: from localhost (unknown [200.46.204.183])
by postgresql.org (Postfix) with ESMTP id 442AF2E0073
for <pgsql-general-postgresql(dot)org(at)postgresql(dot)org>; Thu, 13 Mar 2008 12:20:13 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
with ESMTP id 87423-02 for <pgsql-general-postgresql(dot)org(at)postgresql(dot)org>;
Thu, 13 Mar 2008 12:20:03 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
by postgresql.org (Postfix) with ESMTP id C455B2E004C
for <pgsql-general(at)postgresql(dot)org>; Thu, 13 Mar 2008 12:20:01 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl(at)localhost [127.0.0.1])
by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m2DFJxH0017243;
Thu, 13 Mar 2008 11:19:59 -0400 (EDT)
To: Nikhil Bokare <nbokare(at)gmail(dot)com>
cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Segementation fault in PQgetvalue()
In-reply-to: <463adc14-01d5-4bc8-952a-a3d718e99e38(at)s8g2000prg(dot)googlegroups(dot)com>
References: <463adc14-01d5-4bc8-952a-a3d718e99e38(at)s8g2000prg(dot)googlegroups(dot)com>
Comments: In-reply-to Nikhil Bokare <nbokare(at)gmail(dot)com>
message dated "Wed, 12 Mar 2008 23:41:55 -0700"
Date: Thu, 13 Mar 2008 11:19:58 -0400
Message-ID: <17242(dot)1205421598(at)sss(dot)pgh(dot)pa(dot)us>
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200803/569
X-Sequence-Number: 129891

Nikhil Bokare <nbokare(at)gmail(dot)com> writes:
> This is what I'm trying to:
> char lmt_str[100];
> sprintf(lmt_str,"%s",PQgetvalue(res, 0, nFields-1));
> //the last field is of type timestamp
> This is giving segmentation fault.
> I'm confused....

The only way PQgetvalue will return NULL is if the row/column numbers
are out of range. Are you sure you calculated the field number
correctly? Are you sure the query result contains a row at all?
(Did you check for query failure?)

regards, tom lane