Thread: CONCAT function

CONCAT function

From
"Marek Lewczuk"
Date:
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




Re: CONCAT function

From
Richard Huxton
Date:
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

Re: CONCAT function

From
"Marek Lewczuk"
Date:
>
> 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.




Re: CONCAT function

From
"Shridhar Daithankar"
Date:
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.


Re: CONCAT function

From
"Marek Lewczuk"
Date:
>
> 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.




Re: CONCAT function

From
Csaba Nagy
Date:
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



Re: CONCAT function

From
"scott.marlowe"
Date:
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?


Re: CONCAT function

From
Richard Huxton
Date:
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

Re: CONCAT function

From
"scott.marlowe"
Date:
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.


Re: CONCAT function

From
"scott.marlowe"
Date:
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.


Re: CONCAT function

From
Christopher Browne
Date:
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/>

Re: CONCAT function

From
Christopher Browne
Date:
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

Re: CONCAT function

From
Dennis Gearon
Date:
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?"
>
>:-)
>
>