How to define + operator for strings

I want to create portable code which runs in other dbms without
modification.

Unfortunately this other dbms uses + for string concatenation and has no way
to define operators.

How to define + operator as alias of || operator for strings so I can use

SELECT firstname+ ' '+ lastname
...

in Postgres

Andrus.

am 28.04.2006, um 12:59:25 +0300 mailte Andrus folgendes:
> I want to create portable code which runs in other dbms without
> modification.
>
> Unfortunately this other dbms uses + for string concatenation and has no way
> to define operators.
>
> How to define + operator as alias of || operator for strings so I can use
>
> SELECT firstname+ ' '+ lastname

create function _string_plus(text, text) returns text as $$
begin
return $1 || $2;
end;
$$ language plpgsql;

create operator + (
leftarg = text,
rightarg = text,
procedure = _string_plus,
commutator = +
);

test=*# select 'foo' + 'bar';
?column?
----------
foobar
(1 row)

Please read http://www.postgresql.org/docs/8.1/interactive/xoper.html

HTH, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47215, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===

"A. Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com> writes:
> am 28.04.2006, um 12:59:25 +0300 mailte Andrus folgendes:
>> How to define + operator as alias of || operator for strings

> create function _string_plus(text, text) returns text as $$
> begin
> return $1 || $2;
> end;
> $$ language plpgsql;

> create operator + (
> leftarg = text,
> rightarg = text,
> procedure = _string_plus,
> commutator = +
> );

There's no need to bother with creating a function, just make the +
operator point at the same underlying function that || already uses
("textcat" I believe).

However, the reply is really incomplete without pointing out why this is
not such a hot idea: text + text will tend to capture ambiguous cases,
and thus possibly break queries that used to work (date + integer is a
case that comes to mind as being at risk).

Refusing to deal with databases that can't handle the 14-year-old SQL
standard spelling of concatenation would be a better plan IMHO --- if
they can't get this right, it's unlikely that they are much better on
a lot of other points that will be harder to work around.

regards, tom lane

> text + text will tend to capture ambiguous cases,
> and thus possibly break queries that used to work (date + integer is a
> case that comes to mind as being at risk).

How to add + operator for strings so that date+integer expression is not
broken ?

> Refusing to deal with databases that can't handle the 14-year-old SQL
> standard spelling of concatenation would be a better plan IMHO --- if
> they can't get this right, it's unlikely that they are much better on
> a lot of other points that will be harder to work around.

I have huge amount of code written for this. Refusing is difficult.

Andrus.

On Fri, Apr 28, 2006 at 07:35:20PM +0300, Andrus wrote:
> > text + text will tend to capture ambiguous cases,
> > and thus possibly break queries that used to work (date + integer is a
> > case that comes to mind as being at risk).
>
> How to add + operator for strings so that date+integer expression is not
> broken ?

You can't really. There are 27 meanings for a binary '+' operator and
it's not always easy to work out whats is going to if the underlying
types are not numeric in some sense. Given you're using a system that's
not standards compliant, it would surprise me if you used date+integer
at all.

> > Refusing to deal with databases that can't handle the 14-year-old SQL
> > standard spelling of concatenation would be a better plan IMHO --- if
> > they can't get this right, it's unlikely that they are much better on
> > a lot of other points that will be harder to work around.
>
> I have huge amount of code written for this. Refusing is difficult.

Does it not support the SQL standard way of string concatination? You
should be planning a transition because text+text will cause problems
down the line...

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Martijn van Oosterhout wrote:

> Does it not support the SQL standard way of string concatination? You
> should be planning a transition because text+text will cause problems
> down the line...

Sounds to me like a job for sed, awk, perl, tr.... choose your
conversion tool. Make the code right, don't try and make the database
handle it.

--
Until later, Geoffrey

Any society that would give up a little liberty to gain a little
security will deserve neither and lose both. - Benjamin Franklin

> You can't really. There are 27 meanings for a binary '+' operator and
it's not always easy to work out whats is going to if the underlying
types are not numeric in some sense.

So is the best way to create function CONCAT(s1, s2, ... s10) which returns
concatenation in its arguments in both DBMS.
So I can use CONCAT() instead of plus operator.

> Given you're using a system that's not standards compliant, it would
> surprise me if you used date+integer at all.

I'm using Microsoft Visual FoxPro.
It allows date+integer and timestamp+integer arithmetics.
In first case integer means number of days and in second case number of
seconds.

> Does it not support the SQL standard way of string concatination?

SELECT DB||CR FROM OPER

causes error message

Command contains unrecognized phrase/keyword.

> You should be planning a transition because text+text will cause problems
down the line...

There was never problems in date+integer arithmetics in Visual FoxPro.

Visual FoxPro has powerful embedded GUI report designer and grid control.
I haven't found any comparable which works with Postgres.

So I select data from Postgres, store it in FoxPro local data engine and
create reports from it.

Andrus.