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--