exec 3<&0 echo "BEGIN;" > $TEMPFILE $DB -a -f- <<'EOF' 2>/dev/null | BEGIN; CREATE TEMPORARY TABLE stock_checker ( id serial primary key, code char(4), date timestamp, qty numeric, stock numeric default 0, product bigint ); INSERT INTO stock_checker (code, date, qty, product) SELECT code, date, CASE sign WHEN false THEN -qty ELSE qty END AS qty, product FROM ( SELECT a.code, b.date, a.sign, d.qty, e.product, b.is_void FROM _document_type_data a, own_document b, own_item c, own_product_item d, _price_data e WHERE a.id = b.own_document_type AND b.id=c.own_document AND c.id=d.own_item AND d.price=e.id UNION SELECT a.code, b.date, a.sign, d.qty, e._table as product, b.is_void FROM _document_type_data a, alien_document b, alien_item c, alien_product_item d, _product_data e WHERE a.id = b.alien_document_type AND b.id = c.alien_document AND c.id=d.alien_item AND d.product=e.id ) AS foo WHERE code ~* '^(FC|N[CD]|AS|DM)' AND is_void IS NOT true ORDER BY product, date; CREATE FUNCTION f() RETURNS text AS ' DECLARE i RECORD; p bigint; t numeric; BEGIN p := 0; FOR i IN SELECT * FROM stock_checker ORDER BY product, date LOOP IF p != i.product THEN p := i.product; t := i.qty; ELSE t := t + i.qty; END IF; UPDATE stock_checker SET stock=t WHERE id=i.id; END LOOP; RETURN \'xyzzy\'; END; ' LANGUAGE 'plpgsql'; SELECT f(); SELECT product, stock AS calc, level, canonical_name as desc FROM ( SELECT DISTINCT ON (product) stock_checker.*, _product_store_data.level, NULLIF(stock-level,0) as delta, canonical_name FROM stock_checker LEFT JOIN _product_store_data USING (product) LEFT JOIN _product_data ON (_product_store_data.product = _product_data._table) ORDER BY product, date DESC ) AS foo WHERE delta IS NOT NULL; DROP FUNCTION f(); DROP TABLE stock_checker; DROP SEQUENCE stock_checker_id_seq; ROLLBACK; EOF awk -F\| '/^xyzzy/{seen=1} seen && /^[0-9]+/{print $1, $2, $3, $4}' | while read prod calc level desc; do if [ -z "$SEEN" ]; then echo -e "\aSe han encontrado inconsistencias." read -p "Desea que se le pida confirmación antes de cada acción [S/n]? " conf <&3 case "$conf" in n*|N*) conf="" ;; *) conf="1" esac SEEN=1 fi if [ ! -z "$conf" ]; then echo -e "El product #$prod («$desc»)\ntiene un nivel de $level y debiera tener $calc." read -p "Desea corregir [S/n]? " corr <&3 fi case "$corr" in n*|N*) ;; *) echo "UPDATE _product_store_data SET level=$calc WHERE product=$prod;" >> $TEMPFILE esac done echo "COMMIT;" >> $TEMPFILE exec 3<&- exec $DB -f $TEMPFILE