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

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

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

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

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

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

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>

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

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

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-l
2379
45.168%
post-gres
1611
30.587%
pahst-grey
24
0.456%
pg-sequel
50
0.949%
post-gree
350
6.645%
postgres-sequel
574
10.898%
p-g
49
0.930%
database
230
4.367%
Total
5267

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

Venkat wrote:

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)

The name is PostgreSQL or Postgres.

I looked at the syntax of the UPDATE statement as specified by
the SQL standard, and it says in Part 2, chapters 14.11 and 14.12,
that PostgreSQL is behaving in the standard-conforming way.

If you use nonstandard SQL extensions of a database vendor,
portability will suffer, which is neither Oracle's nor
PostgreSQL's fault.

I guess you'll have to rewrite those UPDATE statements.

Yours,
Laurenz Albe