Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id 8A0BB1F633FD
	for <pgsql-sql@postgresql.org>; Thu, 12 Apr 2012 09:31:57 -0300 (ADT)
Received: from plane.gmane.org ([80.91.229.3])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <sql-pgsql-sql@m.gmane.org>) id 1SIJBq-0004Ct-Ng
	for pgsql-sql@postgresql.org; Thu, 12 Apr 2012 12:31:56 +0000
Received: from list by plane.gmane.org with local (Exim 4.69)
	(envelope-from <sql-pgsql-sql@m.gmane.org>) id 1SIJBa-0003DS-L0
	for pgsql-sql@postgresql.org; Thu, 12 Apr 2012 14:31:38 +0200
Received: from ip-118-90-103-174.xdsl.xnet.co.nz ([118.90.103.174])
	by main.gmane.org with esmtp (Gmexim 0.1 (Debian))
	id 1AlnuQ-0007hv-00
	for <pgsql-sql@postgresql.org>; Thu, 12 Apr 2012 14:31:38 +0200
Received: from jasen by ip-118-90-103-174.xdsl.xnet.co.nz with local (Gmexim
	0.1 (Debian)) id 1AlnuQ-0007hv-00
	for <pgsql-sql@postgresql.org>; Thu, 12 Apr 2012 14:31:38 +0200
X-Injected-Via-Gmane: http://gmane.org/
To: pgsql-sql@postgresql.org
From: Jasen Betts <jasen@xnet.co.nz>
Subject: Re: How to html-decode a html-encoded field
Date: 12 Apr 2012 12:26:18 GMT
Organization: Dis (not Dat) Organisation
Lines: 24
Message-ID: <jm6hla$bld$1@reversiblemaps.ath.cx>
References: 
 <CAAY=A7-PGBxQXJxVo2djnOKgwGd+0=bKjmG8_FHfOEFn1dSjbQ@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
X-Complaints-To: usenet@dough.gmane.org
X-Gmane-NNTP-Posting-Host: ip-118-90-103-174.xdsl.xnet.co.nz
X-Face: ?)Aw4rXwN5u0~$nqKj`xPz>xHCwgi^q+^?Ri*+R(&uv2=E1Q0Zk(>h!~o2ID@6{uf8s; a
	+M[5[U[QT7xFN%^gR"=tuJw%TXXR'Fp~W;
	(T"1(739R%m0Yyyv*gkGoPA.$b,D.w:z+<'"=-lVT?6
	{T?=R^:W5g|E2#EhjKCa+nt":4b}dU7GYB*HBxn&Td$@f%.kl^:7X8rQWd[NTc"P"u6nkisze/Q;
	8 "9Z{peQF,w)7UjV$c|RO/mQW/NMgWfr5*$-Z%u46"/00mx-,\R'fLPe.)^
User-Agent: slrn/pre1.0.0-18 (Linux)
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201204/28
X-Sequence-Number: 36505

On 2012-04-10, JORGE MALDONADO <jorgemal1960@gmail.com> wrote:
> --000e0ce0d2ee43edb104bd553408
> Content-Type: text/plain; charset=ISO-8859-1
>
> I have a table with a varchar field, such a field is HTML ENCODED. So, for
> example, the string "PLAIN WHITE T'S" is saved as "PLAIN WHITE
> T&#39;&#39;S" (double quotes are not part of the string, I use them for
> clarity only). I need to perform a SELECT statement on this table and get
> the values HTML DECODED and I wonder if there is a function that I can
> include in such a statement for this purpose, for example "SELECT
> htmldecode(fld1) FROM table1". I will appreciate anu comments about my
> issue.

if you don't mind gettin an exception or the wrong answer on invalid 
input you can abuse xpath thusly:

with sample as ( values ('m&#97;gic'),('s&amp;witch'),('to&#x61;ster')) 
select 
  column1 as input ,
  (xpath('/z/text()', ('<z>'|| column1 ||'</z>')::xml))[1] as output 
from sample
      
-- 
⚂⚃ 100% natural