Is using the RETURNING clause from an UPDATE as the query clause for an INSERT's query clause possible?

I'm trying to use PostgreSQL's RETURNING clause on an UPDATE within in UPDATE statement, and running into trouble.

Postgres allows a query clause in an INSERT, for example:

INSERT INTO films 
SELECT * FROM tmp_films WHERE date_prod < '2004-05-07';

I would like to use the RETURNING clause from an UPDATE as the query clause for an INSERT, for example:

INSERT INTO user_status_history(status)
UPDATE user_status SET status = 'ACTIVE' WHERE status = 'DISABLED' RETURNING status

All of the Postgres references I can find suggest that a RETURNING clause behaved exactly like a SELECT clause, however when I run something like the above, I get the following:

ERROR: syntax error at or near "UPDATE"

LINE 2: UPDATE user_statuses

Despite being able to execute the UPDATE portion of the above query without error.

Is using the RETURNING clause from an UPDATE as the query clause for an INSERT's query clause possible?

The goal is to upd开发者_JAVA百科ate one table and insert into another with a single query, if possible.

With PostgreSQL 9.1 (or higher) you may use the new functionality that allows data-modification commands (INSERT/UPDATE/DELETE) in WITH clauses, such as:

WITH updated_rows AS
(
    UPDATE products
    SET ...
    WHERE ...
    RETURNING *
)
INSERT INTO products_log
SELECT * FROM updated_rows;

With PostgreSQL 9.0 (or lower) you may embed the UPDATE command inside one function, and then use that function from another function which performs the INSERT command, such as:

FUNCTION update_rows()
RETURNS TABLE (id integer, descrip varchar)
AS $$
BEGIN
    RETURN QUERY
        UPDATE products
        SET ...
        WHERE ...
        RETURNING *;
END;
$$ LANGUAGE plpgsql;

FUNCTION insert_rows()
RETURNS void
AS $$
BEGIN
    INSERT INTO products_log
    SELECT * FROM update_rows() AS x;
END;
$$ LANGUAGE plpgsql;

Right now, no.

There was a feature that almost made it into PostgreSQL 9.0 known as Writeable CTE's that does what you're thinking (although the syntax is different).

Currently, you could either do this via a trigger or as two separate statements.

I think this is not possible the way you are trying to do. I'd suggest you to write an AFTER UPDATE trigger, which could perform the insert, then.