Thread: BUG #5968: DOCUMENTATION: SELECT synopsis omits RETURNING keyword

The following bug has been logged online:

Bug reference:      5968
Logged by:          Gavin Flower
Email address:      gavin.flower@archidevsys.co.nz
PostgreSQL version: 9.1aplha5
Operating system:   x86_64 Linux
Description:        DOCUMENTATION: SELECT synopsis omits RETURNING keyword
Details:

I think the documentation of SELECT should include the keyword 'RETURNING'
in its synopsis.

Also, that at least one example be given on that page.

The text mentions 'RETURNING', so I was surprised not to see it in the
synopsis.

I found examples in the section:
'7.8. WITH Queries (Common Table Expressions)'.

"Gavin Flower" <gavin.flower@archidevsys.co.nz> writes:
> I think the documentation of SELECT should include the keyword 'RETURNING'
> in its synopsis.

Huh?  There's no SELECT RETURNING.

            regards, tom lane

On 08/04/11 21:22, Gavin Flower wrote:
> On 08/04/11 14:57, Tom Lane wrote:
>> "Gavin Flower"<gavin.flower@archidevsys.co.nz>  writes:
>>> I think the documentation of SELECT should include the keyword 'RETURNING'
>>> in its synopsis.
>> Huh?  There's no SELECT RETURNING.
>>
>>             regards, tom lane
> The 'WITH' clause is described in the synopsis for 'SELECT' - and the
> 'WITH' clause uses the keyword 'RETURNING'.
>
> and/with_query/  is:
>
>      /with_query_name/  [ (/column_name/  [, ...] ) ] AS (/select/  |/insert/  |/update/  |/delete/  )
>
> So as the 'WITH' clause is defined in the synopsis, I would expect
> that the synopsis for 'SELECT' should also mention the 'RETURNING'
> keyword - but it does not.
>
> There is an example in:
> '7.8. WITH Queries (Common Table Expressions)'
> WITH t AS (
>      UPDATE products SET price = price * 1.05
>      RETURNING *
> )
> SELECT * FROM t;
>
> Regards,
> Gavin

I found were RETURNING is defined, so I was confused...


    Synopsis

UPDATE [ ONLY ]/table/  [ [ AS ]/alias/  ]
     SET {/column/  = {/expression/  | DEFAULT } |
           (/column/  [, ...] ) = ( {/expression/  | DEFAULT } [, ...] ) } [, ...]
     [ FROM/fromlist/  ]
     [ WHERE/condition/  | WHERE CURRENT OF/cursor_name/  ]
     [ RETURNING * |/output_expression/  [ [ AS ]/output_name/  ] [, ...] ]


However, may be it would still be good to mention it on the SELECT page?


Cheers,
Gavin

(Who while feeling a little foolish, still thinks he has a valid point!)

On 08/04/11 14:57, Tom Lane wrote:
> "Gavin Flower"<gavin.flower@archidevsys.co.nz>  writes:
>> I think the documentation of SELECT should include the keyword 'RETURNING'
>> in its synopsis.
> Huh?  There's no SELECT RETURNING.
>
>             regards, tom lane
The 'WITH' clause is described in the synopsis for 'SELECT' - and the
'WITH' clause uses the keyword 'RETURNING'.

and/with_query/  is:

     /with_query_name/  [ (/column_name/  [, ...] ) ] AS (/select/  |/insert/  |/update/  |/delete/  )


So as the 'WITH' clause is defined in the synopsis, I would expect that
the synopsis for 'SELECT' should also mention the 'RETURNING' keyword -
but it does not.

There is an example in:

'7.8. WITH Queries (Common Table Expressions)'

WITH t AS (
     UPDATE products SET price = price * 1.05
     RETURNING *
)
SELECT * FROM t;


Regards,
Gavin

Gavin Flower <GavinFlower@archidevsys.co.nz> writes:
> I found were RETURNING is defined, so I was confused...
> ...
> However, may be it would still be good to mention it on the SELECT page?

It is mentioned --- the subsection on the WITH clause points out that
you typically want to use RETURNING on an INSERT/UPDATE/DELETE in WITH.

I thought about providing an example, but couldn't come up with one that
was both simple and compelling.  There's no strong reason to do WITH
(something RETURNING) SELECT ... --- you might as well just do the work
in a straight "something RETURNING" statement.  To do something that
actually requires the more complex syntax, you'd need to join the
RETURNING result to another table or another RETURNING result.
Creating an easily understandable example that involves that is beyond
my powers at this time of the morning.

            regards, tom lane

On 09/04/11 02:42, Tom Lane wrote:
> Gavin Flower<GavinFlower@archidevsys.co.nz>  writes:
>> I found were RETURNING is defined, so I was confused...
>> ...
>> However, may be it would still be good to mention it on the SELECT page?
> It is mentioned --- the subsection on the WITH clause points out that
> you typically want to use RETURNING on an INSERT/UPDATE/DELETE in WITH.
>
> I thought about providing an example, but couldn't come up with one that
> was both simple and compelling.  There's no strong reason to do WITH
> (something RETURNING) SELECT ... --- you might as well just do the work
> in a straight "something RETURNING" statement.  To do something that
> actually requires the more complex syntax, you'd need to join the
> RETURNING result to another table or another RETURNING result.
> Creating an easily understandable example that involves that is beyond
> my powers at this time of the morning.
>
>             regards, tom lane
I think it may be useful to explain how I looked for explanations of
RETURNING, as I am sure some other people adopt the same approach.

I located the page for 'SELECT', then I used the search function of the
browser to look for the word 'RETURNING'.  Partly because I have poor
eyesight, I did not pay as much attention to context, as perhaps I
should.  Often I use the table of conbtents, and sometimes I also use
the index.

I hope this is a useful perspective on how documentation is sometimes
accessed.

I often find the examples very helpful, so I am glad you added an
example to the page for 'SELECT'. (I had an appreciative chuckle, when I
saw my name in the newsletter!)


Regards,
Gavin