Thread: Something I'd like to try...

Something I'd like to try...

From
Niall Smart
Date:
Hi,

I just noticed that postgres doesn't totally support
column aliases on UPDATE statements, for example        UPDATE EMPLOYEES SET                OFFICE_PHONE =
UU.OFFICE_PHONE,               MOBILE_PHONE = UU.MOBILE_PHONE,                OFFICE_CD = UU.OFFICE_CD,
ABOUT_ME= UU.ABOUT_ME        FROM                UNCONFIRMED_UPDATES UU        WHERE
EMPLOYEES.EMPLOYEE_ID= UU.EMPLOYEE_ID AND                UU.UPDATE_ID = 'HJhjaJ023J19KJAqp'
 

It is not currently possible to alias EMPLOYEES
so that the test can become
        E.EMPLOYEE_ID = UU.EMPLOYEE_ID

Do the guru's think that this would be hard to add?  Also,
is it desirable?
--

Niall Smart

email:  niall.smart@ebeon.com
phone:  (087) 8052390


Re: Something I'd like to try...

From
Bruce Momjian
Date:
Can someone comment on this?

> Hi,
> 
> I just noticed that postgres doesn't totally support
> column aliases on UPDATE statements, for example
>  
>          UPDATE EMPLOYEES SET
>                  OFFICE_PHONE = UU.OFFICE_PHONE,
>                  MOBILE_PHONE = UU.MOBILE_PHONE,
>                  OFFICE_CD = UU.OFFICE_CD,
>                  ABOUT_ME = UU.ABOUT_ME
>          FROM
>                  UNCONFIRMED_UPDATES UU
>          WHERE
>                  EMPLOYEES.EMPLOYEE_ID = UU.EMPLOYEE_ID AND
>                  UU.UPDATE_ID = 'HJhjaJ023J19KJAqp'
> 
> It is not currently possible to alias EMPLOYEES
> so that the test can become
> 
>          E.EMPLOYEE_ID = UU.EMPLOYEE_ID
> 
> Do the guru's think that this would be hard to add?  Also,
> is it desirable?
> --
> 
> Niall Smart
> 
> email:  niall.smart@ebeon.com
> phone:  (087) 8052390
> 


--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: Something I'd like to try...

From
Cristóvão Dalla Costa
Date:
It seems to me that aliases on updates are something totally superflous.
Since updates always work on one table at a time, why bother especifying the
table each field comes from?

> Can someone comment on this?
>
> > Hi,
> >
> > I just noticed that postgres doesn't totally support
> > column aliases on UPDATE statements, for example
> >
> >          UPDATE EMPLOYEES SET
> >                  OFFICE_PHONE = UU.OFFICE_PHONE,
> >                  MOBILE_PHONE = UU.MOBILE_PHONE,
> >                  OFFICE_CD = UU.OFFICE_CD,
> >                  ABOUT_ME = UU.ABOUT_ME
> >          FROM
> >                  UNCONFIRMED_UPDATES UU
> >          WHERE
> >                  EMPLOYEES.EMPLOYEE_ID = UU.EMPLOYEE_ID AND
> >                  UU.UPDATE_ID = 'HJhjaJ023J19KJAqp'
> >
> > It is not currently possible to alias EMPLOYEES
> > so that the test can become
> >
> >          E.EMPLOYEE_ID = UU.EMPLOYEE_ID
> >
> > Do the guru's think that this would be hard to add?  Also,
> > is it desirable?
> > --





Re: Something I'd like to try...

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Can someone comment on this?
>> 
>> I just noticed that postgres doesn't totally support
>> column aliases on UPDATE statements, for example

The SQL92 spec very clearly does not allow an alias on the target table:
        13.10  <update statement: searched>
        <update statement: searched> ::=             UPDATE <table name>               SET <set clause list>
  [ WHERE <search condition> ]
 

While I'm willing to consider variations from the spec that add
significant functionality, this proposed addition adds no functionality
worth noticing.  It'd just be another way to trip yourself up when
moving across DBMSes.
        regards, tom lane


Re: Something I'd like to try...

From
Josh Berkus
Date:
Tom,

> >> I just noticed that postgres doesn't totally support
> >> column aliases on UPDATE statements, for example
> 
> The SQL92 spec very clearly does not allow an alias on the target table:

I have to agree here.  The only improvement from a deviation (alllowing
aliasing) would be cosmetic. 
                -Josh
-- 
______AGLIO DATABASE SOLUTIONS___________________________                                       Josh Berkus  Complete
informationtechnology      josh@agliodbs.com   and data management solutions       (415) 436-9166  for law firms, small
businesses      fax  436-0137   and non-profit organizations.       pager 338-4078                               San
Francisco


Re: Something I'd like to try...

From
Barry Lind
Date:
It is interesting that this should come up now.  Just last week I was
trying to port a SQL statement from Oracle to Postgresql that used table
aliases in an update statement.  While I can see that this functionality
wouldn't be used very often it can be very useful under certain
circumstances.

I have a table that stores a hierarchy.  Sometimes in an update I want
to join back to the same table to get other information related to
children or parent rows.  In Oracle I can do this using the alias, but
in Postgresql I cannot.

Consider the SQL statements below as simplistic examples of what I was
doing in Oracle:

table_foo foo_id int parent_foo_id int column_a  int column_b  int

update table_foo f1
set column_a = (select sum(column_a) from table_foo f2               where f2.parent_foo_id = f1.foo_id);

update table_foo f1
set column_b = 1
where exists (select column_a from table_foo f2                 where f2.parent_foo_id = f1.foo_id);


thanks,
--Barry




Tom Lane wrote:
> 
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Can someone comment on this?
> >>
> >> I just noticed that postgres doesn't totally support
> >> column aliases on UPDATE statements, for example
> 
> The SQL92 spec very clearly does not allow an alias on the target table:
> 
>          13.10  <update statement: searched>
> 
>          <update statement: searched> ::=
>               UPDATE <table name>
>                 SET <set clause list>
>                 [ WHERE <search condition> ]
> 
> While I'm willing to consider variations from the spec that add
> significant functionality, this proposed addition adds no functionality
> worth noticing.  It'd just be another way to trip yourself up when
> moving across DBMSes.
> 
>                         regards, tom lane


Re: Something I'd like to try...

From
Tom Lane
Date:
Barry Lind <barry@xythos.com> writes:
> I have a table that stores a hierarchy.  Sometimes in an update I want
> to join back to the same table to get other information related to
> children or parent rows.  In Oracle I can do this using the alias, but
> in Postgresql I cannot.

> update table_foo f1
> set column_b = 1
> where exists (select column_a from table_foo f2
>                   where f2.parent_foo_id = f1.foo_id);

What's wrong with the spec-compliant

update table_foo
set column_b = 1
where exists (select column_a from table_foo f2             where f2.parent_foo_id = table_foo.foo_id);

?
        regards, tom lane