If more than one table reference is listed in the FROM clause, the tables are cross-joined (that is, the Cartesian product of their rows is formed see below). The alternative involves storage of the DMLR result set anyway, within client code or with the repeated query returning what is to be modified.The FROM clause derives a table from one or more other tables given in a comma-separated table reference list.įROM table_reference ]Ī table reference can be a table name (possibly schema-qualified), or a derived table such as a subquery, a JOIN construct, or complex combinations of these. query, using a secret temp table in the implementation would normally be a reasonably performant approach. If the DMLR "value" could be used after a WITH. Follow-on operations that require multiple DML statements can be effected by a "WITH DMLR store-into-temp-table use temp-table for more DML" sequence. The WITH syntax makes avoiding repetition convenient and expresses reuse.Įven if a WITH-DMLR expression can only be followed by a single DML operation, it is useful for simple things like adding to an audit log. I suspect this is why PG makes a DMLR table-value only usable when it is part of a WITH query. Of course, DML with RETURNING ("DMLR" here) is not something that should be expected to have the same "value" when repeated within an expression. However, that simple view ignores an important side effect. I had thought that DML with a RETURNING clause would and should also function as a table-valued expression. Table and view names, table-valued functions, and SELECT queries can all be used as a "row set" within containing SQL. When using SQL, it is helpful for comprehension that a category of "table-valued expressions" exists. My thinking, and reason for liking the RETURNING clause feature (before understanding that limitation), was that it would simplify pure SQL operations that would otherwise require repetition of SELECT queries or client code written to collect a SELECT result set then run the DML. If something looks like a duck and acts like a duck in some way(s) but not all, an explicit caution, "This is NOT a duck.", is a favor to readers. The syntax railroad diagrams "mention" that fact by omission, but deducing that limitation requires a careful study. I suppose that should be mentioned in the documentation. At the very least, it would be nice if there was a way to get RETURNING results into a temp table without writing library client code to do so.ĭML statements cannot be used in subqueries. I only claim that the feature might be made more useful. It can only be used with a top-level DELETE, INSERT, or UPDATE statement." Hence, I am not claiming that SQLite v3.35.0 does not work as documented. I should add that the draft RETURNING doc says, "The RETURNING clause is not a statement itself, but a clause that can optionally appear near the end of top-level DELETE, INSERT, and UPDATE statements." This might be interpreted as "The RETURNING clause does not convert the preceding DML into a subquery. (I must admit that it was this latter utility that had me excited about the new RETURNING feature.) Especially welcome would be an incantation that makes DML with a RETURNING clause into something able to participate in containing DML statements. Sqlite> insert into t0log (whenCreated,what) with stuffer as (INSERT INTO t0(c) VALUES(random()) RETURNING b,c) select b,c Īt this point, I conclude that either: (1) I am doing this wrongly (2) my notion is ill-founded that adding a RETURNING clause to some DML makes that DML into something with further use like a subquery or table-valued function or (3) SQLite is not yet able to do more with RETURNING clause results than make them available to column value retrieval via the API.Īny thoughts as to which of those 3 cases applies is welcome. With stuffed as (select b,c from t0) select b,c from stuffed Insert into t0log (whenCreated,what) select b,c from t0 Sqlite> with stuffer as (INSERT INTO t0(c) VALUES(random()) RETURNING b,c) select b,c from stuffer I have tried mightily, with SQLite version 3.35.0 13:52:17, to get the RETURNING feature to produce something usable as a subquery that can be used in further (or "outer") operations rather then just producing a result set. See the with_query use and expansion at PostgreSQL SELECT.) This page on PostgreSQL SELECT substantiates my notion that DML with a RETURNING clause may be used as a subquery (in PostgreSQL at least. The fact that DML with a RETURNING clause produces results, just as select statements do, leads me to believe that inserting them into t0log should be possible. INSERT INTO t0(c) VALUES(random()) RETURNING b,c , I am trying to insert into t0log the results returned by this INSERT statement, , (lifted from RETURNING ), and having another table,Ĭreate table t0log (whenCreated text, what integer)
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |