How to rewrite 'with update' to postgresql 9.0

Question

Following SQL statement was written for PostgreSQL 9.4.15 and it's working on this version of PostgreSQL.

WITH const_threshold AS (
    SELECT max(id)
    FROM temp_menu_items AS val
)
UPDATE temp_menu_items
SET id        = id + (SELECT parent_id
                      FROM temp_menu_items
                      WHERE parent_id IS NOT NULL
                      ORDER BY parent_id DESC
                      LIMIT 1) + (SELECT *
                                  FROM const_threshold)
  , parent_id = parent_id + (SELECT parent_id
                             FROM temp_menu_items
                             WHERE parent_id IS NOT NULL
                             ORDER BY parent_id DESC
                             LIMIT 1) + (SELECT *
                                         FROM const_threshold);

Reffering to docs there is no with update in PostgreSQL 9.0 https://www.postgresql.org/docs/9.0/static/queries-with.html https://www.postgresql.org/docs/9.4/static/queries-with.html

How to rewrite that statment for PostgreSQL 9.0?

Or more generally:

There is any way to use 'with update' (or equivalent) in 9.0 version of PostgreSQL?

Answer 1

A CTE (WITH foo AS ...) can broadly be thought of as a "shared sub-query", and in many cases can be replaced with a normal sub-query.

This is one of those cases: the only use of const_threshold is in the sub-query (SELECT * FROM const_threshold), so you can replace that directly with the full sub-query (SELECT max(id) FROM temp_menu_items).

This isn't always possible - for instance, CTEs can be "recursive" in a way that normal sub-queries cannot - and may not result in the same query plan, so the general answer is that there is no direct way to do this without upgrading to a newer version of PostgreSQL.

For this example, I believe the result should be equivalent, you just need to write the same sub-query out twice (but the current version isn't exactly concise anyway).