Sometimes I just want to write my own database

[Mike Austin]

I just want to increment a value in one table and use it in another, in one transaction.

This almost works in Postgres, but I get "WITH clause containing a data-modifying statement must be at the top level". :(

CREATE OR REPLACE FUNCTION set_public_items_team_seq_id()
  RETURNS TRIGGER LANGUAGE PLPGSQL AS $$
BEGIN
  NEW.team_key_seq_id := (
    WITH updated_teams AS (
      UPDATE teams
        SET next_seq_id = next_seq_id + 1
        WHERE id = NEW.team_id
        RETURNING next_seq_id
    )
    SELECT next_seq_id from updated_teams
  );

  RETURN NEW;
END;
$$;

[Mike Austin]

The fix is to use UPDATE + INTO instead of WITH + UPDATE. The former is considered a "top level" statement.

CREATE OR REPLACE FUNCTION set_public_items_team_seq_id()

RETURNS TRIGGER LANGUAGE PLPGSQL  AS $$

DECLARE

  result integer;

BEGIN

  UPDATE teams

    SET next_seq_id = next_seq_id + 1

    WHERE id = NEW.team_id

    RETURNING next_seq_id

    INTO result;

  NEW.team_key_seq_id := result;

  RETURN NEW;

END;

$$;