pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Trailing spaces - is there an easier way?
3+ messages / 3 participants
[nested] [flat]

* Trailing spaces - is there an easier way?
@ 2010-11-04 19:03 Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
  2010-11-04 19:42 ` Re: Trailing spaces - is there an easier way? Pavel Stehule <pavel.stehule@gmail.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Dean Gibson (DB Administrator) @ 2010-11-04 19:03 UTC (permalink / raw)
  To: pgsql-sql

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?

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Trailing spaces - is there an easier way?
  2010-11-04 19:03 Trailing spaces - is there an easier way? Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
@ 2010-11-04 19:42 ` Pavel Stehule <pavel.stehule@gmail.com>
  2010-11-04 19:47   ` Re: Trailing spaces - is there an easier way? Kenneth Marshall <ktm@rice.edu>
  0 siblings, 1 reply; 3+ messages in thread

From: Pavel Stehule @ 2010-11-04 19:42 UTC (permalink / raw)
  To: Dean Gibson (DB Administrator) <postgresql@ultimeth.com>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Trailing spaces - is there an easier way?
  2010-11-04 19:03 Trailing spaces - is there an easier way? Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
  2010-11-04 19:42 ` Re: Trailing spaces - is there an easier way? Pavel Stehule <pavel.stehule@gmail.com>
@ 2010-11-04 19:47   ` Kenneth Marshall <ktm@rice.edu>
  0 siblings, 0 replies; 3+ messages in thread

From: Kenneth Marshall @ 2010-11-04 19:47 UTC (permalink / raw)
  To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: Dean Gibson (DB Administrator) <postgresql@ultimeth.com>; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2010-11-04 19:47 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-11-04 19:03 Trailing spaces - is there an easier way? Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2010-11-04 19:42 ` Pavel Stehule <pavel.stehule@gmail.com>
2010-11-04 19:47   ` Kenneth Marshall <ktm@rice.edu>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox