Обсуждение: Can we overload = operator to word numeric = text

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

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

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

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

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