DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=rFCo8lPz24A/LH5gQi91OPvtrxSdNY0FZs9iSte9zDD3152cYnnR/tiKouKAUDQFdn
	xTmx+CxG/EPTqtU0p+v9X4T33kUjc2jlMJRwwvOG1Bew4V2KCb0gdtunvyUVMwClxHD6
	lMvaJqFEL4n7CtafIY5UNPJmKOX/PFxLHyN0Y=
MIME-Version: 1.0
In-Reply-To: <BLU0-SMTP789E97372809C06816956AACC70@phx.gbl>
References: <BLU0-SMTP789E97372809C06816956AACC70@phx.gbl>
Date: Fri, 25 Jun 2010 20:28:58 -0300
Message-ID: <AANLkTil2rYpIoIisIGZpEmpIILCV9syw_yGzuN89drOG@mail.gmail.com>
Subject: Re: How do I remove selected words from text field?
From: Osvaldo Kussama <osvaldo.kussama@gmail.com>
To: Frank Bax <fbax@sympatico.ca>
Cc: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

2010/6/25 Frank Bax <fbax@sympatico.ca>:
> I'm not quite sure how to ask for the query I want, so let's start with
> data:
>
> create table t1 (i int, val varchar);
> insert into t1 values(1,'A B C D');
> insert into t1 values(2,'B D E F');
> insert into t1 values(3,'G H I J');
> create table t2 (q varchar, z varchar);
> insert into t2 values('A','vowel');
> insert into t2 values('B','consonant');
> insert into t2 values('E','vowel');
> insert into t2 values('K','consonant');
>
> t1.val will contain "words" separated by blanks. =A0It might be better if=
 each
> "word" were a separate row in another table; but that's not how the legac=
y
> database was built. =A0I understand this can be simulated by:
>
> select i,a[s] from (select i, generate_subscripts(string_to_array(val,'
> '),1) as s, string_to_array(val,' ') as a from t1) foo;
>
> In my "real life" situation, the "words" are not single letters.
>
> I'd like to write a function that removes selected "words" from t1.val ba=
sed
> on select on t2.
> In the above example; let's exclude all vowels, so I end up with:
> 1 'B C D'
> 2 'B D F'
> 3 'G H I J'
>
> For some "words" in val; there may not be a row when joining to t2.q; the=
se
> words must be included in final result. =A0In the above example; there is=
 no
> row in t2 where q=3D"I"; so it is included in result.
>
> How do I write such a function? =A0Can it be done with SQL only?
>


Try:

SELECT i, array_to_string(array_agg(foo), ' ') "Val"
FROM (SELECT * FROM (SELECT i, regexp_split_to_table(val, E'\\s+') AS
foo FROM t1) bar
LEFT OUTER JOIN t2 ON (bar.foo =3D t2.q) WHERE z IS DISTINCT FROM 'vowel') =
foobar
GROUP BY i;
 i |   Val
---+---------
 1 | B C D
 3 | G H I J
 2 | B D F
(3 linhas)

Osvaldo