Thread: String concatenation operator which keeps trailing spaces in CHAR(n) columns

How to create string concatenation operator which preserves trailing spaces
on CHAR(n) type columns ?

I tried code below, but it returns AB (without spaces).
How to force it to return A B (keep space after A) ?

Andrus.

CREATE OR REPLACE FUNCTION public.stringconcat(left text, right text)
RETURNS text
LANGUAGE sql IMMUTABLE
AS $BODY$
SELECT concat($1, $2) ;
$BODY$;

CREATE OPERATOR public.+ (
    leftarg = text,
    rightarg = text,
    procedure = public.stringconcat
);

create temp table test (col1  char(2)) on commit drop;
insert into test values ('A');
select col1 +  'B'
   from test;


I posted similar question also in


http://stackoverflow.com/questions/24975118/how-to-create-string-concatenation-operator-which-preserves-trailing-spaces-in-c

Andrus wrote:
> How to create string concatenation operator which preserves trailing spaces
> on CHAR(n) type columns ?
> 
> I tried code below, but it returns AB (without spaces).
> How to force it to return A B (keep space after A) ?
> 
> Andrus.
> 
> CREATE OR REPLACE FUNCTION public.stringconcat(left text, right text)
> RETURNS text
> LANGUAGE sql IMMUTABLE
> AS $BODY$
> SELECT concat($1, $2) ;
> $BODY$;
> 
> CREATE OPERATOR public.+ (
>     leftarg = text,
>     rightarg = text,
>     procedure = public.stringconcat
> );
> 
> create temp table test (col1  char(2)) on commit drop;
> insert into test values ('A');
> select col1 +  'B'
>    from test;
> 
> 
> I posted similar question also in
> 
> http://stackoverflow.com/questions/24975118/how-to-create-string-concatenation-operator-which-
> preserves-trailing-spaces-in-c

Use "bpchar" instead of "text" in the definition of function and operator.

Otherwise col1 gets cast to "text" and loses its trailing spaces.

Yours,
Laurenz Albe

Hi!

>Use "bpchar" instead of "text" in the definition of function and operator.
>Otherwise col1 gets cast to "text" and loses its trailing spaces.

Thank you very much.
It worked.
Which notation to use for this ?

Is it reasonable use "+" as such operator for strings or should some other
notation used ?

I tried:

CREATE OR REPLACE FUNCTION public.concatkeepspaces(left bpchar, right
bpchar)
RETURNS bpchar
LANGUAGE sql IMMUTABLE
AS $BODY$
SELECT concat($1,$2);
$BODY$;

CREATE OPERATOR public.+ (
    leftarg = bpchar,
    rightarg = bpchar,
    procedure = public.concatkeepspaces
);

Andrus.

Andrus wrote:
>> Use "bpchar" instead of "text" in the definition of function and operator.
>> Otherwise col1 gets cast to "text" and loses its trailing spaces.
> 
> Thank you very much.
> It worked.
> Which notation to use for this ?
> 
> Is it reasonable use "+" as such operator for strings or should some other
> notation used ?

The traditional concatenation operator name is ||, but I believe that it is
confusing to overwrite builtin operators.

You could use a name that does not show up in
SELECT DISTINCT oprname FROM pg_operator;

Yours,
Laurenz Albe

On Wed, Jul 30, 2014 at 5:43 AM, Andrus <kobruleht2@hot.ee> wrote:
> How to create string concatenation operator which preserves trailing spaces
> on CHAR(n) type columns ?

hm, why do that at all?   how about avoid the char() type and create
views over tables using rpad when you want space padding:

create view v_foo as
  select *, rpad(f, 50, ' ') as f_padded;

merlinm

Hi,

>hm, why do that at all?   how about avoid the char() type and create
>views over tables using rpad when you want space padding:
>create view v_foo as
>  select *, rpad(f, 50, ' ') as f_padded;

I'm creating a converter which converts Visual FoxPro expressions to
Postgres at runtime.
FoxPro expression a+b  produces trailing spaces after a  .
To get same result I need to + or other operator with this behaviour.

Andrus.