Re: Question on doc for RETURNING clause
> On Jan 11, 2024, at 12:43 PM, Tom Lane wrote: > > "Russell, John" writes: >> Hi, I was thinking of suggesting some doc clarifications and additional >> examples related to the RETURNING clause. Just a couple of questions first >> to see if my understanding is correct. > >> I was trying to figure out what the precise “thing” is that comes back from >> a RETURNING clause. A table reference? A result set? > > I'd say it's a result set, just like the output of SELECT. > >> That made me think both a RETURNING clause could work in contexts such as >> CTE (yes) and subquery (seems like no). > > We disallow DML in subqueries because there's a lot of squishiness > around when a subquery is evaluated, whether it's evaluated to > completion, or indeed whether it's evaluated more than once. > CTEs have tighter semantics and so it's practical to require > "exactly once" evaluation for CTEs. Partly this is a matter of > historical expectations, but I doubt we'd consider revisiting it. Makes sense. I don’t mind the limitation, I was just thinking of places to document it and examples to add to illustrate usage of RETURNING. If you want to rename columns in the result set, use AS clauses for the column names in RETURNING. If you want to sort and filter what comes back from RETURNING, use a CTE and select from that. My first idea was to try a subquery for all such cases; hadn’t thought of how subqueries might get pruned, moved around, or repeated. >>> PostgreSQL allows INSERT, UPDATE, and DELETE to be used as WITH queries. >>> This is not found in the SQL standard. > >> Is MERGE allowed in that ^^^ context? > > Not yet, as you'd find out if you tried it. I think there's a patch > in the pipeline to allow it. On the way to trying it, I did see in the doc that MERGE doesn’t have a RETURNING clause, which I presume makes the point moot. If there’s a patch that adds such support in process, I won’t suggest adding “MERGE not supported” at this spot in the doc. Thanks, John
Re: Question on doc for RETURNING clause
"Russell, John" writes: > Hi, I was thinking of suggesting some doc clarifications and additional > examples related to the RETURNING clause. Just a couple of questions first to > see if my understanding is correct. > I was trying to figure out what the precise “thing” is that comes back from a > RETURNING clause. A table reference? A result set? I'd say it's a result set, just like the output of SELECT. > That made me think both a RETURNING clause could work in contexts such as CTE > (yes) and subquery (seems like no). We disallow DML in subqueries because there's a lot of squishiness around when a subquery is evaluated, whether it's evaluated to completion, or indeed whether it's evaluated more than once. CTEs have tighter semantics and so it's practical to require "exactly once" evaluation for CTEs. Partly this is a matter of historical expectations, but I doubt we'd consider revisiting it. >> PostgreSQL allows INSERT, UPDATE, and DELETE to be used as WITH queries. >> This is not found in the SQL standard. > Is MERGE allowed in that ^^^ context? Not yet, as you'd find out if you tried it. I think there's a patch in the pipeline to allow it. regards, tom lane
Re: Question on doc for RETURNING clause
On Thu, Jan 11, 2024 at 11:55 AM Russell, John wrote: > ``` > postgres=> insert into generatedfields (x) values (0), (10), (100) order > by 2 desc returning id, x; > ERROR: ORDER BY position 2 is not in select list > LINE 1: ...eratedfields (x) values (0), (10), (100) order by 2 desc ret... > ^ > ``` > > Is the acceptance of ORDER BY documented anywhere? VALUES, like SELECT, is an SQL Command in its own right. https://www.postgresql.org/docs/current/sql-values.html That is what you are ordering, before attempting insertion. Hence why it only sees one column. > I didn’t see that anywhere in the INSERT syntax. Does it have any > practical effect if there’s no RETURNING clause, e.g. do the rows get > physically inserted in the ORDER BY order, which could have implications > for columns like SERIAL? > At present, the order of rows presented to the insert does in no way compel the insert command to act on the provided rows in order; even though in practice it will seem to do so. David J.
Question on doc for RETURNING clause
Hi, I was thinking of suggesting some doc clarifications and additional examples related to the RETURNING clause. Just a couple of questions first to see if my understanding is correct. There’s the basic usage of the RETURNING clause, like is shown on the https://www.postgresql.org/docs/current/dml-returning.html doc page: ``` postgres=> create table ret (id serial, x int, s varchar); CREATE TABLE postgres=> insert into ret (x, s) values (1, 'one'), (2, 'two'), (3, 'three') returning id, s; id | s +--- 1 | one 2 | two 3 | three ``` > The allowed contents of a RETURNING clause are the same as a SELECT command's > output list (see Section 7.3). It can contain column names of the command's > target table, or value expressions using those columns. I was trying to figure out what the precise “thing” is that comes back from a RETURNING clause. A table reference? A result set? The glossary mentions it in the context of result sets: https://www.postgresql.org/docs/16/glossary.html#GLOSSARY-RESULT-SET That made me think both a RETURNING clause could work in contexts such as CTE (yes) and subquery (seems like no). A DML statement with a RETURNING clause can be used in a CTE: ``` postgres=> with t1 as (insert into ret (x, s) values (1, 'one'), (2, 'two'), (3, 'three') returning id, s) select * from t1 order by id desc; id | s +--- 6 | three 5 | two 4 | one ``` But it can’t be used in a subquery: ``` postgres=> select * from (insert into ret (x, s) values (1, 'one'), (2, 'two'), (3, 'three') returning id, s) t1 order by id desc; ERROR: syntax error at or near "into" LINE 1: select * from (insert into ret (x, s) values (1, 'one'), (2,... ``` I couldn’t tell from the definition of subqueries in 7.2.1.3 if a DML with a RETURNING clause should be allowed there or not. INSERT/UPDATE/DELETE are mentioned in the with_query block of the SELECT statement: https://www.postgresql.org/docs/16/sql-select.html Also on that page: > PostgreSQL allows INSERT, UPDATE, and DELETE to be used as WITH queries. This > is not found in the SQL standard. Is MERGE allowed in that ^^^ context? Having a RETURNING clause doesn’t magically make a DML statement recognize extra clauses like ORDER BY: ``` postgres=> insert into ret (x, s) values (1, 'one'), (2, 'two'), (3, 'three') returning id, s order by id desc; ERROR: syntax error at or near "order" LINE 1: ... 'one'), (2, 'two'), (3, 'three') returning id, s order by i... ^ ``` Although intriguingly ORDER BY was recognized if I put it before RETURNING: ``` postgres=> insert into ret (x, s) values (1, 'one'), (2, 'two'), (3, 'three') order by id desc returning id, s; ERROR: column "id" does not exist LINE 1: ...ues (1, 'one'), (2, 'two'), (3, 'three') order by id desc re... ^ HINT: There is a column named "id" in table "ret", but it cannot be referenced from this part of the query. ``` If I didn’t rely on the column name, I could do ORDER BY as part of the INSERT… but it seems like only the “real” inserted column is considered. Here the ordering is by column 2 of the RETURNING clause, which is column 1 from the list of inserted columns: ``` postgres=> insert into generatedfields (x) values (0), (10), (100) order by 1 desc returning id, x; id | x +- 13 | 100 14 | 10 15 | 0 ``` The statement is only aware of 1 column that it can order by, not 2 as in the RETURNING clause: ``` postgres=> insert into generatedfields (x) values (0), (10), (100) order by 2 desc returning id, x; ERROR: ORDER BY position 2 is not in select list LINE 1: ...eratedfields (x) values (0), (10), (100) order by 2 desc ret... ^ ``` Is the acceptance of ORDER BY documented anywhere? I didn’t see that anywhere in the INSERT syntax. Does it have any practical effect if there’s no RETURNING clause, e.g. do the rows get physically inserted in the ORDER BY order, which could have implications for columns like SERIAL? Thanks, John
