Data-modifying query in CTE

(1.2) By beetlejuice (coleifer) on 2022-07-22 20:09:22 edited from 1.1 [link] [source]

Hi, now that Sqlite supports RETURNING, I'm wondering if there are any plans or interest in supporting using data-modifying queries in CTE's.

Here are some contrived examples that work with Postgres to simulate moving some rows from one table to another, but hopefully they illustrate the feature I'm asking about:

create table product (
    id serial not null primary key,
    name text not null,
    price integer not null);

create table archive (
    id serial not null primary key,
    name text not null,
    price integer not null,
    timestamp integer not null);

insert into product (name, price)
    values ('p1', 1), ('p2', 2), ('p3', 3), ('p4', 4);

-- Delete then insert
with moved(id, name, price) as (
    delete from product where price < 3
    returning id, name, price)
insert into archive (id, name, price, timestamp)                                       
select moved.id, moved.name, moved.price, extract(epoch from now())                   
    from moved;

-- Insert then delete
with moved(id) as (
    insert into archive (id, name, price, timestamp)                                  
    select id, name, price, extract(epoch from now())                            
    from product where price < 3
    returning id)
delete from product where id in (select moved.id from moved);

Both of the above example queries delete two rows from the product table and move them to the archive table. To clarify: I'm not asking for other approaches -- these queries are intended to serve only as examples of a general feature: using DML queries + returning within a CTE.

(2) By Keith Medcalf (kmedcalf) on 2022-07-22 19:54:59 in reply to 1.1 [source]

Why not simply use the following trigger to obtain the same result (fixed to use SQLite3 recognized syntax and declarations)?

create table product (
    id integer primary key autoincrement,
    name text not null,
    price integer not null);

create table archive (
    id integer primary key,
    name text not null,
    price integer not null,
    timestamp integer not null);

insert into product (name, price)
    values ('p1', 1), ('p2', 2), ('p3', 3), ('p4', 4);

create trigger movetoarchive after delete on product
begin
  insert into archive values (old.id, old.name, old.price, unixepoch());
end;

delete from product where price < 3;

Mucho simpler. Significantly less typing. Does not require usage of (IMHO) dodgy features.

(3) By beetlejuice (coleifer) on 2022-07-23 11:39:23 in reply to 2 [link] [source]

Keith, I am not interested in alternatives. This is a particular implementation detail about how DML queries with a RETURNING clause may be used.

Hopefully one of the sqlite crew can respond.

(4) By beetlejuice (coleifer) on 2022-07-27 14:23:13 in reply to 1.2 [link] [source]

Bumping once in the hopes Dan or DRH might chime in?