Thread: BUG #1947: Enhancement Request - CONCAT() function
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.
"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!
"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
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
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!
> -----Original Message----- > From: David Fetter [mailto:david@fetter.org]=20 > Sent: 08 October 2005 22:16 > To: Tony Marston > Cc: pgsql-bugs@postgresql.org > Subject: Re: [BUGS] BUG #1947: Enhancement Request - CONCAT() function >=20 >=20 > On Sat, Oct 08, 2005 at 12:39:40PM +0100, Tony Marston wrote: > >=20 > > The following bug has been logged online: > >=20 > > 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: > >=20 > > I would like the option to use CONCAT(field1, ' ', field2)=20 > instead of=20 > > the vertical bar syntax (field1 || ' ' || field2) as this is also=20 > > available in other popular databases (MySQL, ORACLE). I am=20 > trying to=20 > > develop applications which are daabase independent, so the use of=20 > > common SQL syntax is very important. >=20 > Fix your application so that it uses the SQL standard ||,=20 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=20 > 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=20
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]=20 > 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()=20 > function=20 >=20 >=20 > "Jim C. Nasby" <jnasby@pervasive.com> writes: > > And you might want to make it a project at http://pgfoundry.org so=20 > > others can make use of it. You might also want to define it as=20 > > accepting an array; I think that would allow you to accept=20 > any number=20 > > of parameters. >=20 > I think Tony is trying to avoid putting in any actual work=20 > ;-). To me, the sanest change would be to modify his app to=20 > use the SQL-standard syntax. Which surely is supported by=20 > those other databases too, no? And if not, why are *we* the=20 > ones getting the bug report? >=20 > 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=20
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]=20 > 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 >=20 >=20 > On Sun, Oct 09, 2005 at 11:05:41AM +0100, Tony Marston wrote: > >=20 > > > "Jim C. Nasby" <jnasby@pervasive.com> writes: > > > > And you might want to make it a project at=20 > http://pgfoundry.org so=20 > > > > others can make use of it. You might also want to define it as=20 > > > > accepting an array; I think that would allow you to accept any=20 > > > > number of parameters. > > >=20 > > > I think Tony is trying to avoid putting in any actual=20 > work ;-). To=20 > > > me, the sanest change would be to modify his app to use the=20 > > > SQL-standard syntax. Which surely is supported by those other=20 > > > databases too, no? And if not, why are *we* the ones=20 > getting the bug=20 > > > report? > > >=20 > > > regards, tom lane > >=20 > > You are getting this as a bug report for the simple reason=20 > that your=20 > > website does not seem to have a method of accepting enhancement=20 > > requests. That is why I specifically put ENHANCEMENT REQUEST in the=20 > > description. >=20 > I've never seen somebody try so hard to get himself labeled=20 > as a help-rejecting complainer before. Are you *certain*=20 > 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=20
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:
=20
select {fn CONCAT(field1, ' ', field2)}
=20
However, for maximum portability, you should probably use:
=20
select {fn CONCAT({fn CONCAT(field1, ' ')}, field2)}
=20
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.)
=20
-Kevin
=20
P.S. A more professional and cooperative tone in any future posts
would be appreciated.
=20
=20
>>> "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 platformsuse broken vertical bars for this operator. When moving SQL script files between systems having different charactersets, such as between ASCII and EBCDIC, vertical bars might not be translated into the vertical bar required bythe target Oracle environment. Oracle provides the CONCAT character function as an alternative to the vertical bar operatorfor 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 >
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 platformsuse broken vertical bars for this operator. When moving SQL script files between systems having different charactersets, such as between ASCII and EBCDIC, vertical bars might not be translated into the vertical bar required bythe target Oracle environment. Oracle provides the CONCAT character function as an alternative to the vertical bar operatorfor 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
> -----Original Message-----
> From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]=20
> 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
>=20
>=20
> If you are striving for portability, perhaps you are using=20
> 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=20
> from the fact that the volunteers in the PostgreSQL community=20
> have put the effort into the JDBC driver to support the=20
> escape sequence for portable string concatenation. This=20
> works in PostgreSQL:
>=20=20
> select {fn CONCAT(field1, ' ', field2)}
>=20=20
> However, for maximum portability, you should probably use:
>=20=20
> select {fn CONCAT({fn CONCAT(field1, ' ')}, field2)}
>=20=20
> Using more than two parameters does not work in the JDBC=20
> driver for at least one commercial DBMS product. (I found=20
> one where there is no exception thrown for the additional=20
> parameters; they are silently
> discarded.)
>=20=20
> -Kevin
>=20=20
> P.S. A more professional and cooperative tone in any future=20
> posts would be appreciated.
I will try.
Tony Marston
http://www.tonymarston.net=20
=20=20
> >>> "Tony Marston" <tony@marston-home.demon.co.uk> 10/08/05=20
> 6:39 AM >>>
>=20
> I would like the option to use CONCAT(field1, ' ', field2)=20
> instead of the vertical bar syntax (field1 || ' ' || field2)=20
> as this is also available in other popular databases (MySQL,=20
> ORACLE). I am trying to develop applications which are=20
> daabase independent, so the use of common SQL syntax is very=20
> important.
>=20
>=20
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 >
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 : : +---------------------------------------+-----------------+
Klint Gore wrote: > 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? textcat is the internal function mapped to ||. It isn't documented anywhere for general use. It does show up in psql \df though. I suppose the bug reporter could use that, but it isn't portable to other database systems. -- 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
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> Where does textcat fit into things?
> textcat is the internal function mapped to ||. It isn't documented
> anywhere for general use. It does show up in psql \df though. I
> suppose the bug reporter could use that, but it isn't portable to other
> database systems.
textcat only handles the two-input case, however.
regards, tom lane
Tom Lane 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 ;-). > And that he wants to write queries that work on Oracle, MySQL, and PostgreSQL at the same time. One point I would make is that although || might appear to break MySQL at the moment, you can set the operator to be concat for the application (maybe in the function that connects to the DB?) Best Wishes Chris Travers Metatron Technology Consulting
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. > > Hi Tony, Let me make a constructive suggestion. I see what you are trying to do and I can understand why this is useful. However, I agree with the main individuals here that it should not be a part of the core project. Fortunately PostgreSQL is extensible and it is quite easy to release custom extensions that can make these things happen without messing with the core project. One of the things I am going to be doing is creating either a Perl (client) or PLPGSQL (server) function to create concat() functions with up to x number of arguments. This will be used as part of our server-side porting framework to allow for easier migration from MySQL in particular. Would you be interested in participating in/testing/contributing to such a project? Best Wishes, Chris Travers Metatron Technology Consulting >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 > > > >