From postgresql@ultimeth.com Thu Nov  4 19:03:55 2010
Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 394211337B88
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu,  4 Nov 2010 16:03:55 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 97137-04 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu,  4 Nov 2010 19:03:48 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail.mailpen.net (mail.mailpen.net [209.59.217.159])
	by mail.postgresql.org (Postfix) with ESMTP id 19E281336971
	for <pgsql-sql@postgresql.org>; Thu,  4 Nov 2010 16:03:47 -0300 (ADT)
Received: from [172.20.1.125] (pool-98-117-107-200.sttlwa.fios.verizon.net
	[98.117.107.200])
	(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
	(No client certificate requested)
	by mail.mailpen.net (Postfix) with ESMTP id EC108114146
	for <pgsql-sql@postgresql.org>; Thu,  4 Nov 2010 11:03:46 -0800 (PST)
Message-ID: <4CD30394.1030106@ultimeth.com>
Date: Thu, 04 Nov 2010 12:03:48 -0700
From: "Dean Gibson (DB Administrator)" <postgresql@ultimeth.com>
Organization: UltiMeth Systems
User-Agent: Thunderbird 2.0.0.24 (Windows/20100228)
MIME-Version: 1.0
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Trailing spaces - is there an easier way?
Content-Type: multipart/alternative;
	boundary="------------050709060400060806000509"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.899 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 201011/8
X-Sequence-Number: 34746

This is a multi-part message in MIME format.
--------------050709060400060806000509
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

I'm sure this has been asked before, but I couldn't find it:

I have a "zzz CHAR (8)" field.  It needs to be CHAR because trailing 
spaces need to be ignored for most operations.  However, I need to 
concatenate it with another (literal) string and need the spaces to be 
significant in that operation.  The *ONLY WAY* I could find to do it  in 
v9.0.1 was (represented in the following function):

CREATE OR REPLACE FUNCTION padded( field CHAR ) RETURNS TEXT
    RETURNS NULL ON NULL INPUT
    IMMUTABLE
    LANGUAGE SQL AS $SQL$
        SELECT RPAD( $1, OCTET_LENGTH( $1 ) )
    $SQL$;

And then of course I write:

SELECT padded( zzz ) || '/' || ...

Is there a better way?


--------------050709060400060806000509
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
</head>
<body bgcolor="#ffffff" text="#000000">
I'm sure this has been asked before, but I couldn't find it:<br>
<br>
I have a "zzz CHAR (8)" field.  It needs to be CHAR because trailing
spaces need to be ignored for most operations.  However, I need to
concatenate it with another (literal) string and need the spaces to be
significant in that operation.  The <b>ONLY WAY</b> I could find to do
it  in v9.0.1 was (represented in the following function):<br>
<br>
<font color="#000099"><tt>CREATE OR REPLACE FUNCTION padded( field CHAR
) RETURNS TEXT<br>
    RETURNS NULL ON NULL INPUT<br>
    IMMUTABLE<br>
    LANGUAGE SQL AS $SQL$<br>
        SELECT RPAD( $1, OCTET_LENGTH( $1 ) )<br>
    $SQL$;</tt></font><br>
<br>
And then of course I write:<br>
<br>
<font color="#000099"><tt>SELECT padded( zzz ) || '/' || ...<br>
</tt></font><br>
Is there a better way?<br>
<br>
</body>
</html>

--------------050709060400060806000509--



From pavel.stehule@gmail.com Thu Nov  4 19:43:10 2010
Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 4548C1337B8B
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu,  4 Nov 2010 16:43:10 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 58669-01 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu,  4 Nov 2010 19:43:03 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-yw0-f46.google.com (mail-yw0-f46.google.com
	[209.85.213.46])
	by mail.postgresql.org (Postfix) with ESMTP id D865A1337B86
	for <pgsql-sql@postgresql.org>; Thu,  4 Nov 2010 16:43:02 -0300 (ADT)
Received: by ywc21 with SMTP id 21so1713140ywc.19
	for <pgsql-sql@postgresql.org>; Thu, 04 Nov 2010 12:43:01 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:received:mime-version:received:in-reply-to
	:references:from:date:message-id:subject:to:cc:content-type
	:content-transfer-encoding;
	bh=9X7DpzVHaZ9JGw29NT0oUPGDOBc8AQMQ5Q9QRAWQg5Q=;
	b=UwWAf1Am0tRVwpLi8imaxxSCrnuLAqZptmljiwLogt4we/6TkUlJB7PPUTr6lrlNQ0
	mhBi4nvyBWr8hxJE6Zxmvo3F/98PRm74/2C6CYekZ51gsWD940GEnEaUvDQxiidIHFgN
	FzO4crybHg5e3GvixgtRMyI9VfO44BB739Ing=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:from:date:message-id:subject:to
	:cc:content-type:content-transfer-encoding;
	b=MiDqRzkVwUTmp19sxBWYtT3PNLzT1Rk+4zHKgsMe41YpuWOotOZG09g79OkCUdYadj
	5eoy2Rt7rr1bCFIEaKBRgwQNCqt0sil8gVSZSSgcFnDqo5bxEMyzLJN088dUa1R5x8Jc
	69IP+Yz8MtpwMcX2fdC5S2N8kxZdK2ZLzmGwg=
Received: by 10.42.104.206 with SMTP id s14mr788202ico.141.1288899781302; Thu,
	04 Nov 2010 12:43:01 -0700 (PDT)
MIME-Version: 1.0
Received: by 10.42.175.195 with HTTP; Thu, 4 Nov 2010 12:42:21 -0700 (PDT)
In-Reply-To: <4CD30394.1030106@ultimeth.com>
References: <4CD30394.1030106@ultimeth.com>
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Thu, 4 Nov 2010 20:42:21 +0100
Message-ID: <AANLkTinNRgvDzk+=098PbtwzqUPxhs9DyrwTgYULjRhc@mail.gmail.com>
Subject: Re: Trailing spaces - is there an easier way?
To: "Dean Gibson (DB Administrator)" <postgresql@ultimeth.com>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.9 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	RCVD_IN_DNSWL_NONE=-0.0001
X-Spam-Level: 
X-Archive-Number: 201011/9
X-Sequence-Number: 34747

2010/11/4 Dean Gibson (DB Administrator) <postgresql@ultimeth.com>:
> I'm sure this has been asked before, but I couldn't find it:
>
> I have a "zzz CHAR (8)" field.=C2=A0 It needs to be CHAR because trailing=
 spaces
> need to be ignored for most operations.=C2=A0 However, I need to concaten=
ate it
> with another (literal) string and need the spaces to be significant in th=
at
> operation.=C2=A0 The ONLY WAY I could find to do it=C2=A0 in v9.0.1 was (=
represented
> in the following function):
>
> CREATE OR REPLACE FUNCTION padded( field CHAR ) RETURNS TEXT
> =C2=A0=C2=A0=C2=A0 RETURNS NULL ON NULL INPUT
> =C2=A0=C2=A0=C2=A0 IMMUTABLE
> =C2=A0=C2=A0=C2=A0 LANGUAGE SQL AS $SQL$
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 SELECT RPAD( $1, OCTET_LENGTH(=
 $1 ) )
> =C2=A0=C2=A0=C2=A0 $SQL$;
>
> And then of course I write:
>
> SELECT padded( zzz ) || '/' || ...
>
> Is there a better way?
>
>

nic=3D# SELECT 'AAAA    '::char(6) || 'bbbb';
 ?column?
----------
 AAAAbbbb
(1 row)

Time: 2.710 ms
nic=3D# SELECT 'AAAA    '::char(6)::cstring || 'bbbb';
  ?column?
------------
 AAAA  bbbb
(1 row)


regards

Pavel Stehule



From ktm@rice.edu Thu Nov  4 19:47:55 2010
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 2DD151337B82
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu,  4 Nov 2010 16:47:55 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 32790-05 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu,  4 Nov 2010 19:47:48 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mh3.mail.rice.edu (mh3.mail.rice.edu [128.42.199.10])
	by mail.postgresql.org (Postfix) with ESMTP id 0A8491337B8D
	for <pgsql-sql@postgresql.org>; Thu,  4 Nov 2010 16:47:47 -0300 (ADT)
Received: from mh3.mail.rice.edu (localhost.localdomain [127.0.0.1])
	by mh3.mail.rice.edu (Postfix) with ESMTP id CC60428F6E3;
	Thu,  4 Nov 2010 14:47:46 -0500 (CDT)
X-Virus-Scanned: by amavis-2.6.4 at mh3.mail.rice.edu, auth channel
Received: from mh3.mail.rice.edu ([127.0.0.1])
	by mh3.mail.rice.edu (mh3.mail.rice.edu [127.0.0.1]) (amavis,
	port 10026)
	with ESMTP id urtqsKkfrtUA; Thu,  4 Nov 2010 14:47:46 -0500 (CDT)
X-SMTP-Auth: no
X-SMTP-Auth: no
X-SMTP-Auth: no
Received: from aart.is.rice.edu (aart.is.rice.edu [168.7.56.48])
	by mh3.mail.rice.edu (Postfix) with ESMTP id A0BD528F6E0;
	Thu,  4 Nov 2010 14:47:46 -0500 (CDT)
Received: by aart.is.rice.edu (Postfix, from userid 18612)
	id 6EAF32D5A6; Thu,  4 Nov 2010 14:47:46 -0500 (CDT)
Date: Thu, 4 Nov 2010 14:47:46 -0500
From: Kenneth Marshall <ktm@rice.edu>
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: "Dean Gibson (DB Administrator)" <postgresql@ultimeth.com>,
	pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: Trailing spaces - is there an easier way?
Message-ID: <20101104194746.GK27429@aart.is.rice.edu>
References: <4CD30394.1030106@ultimeth.com>
	<AANLkTinNRgvDzk+=098PbtwzqUPxhs9DyrwTgYULjRhc@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <AANLkTinNRgvDzk+=098PbtwzqUPxhs9DyrwTgYULjRhc@mail.gmail.com>
User-Agent: Mutt/1.5.16 (2007-06-09)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-4.21 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	RCVD_IN_DNSWL_MED=-2.3, T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201011/10
X-Sequence-Number: 34748

On Thu, Nov 04, 2010 at 08:42:21PM +0100, Pavel Stehule wrote:
> 2010/11/4 Dean Gibson (DB Administrator) <postgresql@ultimeth.com>:
> > I'm sure this has been asked before, but I couldn't find it:
> >
> > I have a "zzz CHAR (8)" field.?? It needs to be CHAR because trailing spaces
> > need to be ignored for most operations.?? However, I need to concatenate it
> > with another (literal) string and need the spaces to be significant in that
> > operation.?? The ONLY WAY I could find to do it?? in v9.0.1 was (represented
> > in the following function):
> >
> > CREATE OR REPLACE FUNCTION padded( field CHAR ) RETURNS TEXT
> > ?????? RETURNS NULL ON NULL INPUT
> > ?????? IMMUTABLE
> > ?????? LANGUAGE SQL AS $SQL$
> > ?????????????? SELECT RPAD( $1, OCTET_LENGTH( $1 ) )
> > ?????? $SQL$;
> >
> > And then of course I write:
> >
> > SELECT padded( zzz ) || '/' || ...
> >
> > Is there a better way?
> >
> >
> 
> nic=# SELECT 'AAAA    '::char(6) || 'bbbb';
>  ?column?
> ----------
>  AAAAbbbb
> (1 row)
> 
> Time: 2.710 ms
> nic=# SELECT 'AAAA    '::char(6)::cstring || 'bbbb';
>   ?column?
> ------------
>  AAAA  bbbb
> (1 row)
> 
> 
> regards
> 
> Pavel Stehule
> 

Here is a link to some useful other pseudo types:

http://www.postgresql.org/docs/9.0/interactive/datatype-pseudo.html

Regards,
Ken