Thread: CONCAT function
Hello, As I'm in the middle of the migration process form mysql to pg I found that there is no CONCAT function which is available in mysql. Can anybody tell me how to implement this function using plPERL or plPGSQL language ? Best wishes, ML
On Tuesday 16 September 2003 13:45, Marek Lewczuk wrote: > Hello, > As I'm in the middle of the migration process form mysql to pg I found > that there is no CONCAT function which is available in mysql. Can > anybody tell me how to implement this function using plPERL or plPGSQL > language ? You want to use the || operator, e.g. 'hello' || ' ' || 'world' Full details of operators and functions can be found in the "data types" section of the documentation. -- Richard Huxton Archonet Ltd
> > You want to use the || operator, e.g. 'hello' || ' ' || 'world' I know that, but in my application (which is working on MySQL now) I have many querys which use CONCAT function, so I need to implement this function is PG - there is no possibility to replace (in short time) all of my querys.
On 16 Sep 2003 at 14:36, Marek Lewczuk wrote: > > > > You want to use the || operator, e.g. 'hello' || ' ' || 'world' > > I know that, but in my application (which is working on MySQL now) I > have many querys which use CONCAT function, so I need to implement this > function is PG - there is no possibility to replace (in short time) all > of my querys. Write your own function.. something like text concat(text,text) return text1 || text2 Check up the syntax but you got the idea.. Bye Shridhar -- Friends, n.: People who borrow your books and set wet glasses on them. People who know you well, but like you anyway.
> > text concat(text,text) > > return text1 || text2 > If it would be so simple I would not write to this group. You see, CONCAT function doesn't have any arguments limitations, so there could be one, two or more arguments. And this is my problem, how to write function without given arguments number.
Search the recent archives for "DECODE" and "NVL" (those are Oracle functions, but people had he same problems with them as you with CONCAT). The basic idea is that in postgres you can overload functions, so you can create more versions of the function with different parameter counts/types. Just make sure you implement all the variations you use in your code. Cheers, Csaba. On Tue, 2003-09-16 at 15:50, Marek Lewczuk wrote: > > > > text concat(text,text) > > > > return text1 || text2 > > > > If it would be so simple I would not write to this group. You see, > CONCAT function doesn't have any arguments limitations, so there could > be one, two or more arguments. And this is my problem, how to write > function without given arguments number. > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
On Tue, 16 Sep 2003, Marek Lewczuk wrote: > Hello, > As I'm in the middle of the migration process form mysql to pg I found > that there is no CONCAT function which is available in mysql. Can > anybody tell me how to implement this function using plPERL or plPGSQL > language ? What's wrong with using the SQL spec || operator?
On Tuesday 16 September 2003 14:35, scott.marlowe wrote: > On Tue, 16 Sep 2003, Marek Lewczuk wrote: > > Hello, > > As I'm in the middle of the migration process form mysql to pg I found > > that there is no CONCAT function which is available in mysql. Can > > anybody tell me how to implement this function using plPERL or plPGSQL > > language ? > > What's wrong with using the SQL spec || operator? Because in mysql, SELECT 'a' || 'b' is treated as 'a' OR 'b' and you end up with a return value of 0. Ho hum. If I had to choose between coding concat() for PG or the standard operator for mysql, I think I'd do the same. -- Richard Huxton Archonet Ltd
On Tue, 16 Sep 2003, Richard Huxton wrote: > On Tuesday 16 September 2003 14:35, scott.marlowe wrote: > > On Tue, 16 Sep 2003, Marek Lewczuk wrote: > > > Hello, > > > As I'm in the middle of the migration process form mysql to pg I found > > > that there is no CONCAT function which is available in mysql. Can > > > anybody tell me how to implement this function using plPERL or plPGSQL > > > language ? > > > > What's wrong with using the SQL spec || operator? > > Because in mysql, SELECT 'a' || 'b' is treated as 'a' OR 'b' and you end up > with a return value of 0. Ho hum. > > If I had to choose between coding concat() for PG or the standard operator for > mysql, I think I'd do the same. there's a switch in MySQL that makes it behave properly. Given the third choice, I'd throw the switch.
On Tue, 16 Sep 2003, scott.marlowe wrote: > On Tue, 16 Sep 2003, Marek Lewczuk wrote: > > > Hello, > > As I'm in the middle of the migration process form mysql to pg I found > > that there is no CONCAT function which is available in mysql. Can > > anybody tell me how to implement this function using plPERL or plPGSQL > > language ? > > What's wrong with using the SQL spec || operator? Having read the other bits of this thread now, it seems you'd like to create a simple function (pl/sql is good enough to so it, no need for pl/pgsql or what) to do this, but it needs to handle a variable number of args. While pgsql doesn't, to the best of my knowledge, support variable number of arguments, it DOES support overloading. So, you can always create a series of functions that hand 2, 3, 4, 5, 6, etc.., arguments until you get enough to handle all the situations you need. I still stand by my recommendation to start MySQL with the ansi compatibility switch and use the SQL standard specified || operator for portability, as the next database you want to port to after Postgresql may not be flexible enough to do this. Standards are nice, they improve portability, and while the default behaviour of MySQL here is not spec, at least they do provide a switch to make it work the right way. Of course, if you've got other packaged apps using || as the OR operator, you might have issues there. But, if the statements of MySQL are to be taken seriously, i have the feeling that one day || may well be deprecated as they try to get their database server more and more accepted for the server room as a replacement for other databases, so you might as well be ahead of the curve, and not caught by surprise when that happens.
In the last exciting episode, newsy@lewczuk.com ("Marek Lewczuk") wrote:
>> You want to use the || operator, e.g. 'hello' || ' ' || 'world'
>
> I know that, but in my application (which is working on MySQL now) I
> have many querys which use CONCAT function, so I need to implement this
> function is PG - there is no possibility to replace (in short time) all
> of my querys.
Well, then you might implement "CONCAT" in plpgsql.
It will presumably take two values (possibly text, possibly generic),
use ||, inside the function, to catenate them, and then return a text
value.
That should be quite straightforward.
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','ntlug.org').
http://cbbrowne.com/info/lisp.html
Rules of the Evil Overlord #220. "Whatever my one vulnerability is, I
will fake a different one. For example, ordering all mirrors removed
from the palace, screaming and flinching whenever someone accidentally
holds up a mirror, etc. In the climax when the hero whips out a mirror
and thrusts it at my face, my reaction will be ``Hmm...I think I need
a shave.''" <http://www.eviloverlord.com/>
In an attempt to throw the authorities off his trail, scott.marlowe@ihs.com ("scott.marlowe") transmitted:
> Given the third choice, I'd throw the switch.
Why is it that I think of something _completely_ different when you
use the phrase "throw the switch?"
:-)
--
let name="cbbrowne" and tld="acm.org" in name ^ "@" ^ tld;;
http://www3.sympatico.ca/cbbrowne/nonrdbms.html
"The world needs more people like us and fewer like them." -- Unknown
You're not from Texas, are you? :-)
Christopher Browne wrote:
>In an attempt to throw the authorities off his trail, scott.marlowe@ihs.com ("scott.marlowe") transmitted:
>
>
>>Given the third choice, I'd throw the switch.
>>
>>
>
>Why is it that I think of something _completely_ different when you
>use the phrase "throw the switch?"
>
>:-)
>
>