Re: Is my MySQL Gaining ? - Mailing list pgsql-general

On Mon, Dec 29, 2003 at 23:41:22 -0000,
  John Sidney-Woollett <johnsw@wardbrook.com> wrote:
> Actually, sometimes these questions will be postgres specific, and this is
> where the docs are too light.
>
> An example is an update statement using values from a correlated subquery.
> Here's example code in pgsql:
>
>   update PHOTO.WPImage
>   set WPImageStateID = 3,
>   Width = WPImageHeader.Width,
>   Height = WPImageHeader.Height,
>   ContentType = WPImageHeader.ContentType,
>   ContentLength = WPImageHeader.ContentLength
>   where WPImage.WDResourceID = WPImageHeader.WDResourceID
>   and WPImage.WDResourceID = pResourceID
>   and WPImage.WPSizeTypeID = 0;
>
> In Oracle this might be written:
>
>   update PHOTO.WPImage i
>   set WPImageStateID = 3,
>   (Width, Height, ContentType, ContentLength) = (
>     select Width, Height, ContentType, ContentLength
>     from PHOTO.WPImageHeader ih
>     where ih.WDResourceID = i.WDResourceID)
>   where WPImage.WDResourceID = pResourceID
>   and WPImage.WPSizeTypeID = 0;
>
> You'll notice that the syntax is entirely different, and very relevant for
> inclusion in the docs for each database's update statement.

The Postgres example uses a join instead of subselects. You could have
used subselects in postgres, but because there is currently not a way
to set more than one column at a time from one subselect, you would
have to repeat the subselect 4 times.

I am not convinced that this needs to be documented in the section on
the update statement. This is something that would belong in an oracle
to postgres conversion guide.

> I've mentioned it before but here it is again, contrast this explanation
> of the UPDATE command in postgres with Oracle's explanation. Which one
> would explain how to make use of a correlated subquery without resorting
> to more googling or the list?
>
> postgres: http://www.postgres.org/docs/current/interactive/sql-update.html
>
> Oracle: http://miami.int.gu.edu.au/dbs/7016/a85397/state27a.htm#2067717
>
> My point is not so much that the docs are difficult for newbies (and they
> probably are), but that they just lack sufficient meat which really ought
> to be included.

I still don't see that there needs to be a lot more added to the postgres
update command documentation. The main thing missing is links to the
syntax definitions for things like from list, condition and expression.
Currently you just have to know that the syntax for from items and conditions
is described with the select documentation and that expression syntax is
covered in the value expressions chapters under sql syntax.