Postgres rollback in a function

Question

CREATE OR REPLACE FUNCTION public.insert_art_mov(
    IN _tipo character varying,
    IN _documento integer,
    IN _control integer,
    IN _fecha_doc timestamp without time zone,
    IN _corden integer,
    IN _calmacen integer,
    IN _donado integer,
    IN _monto_desc numeric,
    IN _observacion text,
    IN _cart_generico integer[],
    IN _cart_comercial integer[],
    IN _cunidad integer[],
    IN _cant numeric[],
    IN _iva numeric[],
    IN _costo numeric[],
    IN _crea_user character varying,
    OUT new_cmov integer)
  RETURNS integer AS
$BODY$
DECLARE
MONTO_MOV numeric(11,2);
MONTO_TOT numeric(11,2);
      BEGIN
      --SUMO LOS VALORES DE CADA DETALLE PARA TENER EL VALOR GLOBAL DEL MOVIMIENTO
      MONTO_MOV := (SELECT SUM(s) FROM UNNEST(_costo) s);
      --RESTO EL MONTO DEL MOVIMIENTO Y DEL DESCUENTO (DE APLICAR) PARA TENER EL MONTO TOTAL
      MONTO_TOT := MONTO_MOV - _monto_desc;
      --INSERTO LA CABEZERA DEL MOVIMIENTO
      INSERT INTO art_movimientos(tipo,fecha_mov,documento,control,fecha_doc,corden,calmacen,status,donado,monto_mov,monto_desc,monto_total,observacion,crea_user)
      VALUES(_tipo,NOW(),_documento,_control,_fecha_doc,_corden,_calmacen,'PENDIENTE',_donado,MONTO_MOV,_monto_desc,MONTO_TOT,_observacion,_crea_user) RETURNING cmovimiento INTO new_cmov;
      --INSERTO LOS DETALLES DEL MOVIMIENTO
      INSERT INTO art_movimientos_det (cmovimiento,cart_generico,cart_comercial,cunidad,cant,iva,costo,crea_user)
      SELECT new_cmov,UNNEST(_cart_generico),UNNEST(_cart_comercial),UNNEST(_cunidad),UNNEST(_cant),UNNEST(_iva),UNNEST(_costo),_crea_user;
      END;
  $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

How I can create a rollback or use transaccions: https://www.postgresql.org/docs/current/static/tutorial-transactions.html to prevent the auto increment's of id (primary key), For example if I execute the procedure with errors, the table art_movimientos get autoincremented the id

You can't do a commit or rollback in a function. And even if you could, a rollback won't rollback the sequence number that was generated. Once a sequence number has been used, it can't be rolled back which is [clearly documented in the manual](https://www.postgresql.org/docs/current/static/functions-sequence.html) — , Mar 11 '17 at 17:33
i read about that in another questions, and i must to add a improve column to make a pretty sequence..! How i can Close this question? — JuJoGuAl, Mar 11 '17 at 17:38
@a_horse_with_no_name Numbers that do not have losses of one another for example (1,2,3,6,7) — JuJoGuAl, Mar 11 '17 at 17:46
Why do you think you need that? If that is a primary key, the value of the key is completely irrelevant. The **only** job of a primary key is to be unique. It is **totally** irrelevant if the keys are (1,2,3), (10,42,463) or (-1,-100,-999) — , Mar 11 '17 at 17:48
because in my table pk are the transaction that the users will need if they want to consult the data (if they wanna know the art_movs, they need to know the cmov(pk) and it shows it when the users print the documents — JuJoGuAl, Mar 11 '17 at 18:11
Again: the value of the primary key **does NOT matter**. Gaps in a sequence a **no problem**. — , Mar 11 '17 at 18:53

Postgres rollback in a function

0 Answers0