BUG #1947: Enhancement Request - CONCAT() function

"Tony Marston" <tony@marston-home.demon.co.uk> writes:

I would like the option to use CONCAT(field1, ' ', field2) instead of the
vertical bar syntax (field1 || ' ' || field2) as this is also available in
other popular databases (MySQL, ORACLE).

|| is the SQL standard, CONCAT() is not. But feel free to write your
own functions:

create function concat(text,text) returns text as
'select $1 || $2' language sql strict immutable;
create function concat(text,text,text) returns text as
'select $1 || $2 || $3' language sql strict immutable;
... repeat up to whatever number of parameters seems needed ...

regards, tom lane

On Sat, Oct 08, 2005 at 12:39:40PM +0100, Tony Marston wrote:

The following bug has been logged online:

Bug reference: 1947
Logged by: Tony Marston
Email address: tony@marston-home.demon.co.uk
PostgreSQL version: 8.0.3
Operating system: Windows XP
Description: Enhancement Request - CONCAT() function
Details:

I would like the option to use CONCAT(field1, ' ', field2) instead
of the vertical bar syntax (field1 || ' ' || field2) as this is also
available in other popular databases (MySQL, ORACLE). I am trying to
develop applications which are daabase independent, so the use of
common SQL syntax is very important.

Fix your application so that it uses the SQL standard ||, which Oracle
supports and MySQL can be made to support via a runtime option.

Also, before deciding that you must support multiple back-end
databases, please look over this presentation

http://www.powerpostgresql.com/Downloads/database_depends_public.swf

and see whether this is really a road you want to travel.

Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!

On Sat, Oct 08, 2005 at 04:33:10PM -0400, Tom Lane wrote:

"Tony Marston" <tony@marston-home.demon.co.uk> writes:

I would like the option to use CONCAT(field1, ' ', field2) instead of the
vertical bar syntax (field1 || ' ' || field2) as this is also available in
other popular databases (MySQL, ORACLE).

|| is the SQL standard, CONCAT() is not. But feel free to write your
own functions:

create function concat(text,text) returns text as
'select $1 || $2' language sql strict immutable;
create function concat(text,text,text) returns text as
'select $1 || $2 || $3' language sql strict immutable;
... repeat up to whatever number of parameters seems needed ...

And you might want to make it a project at http://pgfoundry.org so
others can make use of it. You might also want to define it as accepting
an array; I think that would allow you to accept any number of
parameters.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

"Jim C. Nasby" <jnasby@pervasive.com> writes:

And you might want to make it a project at http://pgfoundry.org so
others can make use of it. You might also want to define it as accepting
an array; I think that would allow you to accept any number of
parameters.

I think Tony is trying to avoid putting in any actual work ;-). To me,
the sanest change would be to modify his app to use the SQL-standard
syntax. Which surely is supported by those other databases too, no?
And if not, why are *we* the ones getting the bug report?

regards, tom lane

-----Original Message-----
From: David Fetter [mailto:david@fetter.org]
Sent: 08 October 2005 22:16
To: Tony Marston
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #1947: Enhancement Request - CONCAT() function

On Sat, Oct 08, 2005 at 12:39:40PM +0100, Tony Marston wrote:

The following bug has been logged online:

Bug reference: 1947
Logged by: Tony Marston
Email address: tony@marston-home.demon.co.uk
PostgreSQL version: 8.0.3
Operating system: Windows XP
Description: Enhancement Request - CONCAT() function
Details:

I would like the option to use CONCAT(field1, ' ', field2)

instead of

the vertical bar syntax (field1 || ' ' || field2) as this is also
available in other popular databases (MySQL, ORACLE). I am

trying to

develop applications which are daabase independent, so the use of
common SQL syntax is very important.

Fix your application so that it uses the SQL standard ||,

Not until you fix your product so that it follows the current SQL standard
TO THE LETTER and contains NOTHING which is not in the standard!

It is a well-known fact that every database vendor includes their own
"extensions" to the SQL standard simply because they want to offer more
functionality to their users, and they can't wait for it to be formally
documented in the standard. It is also a well known fact that extensions
made by one database vendor may also be adopted by other vendors in order to
maintain a level of compatibility.

Are you honestly going to tell me that if your user base requested certain
additional functionality that you would refuse to include it simply because
it was "not in the standard"? If every database vendor offered nothing but
the SQL standard there would be nothing to differentiate between them, so
you would not be able to say "ou daabas is better than theirs beause..."

which Oracle supports and MySQL can be made to support via a
runtime option.

They also both support CONCAT() because there are sometimes difficulties in
dealing with vertical bars in the character sets used by certain operating
systems and file systems. If enough database vendors offer it then it becmes
a "de facto" standard.

Tony Marston

http://www.tonymarston.net

On Sat, Oct 08, 2005 at 11:11:58PM +0100, Tony Marston wrote:

-----Original Message-----

The following bug has been logged online:

Bug reference: 1947
Logged by: Tony Marston
Email address: tony@marston-home.demon.co.uk
PostgreSQL version: 8.0.3
Operating system: Windows XP
Description: Enhancement Request - CONCAT() function
Details:

I would like the option to use CONCAT(field1, ' ', field2)
instead of the vertical bar syntax (field1 || ' ' || field2) as
this is also available in other popular databases (MySQL,
ORACLE). I am trying to develop applications which are daabase
independent, so the use of common SQL syntax is very important.

Fix your application so that it uses the SQL standard ||,

Not until you fix your product so that it follows the current SQL
standard TO THE LETTER and contains NOTHING which is not in the
standard!

In case you did not know, PostgreSQL Global Development Group is an
all-volunteer organization. If you have something constructive to
contribute, please feel free to mention it, but please also to recall
that PGDG does not owe you anything, and whinging about how we use a
common, standard thing rather than a rare, non-standard thing is going
to get you somewhere between nothing and negative. People will
remember your contributions, for good or ill. So far, it's for ill.

It is a well-known fact that every database vendor includes their
own "extensions" to the SQL standard simply because they want to
offer more functionality to their users, and they can't wait for it
to be formally documented in the standard. It is also a well known
fact that extensions made by one database vendor may also be adopted
by other vendors in order to maintain a level of compatibility.

This is not one of those cases.

Are you honestly going to tell me that if your user base requested
certain additional functionality that you would refuse to include it
simply because it was "not in the standard"?

Several proposals have been rejected because they violated the
standard. This is why there is no CONNECT BY syntax for trees, but
there will be a WITH RECURSIVE syntax at some point.

Regards,
David.
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!

Tony Marston wrote:

It is a well-known fact that every database vendor includes their own
"extensions" to the SQL standard simply because they want to offer more
functionality to their users, and they can't wait for it to be formally
documented in the standard.

On the other hand, it would be extremely stupid to include every syntax
for every little feature. If a feature is defined by the standard, then
it's quite clear which syntax to support. The fact that other vendor's
product use a different, non-standard syntax, does not mean that we
should too. In places where it's useful to extend the standard to offer
additional features, we do so. If it's only going to clutter our
namespace, we don't.

Also, PostgreSQL is extensible, which is a feature not all DBMSs offer.
So you can create your own CONCAT() function if you wanted.

Last but not least, We have a very loyal userbase; there's no need for
us to force "vendor lock-in" by supporting non-standard syntax for silly
features, like other vendors do.

--
Alvaro Herrera Valdivia, Chile ICBM: S 39� 49' 17.7", W 73� 14' 26.8"
"Find a bug in a program, and fix it, and the program will work today.
Show the program how to find and fix a bug, and the program
will work forever" (Oliver Silfridge)

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 08 October 2005 22:30
To: Jim C. Nasby
Cc: Tony Marston; pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #1947: Enhancement Request - CONCAT()
function

"Jim C. Nasby" <jnasby@pervasive.com> writes:

And you might want to make it a project at http://pgfoundry.org so
others can make use of it. You might also want to define it as
accepting an array; I think that would allow you to accept

any number

of parameters.

I think Tony is trying to avoid putting in any actual work
;-). To me, the sanest change would be to modify his app to
use the SQL-standard syntax. Which surely is supported by
those other databases too, no? And if not, why are *we* the
ones getting the bug report?

regards, tom lane

You are getting this as a bug report for the simple reason that your website
does not seem to have a method of accepting enhancement requests. That is
why I specifically put ENHANCEMENT REQUEST in the description.

Tony Marston

http://www.tonymarston.net

On Sun, Oct 09, 2005 at 11:05:41AM +0100, Tony Marston wrote:

"Jim C. Nasby" <jnasby@pervasive.com> writes:

And you might want to make it a project at http://pgfoundry.org
so others can make use of it. You might also want to define it
as accepting an array; I think that would allow you to accept
any number of parameters.

I think Tony is trying to avoid putting in any actual work ;-).
To me, the sanest change would be to modify his app to use the
SQL-standard syntax. Which surely is supported by those other
databases too, no? And if not, why are *we* the ones getting the
bug report?

regards, tom lane

You are getting this as a bug report for the simple reason that your
website does not seem to have a method of accepting enhancement
requests. That is why I specifically put ENHANCEMENT REQUEST in the
description.

I've never seen somebody try so hard to get himself labeled as a
help-rejecting complainer before. Are you *certain* that this is what
you want to do, Tony?

Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!

-----Original Message-----
From: David Fetter [mailto:david@fetter.org]
Sent: 09 October 2005 18:20
To: Tony Marston
Cc: 'Tom Lane'; 'Jim C. Nasby'; pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #1947: Enhancement Request - CONCAT() function

On Sun, Oct 09, 2005 at 11:05:41AM +0100, Tony Marston wrote:

"Jim C. Nasby" <jnasby@pervasive.com> writes:

And you might want to make it a project at

http://pgfoundry.org so

others can make use of it. You might also want to define it as
accepting an array; I think that would allow you to accept any
number of parameters.

I think Tony is trying to avoid putting in any actual

work ;-). To

me, the sanest change would be to modify his app to use the
SQL-standard syntax. Which surely is supported by those other
databases too, no? And if not, why are *we* the ones

getting the bug

report?

regards, tom lane

You are getting this as a bug report for the simple reason

that your

website does not seem to have a method of accepting enhancement
requests. That is why I specifically put ENHANCEMENT REQUEST in the
description.

I've never seen somebody try so hard to get himself labeled
as a help-rejecting complainer before. Are you *certain*
that this is what you want to do, Tony?

I am just responding in kind. If you can't take it then don't dish it out in
the first place.

Tony Marston

http://www.tonymarston.net

Tony Marston wrote:

which Oracle supports and MySQL can be made to support via a
runtime option.

They also both support CONCAT() because there are sometimes difficulties in
dealing with vertical bars in the character sets used by certain operating
systems and file systems. If enough database vendors offer it then it becmes
a "de facto" standard.

I have never heard of problems with vertical bars in any of those
settings. Can you elaborate? I don't see how operating systems and
file system character sets relate to SQL query characters.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

If you are striving for portability, perhaps you are using Java as your
programming language. If so, you could benefit from the fact that the
volunteers in the PostgreSQL community have put the effort into the
JDBC driver to support the escape sequence for portable string
concatenation. This works in PostgreSQL:

select {fn CONCAT(field1, ' ', field2)}

However, for maximum portability, you should probably use:

select {fn CONCAT({fn CONCAT(field1, ' ')}, field2)}

Using more than two parameters does not work in the JDBC driver for
at least one commercial DBMS product. (I found one where there is
no exception thrown for the additional parameters; they are silently
discarded.)

-Kevin

P.S. A more professional and cooperative tone in any future posts
would be appreciated.

"Tony Marston" <tony@marston-home.demon.co.uk> 10/08/05 6:39 AM >>>

I would like the option to use CONCAT(field1, ' ', field2) instead of the
vertical bar syntax (field1 || ' ' || field2) as this is also available in
other popular databases (MySQL, ORACLE). I am trying to develop applications
which are daabase independent, so the use of common SQL syntax is very
important.

Here is a direct quote from the ORACLE manual:

<quote>
On most platforms, the concatenation operator is two solid vertical bars, as shown in Table 3-3. However, some IBM platforms use broken vertical bars for this operator. When moving SQL script files between systems having different character sets, such as between ASCII and EBCDIC, vertical bars might not be translated into the vertical bar required by the target Oracle environment. Oracle provides the CONCAT character function as an alternative to the vertical bar operator for cases when it is difficult or impossible to control translation performed by operating system or network utilities. Use this function in applications that will be moved between environments with differing character sets.
</quote>

Tony Marston
http://www.tonymarston.net

pgman@candle.pha.pa.us wrote:

PostgreSQL runs on machines that use EBCDIC?

On Mon, Oct 10, 2005 at 04:26:15PM +0100, tony@marston-home.demon.co.uk wrote:

Here is a direct quote from the ORACLE manual:

<quote>
On most platforms, the concatenation operator is two solid vertical bars, as shown in Table 3-3. However, some IBM platforms use broken vertical bars for this operator. When moving SQL script files between systems having different character sets, such as between ASCII and EBCDIC, vertical bars might not be translated into the vertical bar required by the target Oracle environment. Oracle provides the CONCAT character function as an alternative to the vertical bar operator for cases when it is difficult or impossible to control translation performed by operating system or network utilities. Use this function in applications that will be moved between environments with differing character sets.
</quote>

Tony Marston
http://www.tonymarston.net

pgman@candle.pha.pa.us wrote:

Tony Marston wrote:

which Oracle supports and MySQL can be made to support via a
runtime option.

They also both support CONCAT() because there are sometimes difficulties in
dealing with vertical bars in the character sets used by certain operating
systems and file systems. If enough database vendors offer it then it becmes
a "de facto" standard.

I have never heard of problems with vertical bars in any of those
settings. Can you elaborate? I don't see how operating systems and
file system character sets relate to SQL query characters.

-- 
Bruce Momjian                        |  http://candle.pha.pa.us
pgman@candle.pha.pa.us               |  (610) 359-1001
+  If your life is a hard drive,     |  13 Roberts Road
+  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

No, but Oracle does, which is why I am trying to produce SQL statements that
will run on MySQL, PostgreSQL and Oracle without the need for conversion.

Tony Marston

http://www.tonymarston.net

-----Original Message-----
From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]
Sent: 10 October 2005 15:50
To: tony@marston-home.demon.co.uk; pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #1947: Enhancement Request - CONCAT() function

If you are striving for portability, perhaps you are using
Java as your programming language.

Nice try, but I don't use Java. I'm a COBOL man myself :)

Actually, I am currently developing software with PHP that I don't want tied
to a single DBMS, which is my I am looking for portability between MySQL,
PostgreSQL and Oracle.

If so, you could benefit
from the fact that the volunteers in the PostgreSQL community
have put the effort into the JDBC driver to support the
escape sequence for portable string concatenation. This
works in PostgreSQL:

select {fn CONCAT(field1, ' ', field2)}

However, for maximum portability, you should probably use:

select {fn CONCAT({fn CONCAT(field1, ' ')}, field2)}

Using more than two parameters does not work in the JDBC
driver for at least one commercial DBMS product. (I found
one where there is no exception thrown for the additional
parameters; they are silently
discarded.)

-Kevin

P.S. A more professional and cooperative tone in any future
posts would be appreciated.

I will try.

Tony Marston

http://www.tonymarston.net

So the argument boils down to we should add concat as a function because
Oracle runs on on EBCDIC and recommends not using concat and because
MySQL has no clue when it comes to SQL standards. Pretty distasteful
arguments, IMHO. Unlike some, I'm in favor of making it easy for people
to migrate from MySQL, but even I don't like the idea of cluttering up
the base functions because of their lack of clue.

As I said before, I think this is best left to http://pgfoundry.org,
unless it's impossible to create a function that accepts an arbitrary
number of arguments. If that's the case, I would argue for changing the
backend so user functions could have an arbitrary number of arguments,
and then creating a concat() project on pgFoundry.

On Mon, Oct 10, 2005 at 10:04:25PM +0100, Tony Marston wrote:

No, but Oracle does, which is why I am trying to produce SQL statements that
will run on MySQL, PostgreSQL and Oracle without the need for conversion.

Tony Marston

http://www.tonymarston.net

-----Original Message-----
From: Jim C. Nasby [mailto:jnasby@pervasive.com]
Sent: 10 October 2005 18:19
To: tony@marston-home.demon.co.uk
Cc: Bruce Momjian; 'David Fetter'; pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #1947: Enhancement Request - CONCAT() function

PostgreSQL runs on machines that use EBCDIC?

On Mon, Oct 10, 2005 at 04:26:15PM +0100,
tony@marston-home.demon.co.uk wrote:

Here is a direct quote from the ORACLE manual:

<quote>
On most platforms, the concatenation operator is two solid vertical
bars, as shown in Table 3-3. However, some IBM platforms use broken
vertical bars for this operator. When moving SQL script

files between

systems having different character sets, such as between ASCII and
EBCDIC, vertical bars might not be translated into the vertical bar
required by the target Oracle environment. Oracle provides

the CONCAT

character function as an alternative to the vertical bar

operator for

cases when it is difficult or impossible to control translation
performed by operating system or network utilities. Use

this function

in applications that will be moved between environments

with differing

character sets. </quote>

Tony Marston
http://www.tonymarston.net

pgman@candle.pha.pa.us wrote:

Tony Marston wrote:

which Oracle supports and MySQL can be made to support via a
runtime option.

They also both support CONCAT() because there are sometimes
difficulties in dealing with vertical bars in the

character sets

used by certain operating systems and file systems. If enough
database vendors offer it then it becmes a "de facto" standard.

I have never heard of problems with vertical bars in any of those
settings. Can you elaborate? I don't see how operating

systems and

file system character sets relate to SQL query characters.

-- 
Bruce Momjian                        |  http://candle.pha.pa.us
pgman@candle.pha.pa.us               |  (610) 359-1001
+  If your life is a hard drive,     |  13 Roberts Road
+  Christ can be your backup.        |  Newtown Square, 

Pennsylvania 19073

---------------------------(end of
broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

tony@marston-home.demon.co.uk wrote:

Here is a direct quote from the ORACLE manual:

<quote> On most platforms, the concatenation operator is two solid
vertical bars, as shown in Table 3-3. However, some IBM platforms use
broken vertical bars for this operator. When moving SQL script files
between systems having different character sets, such as between ASCII
and EBCDIC, vertical bars might not be translated into the vertical
bar required by the target Oracle environment. Oracle provides the
CONCAT character function as an alternative to the vertical bar operator
for cases when it is difficult or impossible to control translation
performed by operating system or network utilities. Use this function
in applications that will be moved between environments with differing
character sets. </quote>

Well, that is very interesting. I have noticed that the ASCII pipe
sometimes has a break in it, but I never realized the solid and broken
bar were ever treated as separate characters.

My guess is that this Oracle note was from the early 1980's, and it was
never removed or updated, but I now understand why they added CONCAT
because long ago they had this translation issue with EBCDIC.

Oracle has a tendency to keep things around forever, which is nice, but
over the years it produces a system with lots of strange features that
make the system hard to use. PostgreSQL has a tendency to remove old,
unused stuff over time to keep the system easy to understand. This is
part of the reason we have not added CONCAT, because it doesn't really
add new functionality to the vast majority of our users.

Sure, if someone wants to write scripts that work with PostgreSQL and
Oracle EBCDIC machines, it would be nice to have, but for 99% of our
users, it is just added cruft, and we have a tendency to try to reduce
cruft rather than make 1% of people happy, especially when the
extensibility system of PostgreSQL allows users to create their own
CONCAT functions if they desire.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

On Mon, 10 Oct 2005 23:45:03 -0400 (EDT), Bruce Momjian <pgman@candle.pha.pa.us> wrote:

Oracle has a tendency to keep things around forever, which is nice, but
over the years it produces a system with lots of strange features that
make the system hard to use. PostgreSQL has a tendency to remove old,
unused stuff over time to keep the system easy to understand. This is
part of the reason we have not added CONCAT, because it doesn't really
add new functionality to the vast majority of our users.

Sure, if someone wants to write scripts that work with PostgreSQL and
Oracle EBCDIC machines, it would be nice to have, but for 99% of our
users, it is just added cruft, and we have a tendency to try to reduce
cruft rather than make 1% of people happy, especially when the
extensibility system of PostgreSQL allows users to create their own
CONCAT functions if they desire.

Where does textcat fit into things?

klint.

+---------------------------------------+-----------------+
: Klint Gore                            : "Non rhyming    :
: EMail   : kg@kgb.une.edu.au           :  slang - the    :
: Snail   : A.B.R.I.                    :  possibilities  :
: Mail      University of New England   :  are useless"   :
:           Armidale NSW 2351 Australia :     L.J.J.      :
: Fax     : +61 2 6772 5376             :                 :
+---------------------------------------+-----------------+