CONCAT function

On Tuesday 16 September 2003 13:45, Marek Lewczuk wrote:[color=blue]
> 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 ?[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

>[color=blue]
> You want to use the || operator, e.g. 'hello' || ' ' || 'world'[/color]

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.




---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

On 16 Sep 2003 at 14:36, Marek Lewczuk wrote:
[color=blue][color=green]
> >
> > You want to use the || operator, e.g. 'hello' || ' ' || 'world'[/color]
>
> 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.[/color]

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.


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to Join Bytes!)

>[color=blue]
> text concat(text,text)
>
> return text1 || text2
>[/color]

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 Join Bytes!

In the last exciting episode, Join Bytes! ("Marek Lewczuk") wrote:[color=blue][color=green]
>> You want to use the || operator, e.g. 'hello' || ' ' || 'world'[/color]
>
> 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.[/color]

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/>

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:[color=blue][color=green]
> >
> > text concat(text,text)
> >
> > return text1 || text2
> >[/color]
>
> 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 Join Bytes![/color]



---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to Join Bytes!

On Tue, 16 Sep 2003, Marek Lewczuk wrote:
[color=blue]
> 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 ?[/color]

What's wrong with using the SQL spec || operator?


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

On Tuesday 16 September 2003 14:35, scott.marlowe wrote:[color=blue]
> On Tue, 16 Sep 2003, Marek Lewczuk wrote:[color=green]
> > 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 ?[/color]
>
> What's wrong with using the SQL spec || operator?[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to Join Bytes!)

On Tue, 16 Sep 2003, Richard Huxton wrote:
[color=blue]
> On Tuesday 16 September 2003 14:35, scott.marlowe wrote:[color=green]
> > On Tue, 16 Sep 2003, Marek Lewczuk wrote:[color=darkred]
> > > 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 ?[/color]
> >
> > What's wrong with using the SQL spec || operator?[/color]
>
> 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.[/color]

there's a switch in MySQL that makes it behave properly.

Given the third choice, I'd throw the switch.


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

On Tue, 16 Sep 2003, scott.marlowe wrote:
[color=blue]
> On Tue, 16 Sep 2003, Marek Lewczuk wrote:
>[color=green]
> > 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 ?[/color]
>
> What's wrong with using the SQL spec || operator?[/color]

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.


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

In an attempt to throw the authorities off his trail, Join Bytes! ("scott.marlowe") transmitted:[color=blue]
> Given the third choice, I'd throw the switch.[/color]

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:
[color=blue]
>In an attempt to throw the authorities off his trail, Join Bytes! ("scott.marlowe") transmitted:
>
>[color=green]
>>Given the third choice, I'd throw the switch.
>>
>>[/color]
>
>Why is it that I think of something _completely_ different when you
>use the phrase "throw the switch?"
>
>:-)
>
>[/color]


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to Join Bytes!