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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general