Postgres Pro
Downloads
Home   >   mailing lists

Thread: Can we overload = operator to word numeric = text

Can we overload = operator to word numeric = text

From
venkatrao.b@tcs.com
Date:

Hello,

We have migrated oracle  database to postgre.
In oracle char to numeric type conversion is explicit (i.e. we can compare char = numeric); but in postgre it is throwing errors. There are so many functions - around 2000, and we can not go and do explict type casting in every function , where there is problem.

Is there any way to come out of this problem. I mean is there any way to make = operator work for numeric = charater varying .
Your suggestions are highly appreciated.

Venkat
=====-----=====-----=====
Notice: The information contained in this e-mail
message and/or attachments to it may contain 
confidential or privileged information. If you are 
not the intended recipient, any dissemination, use, 
review, distribution, printing or copying of the 
information contained in this e-mail message 
and/or attachments to it are strictly prohibited. If 
you have received this communication in error, 
please notify us by reply e-mail or telephone and 
immediately and permanently delete the message 
and any attachments. Thank you

Re: [GENERAL] Can we overload = operator to word numeric = text

From
venkatrao.b@tcs.com
Date:

Dear Pavel,

Thanks a lot...

It worked.

Regards,
Venkat


From:Pavel Stehule <pavel.stehule@gmail.com>
To:venkatrao.b@tcs.com
Cc:pgsql-novice@postgresql.org, pgsql-general@postgresql.org
Date:03/09/2010 04:07 PM
Subject:Re: [GENERAL] Can we overload = operator to word numeric = text




2010/3/9  <venkatrao.b@tcs.com>:
>
> Hello,
>
> We have migrated oracle  database to postgre.
> In oracle char to numeric type conversion is explicit (i.e. we can compare
> char = numeric); but in postgre it is throwing errors. There are so many
> functions - around 2000, and we can not go and do explict type casting in
> every function , where there is problem.
>
> Is there any way to come out of this problem. I mean is there any way to
> make = operator work for numeric = charater varying .
> Your suggestions are highly appreciated.

try

create or replace function num_text_eq(numeric, varchar)
returns bool as $$
 select $1 = $2::numeric$$
language sql;

create operator = ( leftarg=numeric,  rightarg=varchar, procedure=num_text_eq);
postgres=# select 10='10';
?column?
----------
t
(1 row)

regards
Pavel Stehule




>
> Venkat
>
> =====-----=====-----=====
> Notice: The information contained in this e-mail
> message and/or attachments to it may contain
> confidential or privileged information. If you are
> not the intended recipient, any dissemination, use,
> review, distribution, printing or copying of the
> information contained in this e-mail message
> and/or attachments to it are strictly prohibited. If
> you have received this communication in error,
> please notify us by reply e-mail or telephone and
> immediately and permanently delete the message
> and any attachments. Thank you
>
>
>


=====-----=====-----=====
Notice: The information contained in this e-mail
message and/or attachments to it may contain 
confidential or privileged information. If you are 
not the intended recipient, any dissemination, use, 
review, distribution, printing or copying of the 
information contained in this e-mail message 
and/or attachments to it are strictly prohibited. If 
you have received this communication in error, 
please notify us by reply e-mail or telephone and 
immediately and permanently delete the message 
and any attachments. Thank you

Urgent help needed- alias name in update statement

From
venkatrao.b@tcs.com
Date:

Hello,

In postgre, when i am trying to give alias name in update statement like below -
---------------------------------
update mytable x
set x.name = 'asdf'
where x.no = 1  
-------------------------------


is giving error - mytable is not having col x.

We have migrated code from oracle to postgre 8.4. Is there any solution for this.
(functions were compiled without any compilation errors - now when we are trying to run these we are getting problems)

Please help..

Venkat
=====-----=====-----=====
Notice: The information contained in this e-mail
message and/or attachments to it may contain 
confidential or privileged information. If you are 
not the intended recipient, any dissemination, use, 
review, distribution, printing or copying of the 
information contained in this e-mail message 
and/or attachments to it are strictly prohibited. If 
you have received this communication in error, 
please notify us by reply e-mail or telephone and 
immediately and permanently delete the message 
and any attachments. Thank you

Re: Urgent help needed- alias name in update statement

From
Steve T
Date:
Venkat
Shouldn't the expression have an 'as'?

IE
UPDATE mytable AS x
SET x.name = 'asdf'
WHERE x.no = 1

On Tue, 2010-03-09 at 19:21 +0530, venkatrao.b@tcs.com wrote:

Hello,

In postgre, when i am trying to give alias name in update statement like below -
---------------------------------
update mytable x
set x.name = 'asdf'
where x.no = 1  
-------------------------------


is giving error - mytable is not having col x.

We have migrated code from oracle to postgre 8.4. Is there any solution for this.
(functions were compiled without any compilation errors - now when we are trying to run these we are getting problems)

Please help..

Venkat 
=====-----=====-----=====
Notice: The information contained in this e-mail
message and/or attachments to it may contain 
confidential or privileged information. If you are 
not the intended recipient, any dissemination, use, 
review, distribution, printing or copying of the 
information contained in this e-mail message 
and/or attachments to it are strictly prohibited. If 
you have received this communication in error, 
please notify us by reply e-mail or telephone and 
immediately and permanently delete the message 
and any attachments. Thank you


Steve Tucknott
ReTSol Ltd

DDI:         01323 488548

Re: Urgent help needed- alias name in update statement

From
Michael Wood
Date:
On 9 March 2010 16:03, Steve T <steve@retsol.co.uk> wrote:
>
> Venkat
> Shouldn't the expression have an 'as'?
>
> IE
> UPDATE mytable AS x
> SET x.name = 'asdf'
> WHERE x.no = 1

AS doesn't make a difference.  It's optional.

I don't know why the above doesn't work.  I've encountered something
in the past which may be related:

    SELECT LOWER(SPLIT_PART(something, '^', 3)) AS blah
    FROM mytable
    WHERE something IS NOT NULL
    AND LOWER(SPLIT_PART(something, '^', 3)) <> ''
    AND other = 123;

This works, but what I want to do is the following:

    SELECT LOWER(SPLIT_PART(something, '^', 3)) AS blah
    FROM mytable
    WHERE something IS NOT NULL
    AND blah <> ''
    AND other = 123;

This does not work and I don't know why not.

The error message is:

ERROR:  column "blah" does not exist
LINE 4: AND blah <> ''
            ^

which is not exactly the same as the error Venkat is getting, but it's
pretty close.

P.S.  Venkat, it is called "PostgreSQL" or "Postgres".  Not "Postgre".

> On Tue, 2010-03-09 at 19:21 +0530, venkatrao.b@tcs.com wrote:
>
> Hello,
>
> In postgre, when i am trying to give alias name in update statement like below -
> ---------------------------------
> update mytable x
> set x.name = 'asdf'
> where x.no = 1
> -------------------------------
>
> is giving error - mytable is not having col x.
>
> We have migrated code from oracle to postgre 8.4. Is there any solution for this.
> (functions were compiled without any compilation errors - now when we are trying to run these we are getting
problems)

--
Michael Wood <esiotrot@gmail.com>

Re: Urgent help needed- alias name in update statement

From
Steve T
Date:
Mike/venkat
Sorry, you're right.
update blanktable  b
set recno = b.recno
where b.recno = b.recno;

seems to work ok, but if I change that as below, the error occurs:
update blanktable  b
set b.recno = b.recno
where b.recno = b.recno;
ERROR:  column "b" of relation "blanktable" does not exist
LINE 2: set b.recno = b.recno
            ^

So that would imply that simply setting 'name' in Venkat's example (rather than x.name) should work ok.




On Tue, 2010-03-09 at 17:42 +0200, Michael Wood wrote: 
On 9 March 2010 16:03, Steve T <steve@retsol.co.uk> wrote:
>
> Venkat
> Shouldn't the expression have an 'as'?
>
> IE
> UPDATE mytable AS x
> SET x.name = 'asdf'
> WHERE x.no = 1

AS doesn't make a difference.  It's optional.

I don't know why the above doesn't work.  I've encountered something
in the past which may be related:
   SELECT LOWER(SPLIT_PART(something, '^', 3)) AS blah   FROM mytable   WHERE something IS NOT NULL   AND LOWER(SPLIT_PART(something, '^', 3)) <> ''   AND other = 123;

This works, but what I want to do is the following:
   SELECT LOWER(SPLIT_PART(something, '^', 3)) AS blah   FROM mytable   WHERE something IS NOT NULL   AND blah <> ''   AND other = 123;

This does not work and I don't know why not.

The error message is:

ERROR:  column "blah" does not exist
LINE 4: AND blah <> ''           ^

which is not exactly the same as the error Venkat is getting, but it's
pretty close.

P.S.  Venkat, it is called "PostgreSQL" or "Postgres".  Not "Postgre".

> On Tue, 2010-03-09 at 19:21 +0530, venkatrao.b@tcs.com wrote:
>
> Hello,
>
> In postgre, when i am trying to give alias name in update statement like below -
> ---------------------------------
> update mytable x
> set x.name = 'asdf'
> where x.no = 1
> -------------------------------
>
> is giving error - mytable is not having col x.
>
> We have migrated code from oracle to postgre 8.4. Is there any solution for this.
> (functions were compiled without any compilation errors - now when we are trying to run these we are getting problems)

-- 
Michael Wood <esiotrot@gmail.com>


Steve Tucknott
ReTSol Ltd

DDI:         01323 488548

Re: Urgent help needed- alias name in update statement

From
Josh Kupershmidt
Date:
On Tue, Mar 9, 2010 at 10:42 AM, Michael Wood <esiotrot@gmail.com> wrote:
[snip]
> I don't know why the above doesn't work.  I've encountered something
> in the past which may be related:
>
>    SELECT LOWER(SPLIT_PART(something, '^', 3)) AS blah
>    FROM mytable
>    WHERE something IS NOT NULL
>    AND LOWER(SPLIT_PART(something, '^', 3)) <> ''
>    AND other = 123;
>
> This works, but what I want to do is the following:
>
>    SELECT LOWER(SPLIT_PART(something, '^', 3)) AS blah
>    FROM mytable
>    WHERE something IS NOT NULL
>    AND blah <> ''
>    AND other = 123;
>
> This does not work and I don't know why not.

This behavior is mandated by the SQL standard, I believe. I'm too lazy
to dig up the actual reference, but for instance
http://dev.mysql.com/doc/refman/5.0/en//problems-with-alias.html
claims:
    Standard SQL disallows references to column aliases in a WHERE clause.
    This restriction is imposed because when the WHERE clause is evaluated,
    the column value may not yet have been determined...

You could workaround by using a subquery like:

SELECT mysubq.blah FROM (
    SELECT LOWER(SPLIT_PART(something, '^', 3)) AS blah
    FROM mytable
    WHERE something IS NOT NULL
    AND other = 123
)  AS mysubq
WHERE mysubq.blah <> '' ;

Josh

Re: Urgent help needed- alias name in update statement

From
venkatrao.b@tcs.com
Date:

Friends, Thank you all for your prompt responses..

Thanks Michael, for correcting me..
Actually i also used to call it  postgres earlier..but later somewhere in net i found that it is postgre and not postgres..(may be i din' read that time properly..)
anyways, now when i try to search that link , i could not find..
postgres sounds better than postgre...

see this...

http://www.postgresql.org/community/survey.33


Survey Results

The current results of our How do you pronounce 'PostgreSQL'? survey are:

Answer
Responses
Percentage
post-gres-q-l237945.168%
post-gres161130.587%
pahst-grey240.456%
pg-sequel500.949%
post-gree3506.645%
postgres-sequel57410.898%
p-g490.930%
database2304.367%
Total5267







From:Josh Kupershmidt <schmiddy@gmail.com>
To:Michael Wood <esiotrot@gmail.com>
Cc:pgsql-novice@postgresql.org
Date:03/09/2010 11:32 PM
Subject:Re: [NOVICE] Urgent help needed- alias name in update statement
Sent by:pgsql-novice-owner@postgresql.org




On Tue, Mar 9, 2010 at 10:42 AM, Michael Wood <esiotrot@gmail.com> wrote:
[snip]
> I don't know why the above doesn't work.  I've encountered something
> in the past which may be related:
>
>    SELECT LOWER(SPLIT_PART(something, '^', 3)) AS blah
>    FROM mytable
>    WHERE something IS NOT NULL
>    AND LOWER(SPLIT_PART(something, '^', 3)) <> ''
>    AND other = 123;
>
> This works, but what I want to do is the following:
>
>    SELECT LOWER(SPLIT_PART(something, '^', 3)) AS blah
>    FROM mytable
>    WHERE something IS NOT NULL
>    AND blah <> ''
>    AND other = 123;
>
> This does not work and I don't know why not.

This behavior is mandated by the SQL standard, I believe. I'm too lazy
to dig up the actual reference, but for instance
http://dev.mysql.com/doc/refman/5.0/en//problems-with-alias.html
claims:
   Standard SQL disallows references to column aliases in a WHERE clause.
   This restriction is imposed because when the WHERE clause is evaluated,
   the column value may not yet have been determined...

You could workaround by using a subquery like:

SELECT mysubq.blah FROM (
   SELECT LOWER(SPLIT_PART(something, '^', 3)) AS blah
   FROM mytable
   WHERE something IS NOT NULL
   AND other = 123
)  AS mysubq
WHERE mysubq.blah <> '' ;

Josh

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice


=====-----=====-----=====
Notice: The information contained in this e-mail
message and/or attachments to it may contain 
confidential or privileged information. If you are 
not the intended recipient, any dissemination, use, 
review, distribution, printing or copying of the 
information contained in this e-mail message 
and/or attachments to it are strictly prohibited. If 
you have received this communication in error, 
please notify us by reply e-mail or telephone and 
immediately and permanently delete the message 
and any attachments. Thank you

Re: [GENERAL] Can we overload = operator to word numeric = text

From
Pavel Stehule
Date:
2010/3/9  <venkatrao.b@tcs.com>:
>
> Hello,
>
> We have migrated oracle  database to postgre.
> In oracle char to numeric type conversion is explicit (i.e. we can compare
> char = numeric); but in postgre it is throwing errors. There are so many
> functions - around 2000, and we can not go and do explict type casting in
> every function , where there is problem.
>
> Is there any way to come out of this problem. I mean is there any way to
> make = operator work for numeric = charater varying .
> Your suggestions are highly appreciated.

try

create or replace function num_text_eq(numeric, varchar)
returns bool as $$
  select $1 = $2::numeric$$
language sql;

create operator = ( leftarg=numeric,  rightarg=varchar, procedure=num_text_eq);
postgres=# select 10='10';
 ?column?
----------
 t
(1 row)

regards
Pavel Stehule




>
> Venkat
>
> =====-----=====-----=====
> Notice: The information contained in this e-mail
> message and/or attachments to it may contain
> confidential or privileged information. If you are
> not the intended recipient, any dissemination, use,
> review, distribution, printing or copying of the
> information contained in this e-mail message
> and/or attachments to it are strictly prohibited. If
> you have received this communication in error,
> please notify us by reply e-mail or telephone and
> immediately and permanently delete the message
> and any attachments. Thank you
>
>
>

Re: [GENERAL] Urgent help needed- alias name in update statement

From
Adrian Klaver
Date:
On Tuesday 09 March 2010 5:51:31 am venkatrao.b@tcs.com wrote:
> Hello,
>
> In postgre, when i am trying to give alias name in update statement like
> below -
>
> ---------------------------------
> update mytable x
> set x.name = 'asdf'
> where x.no = 1
> -------------------------------
>
> is giving error - mytable is not having col x.
>
> We have migrated code from oracle to postgre 8.4. Is there any solution
> for this.
> (functions were compiled without any compilation errors - now when we are
> trying to run these we are getting problems)
>
> Please help..
>

From here:
http://www.postgresql.org/docs/8.4/interactive/sql-update.html

"column

    The name of a column in table. The column name can be qualified with a
subfield name or array subscript, if needed. Do not include the table's name in
the specification of a target column — for example, UPDATE tab SET tab.col = 1
is invalid. "


--
Adrian Klaver
adrian.klaver@gmail.com

Re: [GENERAL] Urgent help needed- alias name in update statement

From
Raymond O'Donnell
Date:
On 09/03/2010 13:51, venkatrao.b@tcs.com wrote:
> Hello,
>
> In postgre, when i am trying to give alias name in update statement like
> below -
>
> ---------------------------------
> update mytable x
> set x.name = 'asdf'
> where x.no = 1
> -------------------------------

Leave leave off the "x." :

update mytable
set name = 'asdf'
where no = 1;

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie