Content-Type: text/plain;
  charset="iso-8859-1"
From: Richard Huxton <dev@archonet.com>
Organization: Archonet Ltd
To: Stephen.Thompson@bmwfin.com, pgsql-sql@postgresql.org
Subject: Re: VIEW or Stored Proc - Is this even possible?
Date: Thu, 20 Feb 2003 11:08:23 +0000
User-Agent: KMail/1.4.3
References: <3D6DE68CE505D311B2540020482D02530BE2C5C3@EXCHANGESRV>
In-Reply-To: <3D6DE68CE505D311B2540020482D02530BE2C5C3@EXCHANGESRV>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200302201108.23768.dev@archonet.com>

On Thursday 20 Feb 2003 10:09 am, Stephen.Thompson@bmwfin.com wrote:
> Hello,
>
> Thanks for your suggestion I will look into this further.
>
> The reason this issue exists is to do with address details. What we have =
is
> an address table based upon the PAF address structure. IE house number,
> house name, street, locality etc. The software that we are using to perfo=
rm
> a mail merge will not remove blank lines from the address so we can end up
> with an address label looking as:
>
> My House
>
> Main Street
>
> Any Town
> County

Ah - what an irritating bit of software.

I'd be tempted to write a merged_address() function to return the whole thi=
ng=20
as a multi-line field if your mailmerge can handle that. It would do=20
something like:

SELECT COALESCE(street || '\n','') || COALESCE(town || '\n','') ||=20
COALESCE(county || '\n','') FROM my_addr;

and then trim the trailing '\n'.

Failing that, seeing as you're only ever going to have a few fields to deal=
=20
with you could define a mailing_list view which hard-coded address lines, b=
ut=20
you're going to end up with nested CASE elements.

--=20
  Richard Huxton